PL/SQL Function
The PL/SQL Function is very similar to PL/SQL Procedure. The
main difference between procedure and a function is, a function
must always return a value, and on the other hand a procedure
may or may not return a value. Except this, all the other things of
PL/SQL procedure are true for PL/SQL function too.
Syntax to create a function:
. CREATE [OR REPLACE] FUNCTION function_name [parameters]
. [(parameter_name [IN | OUT | IN OUT] type [, ...])]
. RETURN return_datatype
. {IS | AS}
. BEGIN
. < function_body >
. END [function_name];
Here:
o Function_name: specifies the name of the function.
o [OR REPLACE] option allows modifying an existing function.
o The optional parameter list contains name, mode and
types of the parameters.
o IN represents that value will be passed from outside and OUT
represents that this parameter will be used to return a value
outside of the procedure.
The function must contain a return statement.
o RETURN clause specifies that data type you are going to
return from the function.
o Function_body contains the executable part.
o The AS keyword is used instead of the IS keyword for creating
a standalone function.
What do you mean by Return Statement?
The return statement is used to immediately complete its
execution and return to the calling program. Executing in the
calling program resumes with the statement following the
subprogram call. A function must have atleast one return
statement in its executable section, to return a value from within
a function use the following statement:
Return <expression>;
In the above syntax, where expression evaluates to the return
data type of the function itself.
PL/SQL Function Example
Let's see a simple example to create a function.
. create or replace function adder(n1 in number, n2 in number)
. return number
. is
. n3 number(8);
. begin
. n3 :=n1+n2;
. return n3;
. end;
. /
Now write another program to call the function.
. DECLARE
. n3 number(2);
. BEGIN
. n3 := adder(11,22);
. dbms_output.put_line('Addition is: ' || n3);
. END;
. /
Output:
Addition is: 33
Statement processed.
0.05 seconds
There are two types of Functions:
o Local functions
o Stored functions
Local Functions:
A Local function is the function that is defined in the declaration
of the PL/SQL block. The functions cannot be called by a PL/SQL
block defined outside that enclosing block.
Following syntax is used for declaring a Local function:
. DECLARE
. Function <functname>
. [(parameters{IN}datatype,....)}
. Return datatype {IS | AS}
. [local_declaration]
. BEGIN
. Execution Section;
. [EXCEPTION exception section;]
. END[<funcname>];
. BEGIN
. [EXCEPTION]
. END:
Example 1: Write a function to display the salary of an
employee from EMP table whose employee number is
given.
. DECLARE
. Ecode [Link]%Type;
. Salary [Link]%Type;
. Function calc_sal(E number) Return number AS
. S [Link]%Type;
. BEGIN
. Select sal into S from emp where empno=ecode;
. Return S;
. EXCEPTION
. When no_data_found then
. Return 0;
. END calc_sal;
. BEGIN
. Ecode := &Ecode;
. Salary:= &calc_sal(Ecode);
. If Salary <> 0 then
. dbms_output.put_line('salary =' || Salary);
. ELSE
. dbms_output.put_line('Employee does not exist');
. ENDIF:
. END:
. /
Output:
Function created.
Stored Function:
A stored function is the named PL/SQL block that has been
compiled and stored in one of the Oracle's engine system table.
Following Syntax is used for creating stored function:
. CREATE [OR REPLACE] function <func_name>
. [ (parameter {IN} datatype,........)]
. Return datatype {IS|AS}
. [local_declaration_section]
. BEGIN
. Executable_section
. [EXCEPTION
. exception_section]
. END [func_name];
. </func_name>
Example of Create Stored Function:
. CREATE OR REPLACE FUNCTION totalCustomers
. RETURN number IS
. total number(2) := 0;
. BEGIN
. SELECT count(*) into total
. FROM customers;
. RETURN total;
. END;
. /
After the execution of above code, you will get the following
result.
Function created.
Calling PL/SQL Function:
While creating a function, you have to give a definition of what
the function has to do. To use a function, you will have to call that
function to perform the defined task. Once the function is called,
the program control is transferred to the called function.
After the successful completion of the defined task, the call
function returns program control back to the main program.
To call a function you have to pass the required parameters along
with function name and if function returns a value then you can
store returned value. Following program calls the function
totalCustomers from an anonymous block:
. DECLARE
. c number(2);
. BEGIN
. c := totalCustomers();
. dbms_output.put_line('Total no. of Customers: ' || c);
. END;
. /
After the execution of above code in SQL prompt, you will get the
following result.
Total no. of Customers: 4
PL/SQL procedure successfully completed.
PL/SQL Recursive Function
You already know that a program or a subprogram can call
another subprogram. When a subprogram calls itself, it is called
recursive call and the process is known as recursion.
Example to calculate the factorial of a number
Let's take an example to calculate the factorial of a number. This
example calculates the factorial of a given number by calling
itself recursively.
. DECLARE
. num number;
. factorial number;
.
. FUNCTION fact(x number)
. RETURN number
. IS
. f number;
. BEGIN
. IF x=0 THEN
. f := 1;
. ELSE
. f := x * fact(x-1);
. END IF;
. RETURN f;
. END;
.
. BEGIN
. num:= 6;
. factorial := fact(num);
. dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
. END;
. /
O/P:
Factorial 6 is 720
PL/SQL procedure successfully completed.
PL/SQL Drop Function
Syntax for removing your created function:
If you want to remove your created function from the database,
you should use the following syntax.
. DROP FUNCTION function_name;
In the above syntax, drop is a command and function_name is
the name of the function that you want to drop.
Example:
Drop function fact;
In the above example, drop command is used to drop the
function named fact;
Rules for creating a function:
Following points should be kept in mind while creating function:
o A function may accept one or more parameters.
o Avoid using OUT and IN OUT nodes with functions.
o There must be atleast one "Return" statement in function.
o Add a return clause with data type in the header of the
function.
o PL/SQL user defined functions can be called from any SQL
expression where built-in function can be called.
o Stored PL/SQL function cannot be called from CHECK
constraint clause of CREATE or ALTER TABLE statement.