Oracle7 Server Application Developer's Guide

Cursor Variables

Cursor variables are references to cursors. A cursor is a static object; a cursor variable is a pointer to a cursor. Since cursor variables are pointers, they can be passed and returned as parameters to procedures and functions. A cursor variable can also refer to (``point to'') different cursors in its lifetime.

Some additional advantages of cursor variables are

Encapsulation: queries are centralized in the stored procedure that opens the cursor variable.

Ease of maintenance: if you need to change the cursor, you only need to make the change in one place: the stored procedure. There is no need to change each application.

Convenient security: the user of the application is the username used when the application connects to the server. The user must have execute permission on the stored procedure that opens the cursor. But the user does not need to have read permission on the tables used in the query. This capability can be used to limit access to the columns in the table, as well as access to other stored procedures.

See the PL/SQL User's Guide and Reference for a complete discussion of cursor variables.

Declaring and Opening Cursor Variables

You normally allocate memory for a cursor variable in the client application, using the appropriate ALLOCATE command. In Pro*C, you use the EXEC SQL ALLOCATE <cursor_name> command. In the OCI, you use the Cursor Data Area.

Starting with Oracle7 release 7.3, you can use cursor variables in applications that run entirely in a single server session. You can declare cursor variables in PL/SQL subprograms, open them, and use them as parameters for other PL/SQL subprograms.

Examples

This section includes several examples of cursor variable usage in PL/SQL. For additional cursor variable examples that use the programmatic interfaces, see the following manuals:

Programmer's Guide to the Oracle Pro*C/C++ Precompiler

Programmer's Guide to the Oracle Precompilers

Programmer's Guide to the Oracle Call Interface

SQL*Module User's Guide and Reference

Fetching Data

The following package defines a PL/SQL cursor variable type EMP_VAL_CV_TYPE, and two procedures. The first procedure opens the cursor variable, using a bind variable in the WHERE clause. The second procedure (FETCH_EMP_DATA) fetches rows from the EMP table using the cursor variable.

CREATE OR REPLACE PACKAGE emp_data AS

  TYPE emp_val_cv_type IS REF CURSOR RETURN emp%ROWTYPE;

  PROCEDURE open_emp_cv (emp_cv      IN OUT emp_val_cv_type,
                         dept_number     IN INTEGER); 
  PROCEDURE fetch_emp_data (emp_cv       IN emp_val_cv_type,
                            emp_row     OUT emp%ROWTYPE);

END emp_data;

CREATE OR REPLACE PACKAGE BODY emp_data AS

  PROCEDURE open_emp_cv (emp_cv      IN OUT emp_val_cv_type,
                         dept_number     IN INTEGER) IS
  BEGIN
    OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = dept_number;
  END open_emp_cv;

  PROCEDURE fetch_emp_data (emp_cv       IN emp_val_cv_type,
                            emp_row     OUT emp%ROWTYPE) IS
  BEGIN
    FETCH emp_cv INTO emp_row;
  END fetch_emp_data;
END emp_data;

The following example shows how you can call the EMP_DATA package procedures from a PL/SQL block:

DECLARE
-- declare a cursor variable
  emp_curs emp_data.emp_val_cv_type;

  dept_number dept.deptno%TYPE;
  emp_row emp%ROWTYPE;

BEGIN
  dept_number := 20;

-- open the cursor using a variable
  emp_data.open_emp_cv(emp_curs, dept_number);

-- fetch the data and display it
  LOOP
    emp_data.fetch_emp_data(emp_curs, emp_row);
    EXIT WHEN emp_curs%NOTFOUND;
    DBMS_OUTPUT.PUT(emp_row.ename || '  ');
    DBMS_OUTPUT.PUT_LINE(emp_row.sal);
  END LOOP;
END;

Implementing Variant Records

The power of cursor variables comes from their ability to point to different cursors. In the following package example, a discriminant is used to open a cursor variable to point to one of two different cursors:

CREATE OR REPLACE PACKAGE emp_dept_data AS

  TYPE cv_type IS REF CURSOR;

  PROCEDURE open_cv (cv          IN OUT cv_type,
                     discrim     IN     POSITIVE); 

END emp_dept_data;
/

CREATE OR REPLACE PACKAGE BODY emp_dept_data AS

  PROCEDURE open_cv (cv      IN OUT cv_type,
                     discrim IN     POSITIVE) IS


  BEGIN
    IF discrim = 1 THEN
      OPEN cv FOR SELECT * FROM emp WHERE sal > 2000;
    ELSIF discrim = 2 THEN
      OPEN cv FOR SELECT * FROM dept;
    END IF;
  END open_cv;

END emp_dept_data;

You can call the OPEN_CV procedure to open the cursor variable and point it to either a query on the EMP table or on the DEPT table. How would you use this? The following PL/SQL block shows that you can fetch using the cursor variable, then use the ROWTYPE_MISMATCH predefined exception to handle either fetch:

DECLARE
  emp_rec  emp%ROWTYPE;
  dept_rec dept%ROWTYPE;
  cv       emp_dept_data.cv_type;

BEGIN
  emp_dept_data.open_cv(cv, 1); -- open CV for EMP fetch
  FETCH cv INTO dept_rec;       -- but fetch into DEPT record
                                -- which raises ROWTYPE_MISMATCH
  DBMS_OUTPUT.PUT(dept_rec.deptno);
  DBMS_OUTPUT.PUT_LINE('  ' || dept_rec.loc);

EXCEPTION
  WHEN ROWTYPE_MISMATCH THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE
           ('Row type mismatch, fetching EMP data...');
      FETCH cv into emp_rec;
      DBMS_OUTPUT.PUT(emp_rec.deptno);
      DBMS_OUTPUT.PUT_LINE('  ' || emp_rec.ename);
    END;
END;