Login

Username:

Password: 

Join us Now |  Forgot Password? | Forgot UserName?

PL/SQL

Learn step by step



PL/SQL Functions

PL/SQL functions block create using CREATE FUNCTION statement. The major difference between PL/SQL function or procedure, function return always value where as procedure may or may not return value.

When you create a function or procedure, you have to define IN/OUT/INOUT parameters parameters.

  • IN : IN parameter referring to the procedure or function and allow to overwritten the value of parameter.
  • OUT : OUT parameter referring to the procedure or function and allow to overwritten the value of parameter.
  • IN OUT : Both IN OUT parameter referring to the procedure or function to pass both IN OUT parameter, modify/update by the function or procedure and also get returned.

IN/OUT/INOUT parameters you define in function argument list that get returned back to a result. When you create the function default IN parameter is passed in argument list. It's means value is passed but not returned. Explicitly you have define OUT/IN OUT parameter in argument list.

PL/SQL Functions Syntax
CREATE [OR REPLACE] FUNCTION [SCHEMA..] function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section
variable declarations;
constant declarations;
]
BEGIN
[executable_section
PL/SQL execute/subprogram body
]
[EXCEPTION]
[exception_section
PL/SQL Exception block
]
END [function_name];
Function Example

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

Employee_NO Employee_Name Employee_DEPT Employee_SALARY
1 Jitendra Soni Web Developer 30,000
2 Pankaj Tak Program developer 35,000
3 Ramesh Patel Web Developer 25,000
Create Function

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

SQL>edit fun1
CREATE or REPLACE FUNCTION fun1(no in number)
RETURN varchar2
IS
name varchar2(20);
BEGIN
select ename into name from employee 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.

SQL>edit fun
DECLARE
no number :=&no;
name varchar2(20);
BEGIN
name := fun1(no);
dbms_output.put_line('Name:'||' '||name);
end;
PL/SQL Program Result
SQL>@fun
no number &n=2
Name: marks jems

PL/SQL procedure successfully completed.
PL/SQL Drop Function

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

Functions Drop Syntax
DROP FUNCTION function_name;
Functions Drop Example
SQL>DROP FUNCTION fun1;

Function dropped.


Related Videos