1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
|
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as mni
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE getCursor(p_recordset OUT SYS_REFCURSOR )
2 IS
3 begin
4 OPEN p_recordset FOR
5 SELECT first_name,
6 last_name,
7 department_name
8 FROM employees e
9 INNER JOIN departments d ON d.manager_id = e.employee_id;
10 end;
11 /
Procedure created
SQL>
SQL> DECLARE
2 cur_chief SYS_REFCURSOR;
3 Type r_chief Is Record (
4 first_name hr.employees.first_name%type,
5 last_name hr.employees.last_name%type,
6 department_name hr.departments.department_name%type
7 );
8 var_chief r_chief;
9 BEGIN
10 getCursor(cur_chief);
11 LOOP
12 FETCH cur_chief INTO var_chief;
13 EXIT WHEN cur_chief%NOTFOUND;
14 DBMS_OUTPUT.PUT_LINE(var_chief.department_name || ' - ' ||
15 var_chief.first_name || ',' ||
16 var_chief.last_name);
17 END LOOP;
18 CLOSE cur_chief;
19 END;
20 /
Executive - Steven,King
IT - Alexander,Hunold
Finance - Nancy,Greenberg
Purchasing - Den,Raphaely
Shipping - Adam,Fripp
Sales - John,Russell
Administration - Jennifer,Whalen
Marketing - Michael,Hartstein
Human Resources - Susan,Mavris
Public Relations - Hermann,Baer
Accounting - Shelley,Higgins
PL/SQL procedure successfully completed
SQL> |
Partager