 |
|
EnterpriseDB: REF Cursors
Oracle Tips by
Burleson
|
A REF
CURSOR is a reference
to a cursor, kind of a pointer to a pointer. If you are coming from a
C background, you already know the usefulness of that.
A REF
CURSOR can be used
as
a parameter to a procedure. That means that you can open a cursor and
send back the pointer to that cursor allowing you to stream results to
other procedures (even procedures written in other languages).
There
are two types of static ref cursor: a strongly typed ref cursor and a
weakly typed ref cursor. A strongly typed ref cursor has the column
structure of the cursor defined at compile time. That means that you
have associated a record type with the ref cursor when you were
writing your code.
The
syntax of a strongly typed cursor is:
TYPE <cursor name> IS REF CURSOR RETURN <table%ROWTYPE|record
type name>;
A weakly
typed cursor has no predefined column structure. It is declared as a
ref cursor but the structure associated with it is defined by its use
rather than its declaration.
The
syntax of a weakly typed cursor is:
TYPE <cursor name> IS REF CURSOR;
You have
probably noticed that I created a type as I did for collections and
record types. Unlike a static cursor, to use this ref cursor, I would
need to declare a variable of that cursor type.
* A
shortcut to creating a ref cursor is to use SYS_REFCURSOR as the data
type.
Other
than the declaration, a ref cursor works like a normal cursor. Cursor
attributes are available and you use the OPEN, FETCH and CLOSE syntax.
Below is
an example showing several uses of ref cursors.
DECLARE
v_emp_rec emp%ROWTYPE;
TYPE r_depts IS RECORD (
ename emp.ename%TYPE,
dname dept.dname%TYPE );
v_depts_type r_depts;
TYPE c_employees IS REF CURSOR RETURN emp%ROWTYPE;
v_employees c_employees;
TYPE c_depts IS REF CURSOR RETURN r_depts;
v_depts c_depts;
TYPE c_more_emps IS REF CURSOR;
v_more_emps c_more_emps;
v_yet_more_emps SYS_REFCURSOR;
BEGIN
-- Cursor 1
OPEN v_employees FOR SELECT * FROM emp LIMIT 5;
LOOP
FETCH v_employees INTO v_emp_rec;
EXIT WHEN v_employees%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'v_employees Loop - Ename: ' ||
v_emp_rec.ename );
END LOOP;
CLOSE v_employees;
-- Cursor 2
OPEN v_depts FOR SELECT emp.ename, dept.dname
FROM emp,
dept LIMIT 5;
LOOP
FETCH
v_depts INTO v_depts_type;
EXIT WHEN v_depts%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'v_depts_type Loop - ename: ' ||
v_depts_type.ename || ', dname: ' ||
v_depts_type.dname );
END LOOP;
CLOSE v_depts;
-- Cursor 3
OPEN v_more_emps
FOR SELECT * FROM emp LIMIT 5;
LOOP
FETCH v_more_emps INTO v_emp_rec;
EXIT WHEN v_more_emps%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'v_more_emps Loop - Ename: ' ||
v_emp_rec.ename );
END LOOP;
CLOSE v_more_emps;
-- Cursor 4
OPEN v_yet_more_emps FOR SELECT * FROM emp LIMIT 5;
LOOP
FETCH v_yet_more_emps INTO v_emp_rec;
EXIT WHEN v_yet_more_emps%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'v_yet_more_emps Loop - Ename: ' ||
v_emp_rec.ename );
END LOOP;
CLOSE v_yet_more_emps;
END;
INFO: v_employees Loop - Ename: SMITH
INFO: v_employees Loop - Ename: ALLEN
INFO: v_employees Loop - Ename: WARD
INFO: v_employees Loop - Ename: JONES
INFO: v_employees Loop - Ename: MARTIN
INFO: v_depts_type Loop - ename: SMITH, dname: ACCOUNTING
INFO: v_depts_type Loop - ename: ALLEN, dname: ACCOUNTING
INFO: v_depts_type Loop - ename: WARD, dname: ACCOUNTING
INFO: v_depts_type Loop - ename: JONES, dname: ACCOUNTING
INFO: v_depts_type Loop - ename: MARTIN, dname: ACCOUNTING
INFO: v_more_emps Loop - Ename: SMITH
INFO: v_more_emps Loop - Ename: ALLEN
INFO: v_more_emps Loop - Ename: WARD
INFO: v_more_emps Loop - Ename: JONES
INFO: v_more_emps Loop - Ename: MARTIN
INFO: v_yet_more_emps Loop - Ename: SMITH
INFO: v_yet_more_emps Loop - Ename: ALLEN
INFO: v_yet_more_emps Loop - Ename: WARD
INFO: v_yet_more_emps Loop - Ename: JONES
INFO: v_yet_more_emps Loop - Ename: MARTIN
The
things to notice about this code are the way the cursors are declared.
Cursor 1
is declared as a %ROWTYPE, strongly typed ref cursor and the select
statement is tied directly to the emp table's column structure. This
type of declaration is best when returning a complete row of data. If
the table structure changes, the ref cursor and query will not need to
be modified. The changes will be picked up automatically.
Cursor 2
is declared as a strongly typed cursor of a RECORD type. This ref
cursor is tied directly to the structure of the r_depts. record type.
If you change r_depts., the columns available in the cursor will
change and the query will need to be changed to fit.
Cursor 3
declares a weakly typed cursor. This cursor is not tied to a column
structure until it is called below. If the query changes, the ref
cursor is still usable.
The
real power of REF Cursors is their ability to be dynamically typed and
the ability to be parameterized in stored procedures. The section
immediately below will show you how to use dynamic SQL.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
