PL/SQL User's Guide and Reference

Packaging Cursors

You can separate a cursor specification from its body for placement in a package. That way, you can change the cursor body without having to change the cursor specification. You code the cursor specification in the package specification using the syntax

CURSOR cursor_name [(parameter[, parameter]...)]
    RETURN return_type;

where return_type must represent a record or a row in a database table. In the following example, you use the %ROWTYPE attribute to provide a record type that represents a row in the database table emp:

CREATE PACKAGE emp_actions AS
   /* Declare cursor specification. */
   CURSOR c1 RETURN emp%ROWTYPE;
   ...
END emp_actions;

CREATE PACKAGE BODY emp_actions AS
   /* Define cursor body. */
   CURSOR c1 RETURN emp%ROWTYPE IS
      SELECT * FROM emp WHERE sal > 3000;
   ...
END emp_actions;

The cursor specification has no SELECT statement because the RETURN clause defines the datatype of the result value. However, the cursor body must have a SELECT statement and the same RETURN clause as the cursor specification. Also, the number and datatypes of select items in the SELECT statement must match the RETURN clause.

Packaged cursors increase flexibility. For instance, you can change the cursor body in the last example, as follows, without having to change the cursor specification:

CREATE PACKAGE BODY emp_actions AS
   /* Define cursor body. */
   CURSOR c1 RETURN emp%ROWTYPE IS
      SELECT * FROM emp WHERE deptno = 20;  -- new WHERE clause
   ...
END emp_actions;