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}  
   < function_body >  
END [function_name];  


  • 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,

1Sushil KumarWeb Developer5000
2Manish BhaleraoProgram Developer8000
3Seema RautProgram Developer4000
4Kunal SharmaWeb Developer2000

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);
    select ename into name from emp1 where eno = no;
return name;

Execute Function

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

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


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.


DROP FUNCTION function_name;



Function dropped.