Oracle B25-317-01 User Manual download pdf (Page 68)

Using PL/SQL Ref Cursors to Return Result Sets

6-6 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

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)

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:

1 2 ... 63 64 65 66 67 68 69 70 71 72 73 ... 93 94

No comments

Oracle B25-317-01 User Manual Page 68