User's Manual

Using PL/SQL Ref Cursors to Return Result Sets
6-6 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft
1.
In the SQL Commands page, as the HR user, create the following PL/SQL package
specification:
CREATE OR REPLACE PACKAGE cv_types AS
TYPE empinfotyp IS REF CURSOR;
PROCEDURE get_employees(deptid in number,
employees in out empinfotyp);
END cv_types;
Click Run:
In the Results section, confirm the package specification is successfully created:
2. In the SQL Commands page, as the HR user, create the PL/SQL package body
(implementation):
CREATE OR REPLACE PACKAGE BODY cv_types AS
PROCEDURE get_employees(deptid in number,
employees in out empinfotyp)
IS
BEGIN
OPEN employees FOR
SELECT employee_id,
substr(first_name,1,1) || '. '|| last_name as employee_name,
hire_date,
to_char(salary, '999G999D99') as salary,
NVL(commission_pct,0) as commission_pct,
to_char(calc_remuneration(salary, commission_pct),
'9999G999D99') as remuneration
FROM employees
WHERE department_id = deptid
ORDER BY employee_id ASC;
END get_employees;
END cv_types;
Click Run: