STORED FUNCTION IN PLStructuredQueryL.pptx

SQL

1. Stored Function in PL/SQL

2. Benefits of Stored Functions Efficiency: These offer a significant performance improvement because a single call to a stored procedure can invoke multiple SQL statements. Reusability: A PL/SQL program is written once and can be used in a variety of situations. Portability: A PL/SQL program once made in one OS can be run in any OS. Maintainability: As it can be reused again and again, maintenance cost is low.

3. Difference between Stored Procedure and Stored Function Procedure does not return value Whereas Function always returns value

4. Example 1 create or replace function F1( Deg_f in number ) return number is Deg_c number; begin Deg_c := (5/9)* Deg_f – 32/9; return Deg_c ; end;

5. Example 2 create or replace function F2(e in number) return number is basic emp.sal%type ; da emp.sal%type ; hra emp.sal%type ; tot emp.sal%type ; begin select sal into basic from emp where eno =e; da :=basic; hra :=0.15*basic; tot:= basic+da+hra ; return tot; end;

6. Thank You