This section discusses another type of cursor that provides far
greater flexibility than the previously discussed static cursors.
A cursor variable is a cursor that actually
contains a pointer to a query result set.
The result set is determined by the execution of the
OPEN FOR statement using the cursor variable.
EnterpriseDB currently supports both strongly
and weakly typed REF CURSOR's.
A cursor variable is not tied to a single particular query like a
static cursor. The same cursor variable may be opened a number
of times with OPEN FOR statements containing
different queries. Each time, a new result set is created from
that query and made available via the cursor variable.
REF CURSOR types may be passed as parameters
to or from stored procedures and functions. The return type of
a function may also be a REF CURSOR type.
This provides the capability to modularize the operations on
a cursor into separate programs by passing a cursor variable
between programs.
SPL supports the declaration of a
cursor variable using both the SYS_REFCURSOR built-in data type as
well as creating a type of REF CURSOR and then declaring a variable of that type.
SYS_REFCURSOR is a REF CURSOR type that
allows any result set to be associated with it. This is known as
a weakly-typed REF CURSOR.
Only the declaration of SYS_REFCURSOR and user defined REF CURSOR variable's is different.
The remaining usage like opening the cursor, selecting into the cursor and closing the cursor is the same across both
the cursor types. For the rest of this chapter our examples will primarily be making use of the SYS_REFCURSOR
cursors. All you need to change in the examples to make them work for user defined REF CURSOR's is the declaration
section.
Note: Strongly-typed REF CURSOR's
require the result set to conform to a declared number and order
of fields with compatible data types and can also optionally return a result set.
The following is the syntax for declaring a SYS_REFCURSOR cursor variable:
name SYS_REFCURSOR;
name is an identifier assigned to the
cursor variable.
The following is an example of a SYS_REFCURSOR variable declaration.
DECLARE
emp_refcur SYS_REFCURSOR;
...
You must perform two distinct declaration steps in order to use a user defined REF CURSOR variable:
Create a referenced cursor TYPE
Declare the actual cursor variable based on that TYPE
The syntax for creating a user defined REF CURSOR type is as follows:
TYPE cursor_type_name IS REF CURSOR [RETURN return_type];
The following is an example of a cursor variable declaration.
DECLARE
TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
my_rec emp_cur_type;
...
Once a cursor variable is declared, it must be opened with an
associated SELECT command. The
OPEN FOR statement specifies the
SELECT command to be used to create the
result set.
OPEN name FOR query;
name is the identifier of a previously
declared cursor variable. query is a
SELECT command that determines the result set
when the statement is executed. The value of the cursor variable
after the OPEN FOR statement is executed
identifies the result set.
In the following example, the result set is a list of employee numbers
and names from a selected department. Note that a variable or
parameter can be used in the SELECT command anywhere
an expression can normally appear. In this case a parameter is used
in the equality test for department number.
CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno emp.deptno%TYPE
)
IS
emp_refcur SYS_REFCURSOR;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
...
After a cursor variable is opened, rows may be retrieved from the
result set using the FETCH statement. See
Section 14.7.3 for details on using the
FETCH statement to retrieve rows from a result set.
In the example below, a FETCH statement has
been added to the previous example so now the result set is returned
into two variables and then displayed. Note that the cursor attributes
used to determine cursor state of static cursors can also be used with
cursor variables. See Section 14.7.7 for details
on cursor attributes.
CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno emp.deptno%TYPE
)
IS
emp_refcur SYS_REFCURSOR;
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
...
Use the CLOSE statement described in
Section 14.7.5 to release the result set.
Note: Unlike static cursors, a cursor variable does not have to be
closed before it can be re-opened again. The result set from
the previous open will be lost.
The example is completed with the addition of the CLOSE
statement.
CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno emp.deptno%TYPE
)
IS
emp_refcur SYS_REFCURSOR;
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
CLOSE emp_refcur;
END;
The following is the output when this procedure is executed.
EXEC emp_by_dept(20)
EMPNO ENAME
----- -------
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
The following are restrictions on cursor variable usage.
Comparison operators cannot be used to test cursor variables
for equality, inequality, null, or not null.
Null cannot be assigned to a cursor variable.
The value of a cursor variable cannot be stored in a database
column.
Static cursors and cursor variables are not interchangeable.
For example, a static cursor cannot be used in an
OPEN FOR statement.
In addition the following table shows the permitted parameter
modes for a cursor variable used as a procedure or function
parameter depending upon the operations on the cursor variable
within the procedure or function.
Table 14-3. Permitted Cursor Variable Parameter Modes
Operation | IN | IN OUT | OUT |
---|
OPEN | No | Yes | No |
FETCH | Yes | Yes | No |
CLOSE | Yes | Yes | No |
So for example, if a procedure performs all three operations,
OPEN FOR, FETCH, and
CLOSE on a cursor variable declared as the
procedure's formal parameter, then that parameter must be
declared with IN OUT mode.
The following are examples of cursor variable usage.
In the following example the cursor variable is opened with a query
that selects employees with a given job. Note also that the cursor
variable is specified in this function's RETURN
statement so the result set is made available to the caller of the
function.
CREATE OR REPLACE FUNCTION emp_by_job (p_job VARCHAR2)
RETURN SYS_REFCURSOR
IS
emp_refcur SYS_REFCURSOR;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE job = p_job;
RETURN emp_refcur;
END;
This function is invoked in the following anonymous block by
assigning the function's return value to a cursor variable
declared in the anonymous block's declaration section. The
result set is fetched using this cursor variable and then it
is closed.
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_job emp.job%TYPE := 'SALESMAN';
v_emp_refcur SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES WITH JOB ' || v_job);
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
v_emp_refcur := emp_by_job(v_job);
LOOP
FETCH v_emp_refcur INTO v_empno, v_ename;
EXIT WHEN v_emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
CLOSE v_emp_refcur;
END;
The following is the output when the anonymous block is executed.
EMPLOYEES WITH JOB SALESMAN
EMPNO ENAME
----- -------
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
The following example illustrates how the various operations
on cursor variables can be modularized into separate programs.
The following procedure opens the given cursor variable with
a SELECT command that retrieves all rows.
CREATE OR REPLACE PROCEDURE open_all_emp (
p_emp_refcur IN OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN p_emp_refcur FOR SELECT empno, ename FROM emp;
END;
This variation opens the given cursor variable with a
SELECT command that retrieves all rows, but
of a given department.
CREATE OR REPLACE PROCEDURE open_emp_by_dept (
p_emp_refcur IN OUT SYS_REFCURSOR,
p_deptno emp.deptno%TYPE
)
IS
BEGIN
OPEN p_emp_refcur FOR SELECT empno, ename FROM emp
WHERE deptno = p_deptno;
END;
This third variation opens the given cursor variable with a
SELECT command that retrieves all rows, but
from a different table. Also note that the function's return
value is the opened cursor variable.
CREATE OR REPLACE FUNCTION open_dept (
p_dept_refcur IN OUT SYS_REFCURSOR
) RETURN SYS_REFCURSOR
IS
v_dept_refcur SYS_REFCURSOR;
BEGIN
v_dept_refcur := p_dept_refcur;
OPEN v_dept_refcur FOR SELECT deptno, dname FROM dept;
RETURN v_dept_refcur;
END;
This procedure fetches and displays a cursor variable
result set consisting of employee number and name.
CREATE OR REPLACE PROCEDURE fetch_emp (
p_emp_refcur IN OUT SYS_REFCURSOR
)
IS
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH p_emp_refcur INTO v_empno, v_ename;
EXIT WHEN p_emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
END;
This procedure fetches and displays a cursor variable
result set consisting of department number and name.
CREATE OR REPLACE PROCEDURE fetch_dept (
p_dept_refcur IN SYS_REFCURSOR
)
IS
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('DEPT DNAME');
DBMS_OUTPUT.PUT_LINE('---- ---------');
LOOP
FETCH p_dept_refcur INTO v_deptno, v_dname;
EXIT WHEN p_dept_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_deptno || ' ' || v_dname);
END LOOP;
END;
This procedure closes the given cursor variable.
CREATE OR REPLACE PROCEDURE close_refcur (
p_refcur IN OUT SYS_REFCURSOR
)
IS
BEGIN
CLOSE p_refcur;
END;
The following anonymous block executes all the previously
described programs.
DECLARE
gen_refcur SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('ALL EMPLOYEES');
open_all_emp(gen_refcur);
fetch_emp(gen_refcur);
DBMS_OUTPUT.PUT_LINE('****************');
DBMS_OUTPUT.PUT_LINE('EMPLOYEES IN DEPT #10');
open_emp_by_dept(gen_refcur, 10);
fetch_emp(gen_refcur);
DBMS_OUTPUT.PUT_LINE('****************');
DBMS_OUTPUT.PUT_LINE('DEPARTMENTS');
fetch_dept(open_dept(gen_refcur));
DBMS_OUTPUT.PUT_LINE('*****************');
close_refcur(gen_refcur);
END;
The following is the output from the anonymous block.
ALL EMPLOYEES
EMPNO ENAME
----- -------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
****************
EMPLOYEES IN DEPT #10
EMPNO ENAME
----- -------
7782 CLARK
7839 KING
7934 MILLER
****************
DEPARTMENTS
DEPT DNAME
---- ---------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
*****************
EnterpriseDB also supports dynamic queries via the OPEN FOR USING statement. A string literal or
string variable is supplied in the OPEN FOR USING
statement to the SELECT command.
OPEN name FOR dynamic_string
[ USING bind_arg [, bind_arg_2 ]...];
name is the identifier of a previously
declared cursor variable. dynamic_string
is a string literal or string variable containing a
SELECT command (without the terminating semi-colon).
bind_arg, bind_arg_2...
are bind arguments that are used to pass variables to corresponding
placeholders in the SELECT command when the cursor
variable is opened. The placeholders are identifiers prefixed by a colon
character.
The following is an example of a dynamic query using a string literal.
CREATE OR REPLACE PROCEDURE dept_query
IS
emp_refcur SYS_REFCURSOR;
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
OPEN emp_refcur FOR 'SELECT empno, ename FROM emp WHERE deptno = 30' ||
' AND sal >= 1500';
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
CLOSE emp_refcur;
END;
The following is the output when the procedure is executed.
EXEC dept_query;
EMPNO ENAME
----- -------
7499 ALLEN
7698 BLAKE
7844 TURNER
In the next example, the previous query is modified to use
bind arguments to pass the query parameters.
CREATE OR REPLACE PROCEDURE dept_query (
p_deptno emp.deptno%TYPE,
p_sal emp.sal%TYPE
)
IS
emp_refcur SYS_REFCURSOR;
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
OPEN emp_refcur FOR 'SELECT empno, ename FROM emp WHERE deptno = :dept' ||
' AND sal >= :sal' USING p_deptno, p_sal;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
CLOSE emp_refcur;
END;
The following is the resulting output.
EXEC dept_query(30, 1500);
EMPNO ENAME
----- -------
7499 ALLEN
7698 BLAKE
7844 TURNER
Finally, a string variable is used to pass the
SELECT providing the most flexibility.
CREATE OR REPLACE PROCEDURE dept_query (
p_deptno emp.deptno%TYPE,
p_sal emp.sal%TYPE
)
IS
emp_refcur SYS_REFCURSOR;
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
p_query_string VARCHAR2(100);
BEGIN
p_query_string := 'SELECT empno, ename FROM emp WHERE ' ||
'deptno = :dept AND sal >= :sal';
OPEN emp_refcur FOR p_query_string USING p_deptno, p_sal;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
CLOSE emp_refcur;
END;
EXEC dept_query(20, 1500);
EMPNO ENAME
----- -------
7566 JONES
7788 SCOTT
7902 FORD