PL/SQL - Sotred Functions

PL/SQL FUNCTIONS :

A function is a named pl/sql block that returns a value.

A function can be stored in the database as a schema object for repeated execution.


Syntax :

Create or replace function function_name
Parameter mode datatype
----
Return datatype
Is | as
Pl/sql block


Advantages:

--> Increase efficiency of queries by performing functions in the query rather than in the application.

--> Permits calculations that are too complex.

Eg:

Create or replace function get_sal
(p_id in emp.empno%type)
Return number
Is
V_sal emp.sal%type := 0;
Begin
Select sal into v_sal from emp where empno = p_id;
Return v_sal;
End;

Variable g_sal number;

Execute :g_sal := get_sal(117);

Print g_sal;


A function may accept one or many parameters but must return a single value.



Executing functions:

--> Invoke a function as part of a pl/sql expression.
--> Create a variable to hold the return value.




Invoking functions in SQL expressions :

Create or replace function tax (p_value in number)
Return number
Is
Begin
Return (p_value * 0.08);
End;

Select empno, ename, sal, tax(sal) from emp where empno=7900;



Locations to call user defined functions

--> Select list of select command.
--> Condition of the where and having clause.
--> Connect by, start with, order by and group by clauses.
--> values clauses of insert command.
--> set clause of the update command.


PL/SQL user defined functions can be called from any where, where built in functions can be called.


Restrictions on calling functions from SQL expressions :

--> Must be a stored function.

--> Accept only IN parameters.

--> Accept only valid SQL data types, not pl/sql specific types, as parameters.

--> Return data types that are valid sql data types not pl/sql specific types.

--> Functions called from sql expressions cannot contain DML statements.

--> Functions called from update or delete statements on a table T cannot contain a DML on the same table T.

--> Functions called from a DML statement on a table T cannot query the same table.


-->We must own or have the execute privilege.


Notes:

--> Only stored functions are callable from sql statements. Stored procedures cannot be called.


Removing functions

--> Drop function function_name;




Procedures Vs Functions :



Procedure :

--> Execute as a PL/SQL statement.

--> No return clause in the header.

--> Can return none, one or many values.

--> Can contain a return statement.

Function :

--> Invokes as part of an expression.

--> Must contain a return clause in the header.

--> Must return a single value.

--> Must contain at least one return statement.