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.