Case Study of Using packages to build stored procedures and functions

Stored Procedure: Package create or replace package emppackage is -- Author: Administrator -- created: 14:02:43 -- Purpose: declares a stored procedure -- Public type declarations type empcursor is ref cursor; -- Public Function and procedure declarations -- declare a stored procedure selectemps (emplist out empcursor); End emppackage; package body Create or replace package body emppackage is -- function and procedure implementations procedure selectemps (emplist out empcursor) is begin open emplist for select * from EMP; end emppackage; call declareempc1 emppackage in the Command window. empcursor; emp_row EMP % rowtype; beginemppackage. selectemps (empc1); loopfetch empc1 into emp_row; exit when empc1 % notfound; dbms_output.put_line (emp_row.empno); End loop; close empc1; end; Result: 2345736974997521 storage function: Packet: create or replace package mypackage is -- Author: Administrator -- created: 14:06:47 -- Purpose: storage function -- Public type declarations type empc1 is ref cursor; function queryemps return empc1; end mypackage; package body: Create or replace package body mypackage isfunction queryemps return empc1 is emp_c1 empc1; begin open emp_c1 for select * from EMP; return emp_c1; end mypackage; called in the Command window: declareemp_c1 mypackage. empc1; emp_row EMP % rowtype; beginemp_c1: = mypackage. queryemps; loop fetch emp_c1 into emp_row; exit when emp_c1 % notfound; dbms_output.put_line (emp_row.empno); End loop; close emp_c1; end; Result: 2345736974997521