PL SQL Stored Function
A stored function, often referred to as a stored procedure, is a pre-compiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit. They are stored within the database and can be invoked by applications or users.
Stored functions are used to encapsulate reusable SQL code, which can help improve performance and maintainability of database applications.
Below are a some key points about stored functions:
Reusability: Once a stored function is created, it can be reused multiple times by calling it with the appropriate parameters. This helps avoid code duplication.
Performance: Because stored functions are pre-compiled, they can execute more quickly than ad-hoc SQL queries, which need to be parsed and compiled each time they run.
Security: Stored functions can help enhance security by controlling data access and ensuring that only authorized users can execute certain operations.
Maintainability: Encapsulating complex logic within stored functions makes the database schema easier to manage and maintain. Changes to business logic can be made in one place instead of updating multiple queries across the application.
Parameterization: Stored functions can accept input parameters, allowing for dynamic execution based on the parameters provided.
Return Values: Unlike stored procedures, stored functions must return a single value, which can be used directly in SQL statements. To know more about procedures and functions in sql please click here
A Function Is Block Of Code To Perform Some Task And Must Return A Value. These Functions Are Created By User Explicitely.so That We Can Also Called As “user Defined Function”
Syntax:
Create Or Replace Function <function_name> [(Arugment Datatype,
Argument Datatype,)]
Return <datatype>
Is
Begin
<exec-statements>;
Return (Value);
End <function_name>;
/
How To Call A PL SQL Stored Function:
Select <fname>(Values) From Dual;
Ex: Create A Sf To Accept Employee Number And Return That Employee Name From Emp Table?
Create Or Replace Function Sf1(P_empno Number)
Return Varchar2
As
V_ename Varchar2(10);
Begin
Select Ename Into V_ename From Emp Where Empno=p_empno;
Return V_ename;
End;
/
Function Created.
Sql> Select Sf1(7566) From Dual;
Ex: Create A Sf To Input Department Name And Return Sum Of Salary Of Department?
Function Sf1(P_dname Varchar2)
Return Number
As
V_totsal Number (10);
Begin
Select Sum(Sal) Into V_totsal From Emp E,dept D
Where E. Deptno=d.deptno And Dname=p_dname;
Return V_totsal;
End;
/
Sal> Select Sf1(‘sales’) From Dual;
Ex: Create A Sf To Return No. Of Employee In Between Given Dates?
Function Sf2(Sd Date, Ed Date)
Return Number
As
V_count Number (10);
Begin
Select Count (*) Into V_count From Emp
Where Hiredate Between Sd And Ed;
Return V_count;
End;
/
Sql> Select Sf2(’01-jan-81′,’31-dec-81′) From Dual;
Ex: Create A Sf To Input Employee Number And Return That Employee Gross Salary As Per Given Conditions Are
I) Hra ——– 10%
Ii) Da ——– 20%
Iii) Pf ——–10%.
Function Sf3(P_empno Number)
Return Number
As
V_bsal Number (10);
V_hra Number (10);
V_da Number (10);
V_pf Number (10);
V_gross Number (10);
Begin
Select Sal Into V_bsal From Emp Where Empno=p_empno;
V_hra: =v_bsal*0.1;
V_da: =v_bsal*0.2;
V_pf: =v_bsal*0.1;
V_gross: =v_bsal+v_hra+v_da+v_pf;
Return V_gross;
End;
/
Sql> Select Sf3(7788) From Dual;
Ex: Write A Function To Find Simple Interest.
Create Or Replace Function Si (P Number, T Number, R Number)
Return Number
Is
Simple_int Number;
Begin
Simple_int: =(P*t*r)/100;
Return (Simple_int);
End Si;
/
> Generally, Functions Are Executed By Using ‘select’ Statement.
Sql> Select Si (1000,2,10) From Dual;
Ex: Create A Sf To Find Experience Of Given Employee?
Create Or Replace Function Emp_exp (Tempno Emp.empno%type)
Return Varchar2
Is
Tdate Emp.hiredate%type;
Texp Number;
Begin
Select Hiredate Into Tdate From Emp
Where Empno=tempno;
Texp: =round((Sysdate-tdate)/365);
Return (Tempno||’ Employee Experience Is ‘||texp||’ Years.’);
Exception
When No_data_found Then
Return (‘given Employee Record Not Found.’);
End Emp_exp;
Sql> Select Emp_exp (7788) From Dual;
Sql> Select Emp_exp(Empno) From Emp;
Function For To Calculate Employee Experience:
Create Or Replace Function Emp_expe (Tempno Emp.empno%type)
Return Number
Is
Texp Number;
Begin
Select Round((Sysdate-hiredate)/365) Into Texp From Emp
Where Empno=tempno;
Return(Texp);
End Emp_expe;
Note:
All Functions Are Stored In User_objects.
All Functions Bodies Are Stored In ‘user_source’ System Table.
> To See The Function Body.
Ex:
Sql> Select Text From User_source Where Name=’emp_expe’;
To know more about Difference between stored procedure and function in sql click here
Dropping Functions:
