Functions in PL/SQL

PL/SQL CREATE Function

  • The PL/SQL functions are same as PL/SQL procedure.
  • The main difference between a procedure and a function is, a function must always return a value and the procedure may or may not return value.
  • The function should contain a return statement.

Syntax

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];


Where,
  • function_name specifies the name of the function.
  • [OR REPLACE] option allows to modify an existing function.
Lets take an example to declare, define and invoke a simple PL/SQL function that will compute and return the maximum two values.

Example

DECLARE  
a number;  
b number;  
c number;  
FUNCTION findMax(x IN number, y IN number)   
RETURN number  
IS  
z number;  
BEGIN  
IF x > y THEN  
z:= x;  
ELSE  
Z:= y;  
END IF;  
RETURN z;  
END;   
BEGIN  
a:= 20;  
b:= 30;  
c := findMax(a, b);  
dbms_output.put_line(' Maximum of (20,30): ' | |  c);  
END;  
/


Output:
Maximum of (20,30): 30

Example: Create a function using table.

Consider the following table titled 'Employee'.

IdNameDepartmentsalary
1JayIT45000
2AlbertHR30000
3AnnaHR28000
4BobIT35000
5AliceIT55000

CREATE OR REPLACE FUNCTION totalEmployee
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM Employee;  
    RETURN total;  
END;  
/


The user will get the following message after creating the function.
Function created.

Then call a function
To call a function user needs to pass the required parameters along with function name and if function returns a value then store the returned value.

Following program calls the function totalEmployee from an anonymous block.

DECLARE  
   c number(2);  
BEGIN  
   c := Employee();  
   dbms_output.put_line('Total no. of Employee: ' | | c);  
END;  
/


The user will get following result after executing the above code.
Total no. of Employee: 4

PL/SQL DROP Function

Syntax:
DROP FUNCTION function_name