Chapter 9: PL/SQL Functions

PL/SQL Functions

What is a Function in PL/SQL?

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

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,

• Function_name  specifies the name of the function. 
• [OR REPLACE] option allows modifying an existing function. 
• The optional parameter list contains name, mode and types of the parameters. 
• 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 have at least one RETURNstatement in the execution section. The RETURN clause in the function header specifies the data type of returned value.

PL/SQL Function Example

In this example we are creating a function to pass employee number and get that employee name from table. We have emp1 table having employee information,

EMP_NO	EMP_NAME	EMP_DEPT	EMP_SALARY
1	Sushil Kumar	Web Developer	5000
2	Manish Bhalerao	Program Developer	8000
3	Seema Raut	Program Developer	4000
4	Kunal Sharma	Web Developer	2000

Create Function

So lets start passing IN parameter (no). Return datatype set varchar2. Now inside function SELECT … INTO statement to get the employee name

SQL>edit fun1CREATE or REPLACE FUNCTION fun1(no in number) RETURN varchar2IS
name varchar2(20);
BEGIN
    select ename into name from emp1 where eno = no;
return name;
END;
/

Execute Function

After write the PL/SQL function you need to execute the function.

SQL>@fun1
Function created.

PL/SQL procedure successfully completed.

PL/SQL Program to Calling Function

This program call the above define function with pass employee number and get that employee name.

edit funDECLARE
    no number :=&no;
    name varchar2(20);BEGIN
    name := fun1(no);
    dbms_output.put_line('Name:'||'   '||name);
    end;/

Result

SQL>@fun
no number &n=2
Name: Manish Bhalerao

PL/SQL procedure successfully completed.

PL/SQL Drop Function

You can drop PL/SQL function using DROP FUNCTION statements.

Syntax

DROP FUNCTION function_name;

Example

SQL>DROP FUNCTION fun1;

Function dropped.