| Strongly Typed |
| Note: A REF CURSOR that specifies a specific return type. |
| Package Head |
CREATE OR REPLACE PACKAGE strongly_typed IS
TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;
PROCEDURE child(p_return_rec OUT return_cur);
PROCEDURE parent(p_NumRecs PLS_INTEGER);
END strongly_typed;
/ |
| Package Body |
CREATE OR REPLACE PACKAGE BODY strongly_typed IS
PROCEDURE child(p_return_rec OUT return_cur) IS
BEGIN
OPEN p_return_rec FOR
SELECT * FROM all_tables;
END child; |
| --================================================== |
PROCEDURE parent(p_NumRecs PLS_INTEGER) IS
p_retcur return_cur;
at_rec all_tables%ROWTYPE;
BEGIN
child(p_retcur);
FOR i IN 1 .. p_NumRecs LOOP
FETCH p_retcur
INTO at_rec;
dbms_output.put_line(at_rec.table_name ||
' - ' || at_rec.tablespace_name ||
' - ' || TO_CHAR(at_rec.initial_extent) ||
' - ' || TO_CHAR(at_rec.next_extent));
END LOOP;
END parent; |
END strongly_typed;
/ |
| Run Demo |
set serveroutput on
exec strongly_typed.parent(1)
exec strongly_typed.parent(8) |
| |
| Weakly Typed |
| Note: A REF CURSOR that does not specify the return type such as SYS_REFCURSOR. |
| Child Procedure |
CREATE OR REPLACE PROCEDURE child(
p_NumRecs IN PLS_INTEGER,
p_return_cur OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_return_cur FOR
'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs;
END child;
/ |
| Parent Procedure |
CREATE OR REPLACE PROCEDURE parent(pNumRecs VARCHAR2) IS
p_retcur SYS_REFCURSOR;
at_rec all_tables%ROWTYPE;
BEGIN
child(pNumRecs, p_retcur);
FOR i IN 1 .. pNumRecs LOOP
FETCH p_retcur INTO at_rec;
dbms_output.put_line(at_rec.table_name ||
' - ' || at_rec.tablespace_name ||
' - ' || TO_CHAR(at_rec.initial_extent) ||
' - ' || TO_CHAR(at_rec.next_extent));
END LOOP;
END parent;
/ |
| Run Demo |
set serveroutput on
exec parent(1)
exec parent(17) |
| |
| Passing Ref Cursors |
| Child Procedure |
CREATE TABLE employees (
empid NUMBER(5),
empname VARCHAR2(30));
INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');
INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS
TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
rec_array array_t;
BEGIN
FETCH p_cursor BULK COLLECT INTO rec_array;
FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i));
END LOOP;
END pass_ref_cur;
/ |
set serveroutput on
DECLARE
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR
'SELECT empname FROM employees';
pass_ref_cur(rec_array);
CLOSE rec_array;
END;
/ |
| |
| VARRAY Ref Cursors |
| How to use a REF CURSOR to pass a VARRAY and disassemble it on the receiving end |
CREATE OR REPLACE TYPE Project AUTHID DEFINER AS OBJECT (
project_no NUMBER(2),
title VARCHAR2(35),
cost NUMBER(7,2));
/
CREATE OR REPLACE TYPE ProjectList AS VARRAY(50) OF Project;
/
CREATE TABLE department (
dept_id NUMBER(2),
dname VARCHAR2(15),
budget NUMBER(11,2),
projects ProjectList);
CREATE OR REPLACE PROCEDURE varray_refcur(outparm OUT NOCOPY SYS_REFCURSOR) AUTHID DEFINER IS
BEGIN
OPEN outparm FOR
SELECT *
FROM department;
END varray_refcur;
/
INSERT INTO department
VALUES(30, 'Accounting', 1205700,
ProjectList (Project(1, 'Design New Expense Report', 3250),
Project(2, 'Outsource Payroll', 12350),
Project(3, 'Evaluate Merger Proposal', 2750),
Project(4, 'Audit Accounts Payable', 1425)));
INSERT INTO department
VALUES(50, 'Maintenance', 925300,
ProjectList (Project(1, 'Repair Leak in Roof', 2850),
Project(2, 'Install New Door Locks', 1700),
Project(3, 'Wash Front Windows', 975),
Project(4, 'Repair Faulty Wiring', 1350),
Project(5, 'Winterize Cooling System', 1125)));
INSERT INTO department
VALUES(60, 'Security', 750400,
ProjectList (Project(1, 'Issue New Employee Badges', 13500),
Project(2, 'Find Missing IC Chips', 2750),
Project(3, 'Upgrade Alarm System', 3350),
Project(4, 'Inspect Emergency Exits', 1900)));
COMMIT;
set serveroutput on
DECLARE
drow SYS_REFCURSOR;
drec department%ROWTYPE;
BEGIN
-- call the proc that returns the weakly typed ref cursor
varray_refcur(drow);
FOR i IN 1 .. 3 LOOP
FETCH drow INTO drec;
dbms_output.put_line('Dept#: ' || drec.dept_id);
dbms_output.put_line('DName: ' || drec.dname);
dbms_output.put_line('Budget:' || drec.budget);
dbms_output.put_line('Proj#: ' || drec.projects(i).project_no);
dbms_output.put_line('Title: ' || drec.projects(i).title);
dbms_output.put_line('PCost: ' || drec.projects(i).cost);
dbms_output.put_line('------------------------------');
END LOOP;
END;
/ |
|
