This programme contains a package along with a calling programma that uses this package. Let me first provide the package itself.
The code:
CREATE OR REPLACE PACKAGE employee_data AS TYPE employee_cv_type IS REF CURSOR RETURN employee%ROWTYPE; PROCEDURE open_employee_data ( employee_cv IN OUT employee_cv_type, x_dno IN employee.dno%TYPE); PROCEDURE fetch_employee_data ( employee_cv IN employee_cv_type, employee_output OUT VARCHAR2); END employee_data; / CREATE OR REPLACE PACKAGE BODY employee_data AS PROCEDURE open_employee_data ( employee_cv IN OUT employee_cv_type, x_dno IN employee.dno%TYPE) IS BEGIN OPEN employee_cv FOR SELECT * FROM employee WHERE employee.dno = x_dno; END open_employee_data; PROCEDURE fetch_employee_data ( employee_cv IN employee_cv_type, employee_output OUT VARCHAR2) IS employee_row employee%ROWTYPE; BEGIN FETCH employee_cv INTO employee_row; employee_output := employee_row.lname || ' ' || employee_row.salary; END fetch_employee_data; END employee_data;
The calling programme looks like:
DECLARE department_number employee.dno%TYPE := 5; employee_cv employee_data.employee_cv_type; employee_output VARCHAR2(1000); BEGIN employee_data.open_employee_data (employee_cv, department_number); LOOP employee_data.fetch_employee_data (employee_cv, employee_output); EXIT WHEN employee_cv%NOTFOUND; dbms_output.put_line (employee_output); END LOOP; END;