[Chapter 6] 6.4 Declaring Cursors

6.4 Declaring Cursors

To use an explicit cursor, you must first declare it in the declaration section of your PL/SQL block or in a package, as shown here:

CURSOR cursor_name [ ( [ parameter [, parameter ...] ) ]
   [ RETURN return_specification ]
   IS SELECT_statement;

where cursor_name is the name of the cursor, return_specification is an optional RETURN clause for the cursor, and SELECT_statement is any valid SQL SELECT statement. You can also pass arguments into a cursor through the optional parameter list described in Section 6.10, "Cursor Parameters" .

Once you have declared a cursor you can then OPEN it and FETCH from it.

Here are some examples of explicit cursor declarations:

A cursor without parameters. The result set of this cursor is the set of company ID numbers for each record in the table:
```
CURSOR company_cur IS
   SELECT company_id FROM company;
```
A cursor with parameters. The result set of this cursor is the name of the company which matches the company ID passed to the cursor via the parameter:
```
CURSOR name_cur (company_id_in IN NUMBER)
IS
   SELECT name FROM company
    WHERE company_id = company_id_in;
```
A cursor with a RETURN clause. The result set of this cursor is all columns (same structure as the underlying table) from all employee records in department 10:

CURSOR emp_cur RETURN employee%ROWTYPE
IS
   SELECT * FROM employee
    WHERE department_id = 10;

6.4.1 The Cursor Name

The name of an explicit cursor is not a PL/SQL variable. Instead, it is an undeclared identifier used to point to or refer to the query. You cannot assign values to a cursor, nor can you use it in an expression.

As a result, both of the executable statements after the BEGIN line below are invalid:

DECLARE
   CURSOR company_cur IS
      SELECT company_id FROM company;
BEGIN
   company_cur := 15009; /* Invalid syntax */

   IF company_cur IS NOT NULL THEN ... ;  /* Invalid syntax */

In compiling either statement, you will receive the following error:

PLS-0321: expression 'COMPANY_CUR' is inappropriate as the left-hand side of an assignment statement.

The name of a cursor can be up to 30 characters in length and follows the rules for any other identifier in PL/SQL.

6.4.2 PL/SQL Variables in a Cursor

Because a cursor must be associated with a SELECT statement, every cursor must reference at least one table from the database and determine from that (and from the WHERE clause) which rows will be returned in the active set. This does not mean, however, that a cursor's SELECT may only return database information. The list of expressions that appears after the SELECT keyword and before the FROM keyword is called the select list.

In native SQL, this select list may contain both columns and expressions (SQL functions on those columns, constants, etc.). In PL/SQL, the select list of a SELECT may contain PL/SQL variables, expressions, and even functions (PL/SQL Release 2.1 and above).

In the following cursor, the SELECT statement retrieves rows based on the employee table, but the information returned in the select list contains a combination of table columns, a PL/SQL variable, and a bind variable from the host environment (such as an Oracle Forms item):

DECLARE
   /* A local PL/SQL variable */
   projected_bonus NUMBER := 1000;
   /*
   || Cursor adds $1000 to the salary of each employee
   || hired more than three years ago.
   */
   CURSOR employee_cur
   IS
      SELECT employee_id,
             salary + projected_bonus new_salary, /* Column alias */
            :review.evaluation                    /* Bind variable */
        FROM employee
       WHERE hiredate < ADD_MONTHS (SYSDATE, -36);

BEGIN
   ...
END;

You can reference local PL/SQL program data (PL/SQL variables and constants), as well as host language bind variables in the WHERE, GROUP, and HAVING clauses of the cursor's SELECT statement.

6.4.3 Identifier Precedence in a Cursor

Be careful about naming identifiers when you mix PL/SQL variables in with database columns. It is, for instance, common practice to give a variable the same name as the column whose data it is supposed to represent. This makes perfect sense until you want to reference those local variables in a SQL statement along with the column.

In the following example, I want to fetch each employee who was hired more than three years ago and, using a local variable, add $1000 to their salary. The employee table has a column named "salary." Unfortunately, this procedure relies on a local variable of the same name to achieve its ends. Although this code will compile without error, it will not produce the desired result:

PROCEDURE improve_QOL
IS
   /* Local variable with same name as column: */
   salary NUMBER := 1000;

   CURSOR double_sal_cur
   IS
      SELECT salary + salary
        FROM employee
       WHERE hiredate < ADD_MONTHS (SYSDATE, -36);
BEGIN

Instead of adding $1000 to each person's salary, this code will instead double his or her salary. Inside the SQL statement, any unqualified reference to "salary" is resolved by using the column named "salary."

I could achieve the desired effect by qualifying the PL/SQL variable with the name of the procedure, as follows:

CURSOR double_sal_cur
IS
   SELECT salary + 
improve_QOL.
salary
     FROM employee
    WHERE hiredate < ADD_MONTHS (SYSDATE, -36);

In this situation, you are informing the compiler that the second reference to salary is that variable "owned" by the improve_QOL procedure. It will then add the current value of that variable to the salary column value.

I do not, however, recommend that you make use of qualified local variable names in this way. If your local variable names conflict with database column or table names, change the name of your variable. Best of all, avoid this kind of duplication by using a standard naming convention for local variables which represent database information.

6.4.4 The Cursor RETURN Clause

One of the most significant new features in PL/SQL Version 2 is the full support for packages and the resulting modularization of code that is now possible with that construct. Packages introduce an enhancement to the way you can declare a cursor: the RETURN clause.

When you group programs together into a package, you can make only the specification, or header information, of those programs available to developers. Although a developer can tell from the specification what the module is called and how to call it, he or she does not need to see any of the underlying code. As a result, you can create true black boxes behind which you can hide complex implementational details.

With Version 2 of PL/SQL you can accomplish the same objective with cursors by using the cursor RETURN clause. The RETURN clause allows you to create a specification for a cursor which is separate from its body (the SELECT statement). You may then place cursors in packages and hide the implementation details from developers.

Consider the following cursor declaration with RETURN clause:

CURSOR caller_cur (id_in IN NUMBER) RETURN caller%ROWTYPE
IS
   SELECT * FROM caller WHERE caller_id = id_in;

The specification of the caller_cur cursor is:

CURSOR caller_cur (id_in IN NUMBER) RETURN caller%ROWTYPE

while the body of the caller_cur cursor is:

SELECT * FROM caller WHERE caller_id = id_in;

Everything up to but not including the IS keyword is the specification, while everything following the IS keyword is the body.

You can include a RETURN clause for any cursor you write in PL/SQL Version 2, but it is required only for cursors which are contained in a package specification.

The RETURN clause may be made up of any of the following datatype structures:

A record defined from a database table, using the %ROWTYPE attribute
A record defined from a programmer-defined record

Here is an example of a cursor defined in a package. First, the package specification provides the name of the cursor and the RETURN datatype (an entire row from the company table):

PACKAGE company
IS
   CURSOR company_cur (id_in NUMBER) RETURN company%ROWTYPE;
END company;

Then the following package body repeats the cursor specification and adds the SQL statement:

PACKAGE BODY company
IS
   CURSOR company_cur (id_in NUMBER) RETURN company%ROWTYPE
   IS
      SELECT * FROM company
       WHERE company_id = id_in;

END company;

The number of expressions in the cursor's select list must match the number of columns in the record identified by table_name%ROWTYPE or PLSQL_record%ROWTYPE. The datatypes of the elements must also be compatible. If the second element in the select list is type NUMBER, then the second column in the RETURN record cannot be type VARCHAR2 or BOOLEAN.

Why place cursors in a package? For the same reasons you would place a procedure or a function in a package: a package is a collection of logically related objects. By grouping the code into a package you make it easier for a developer to identify and use the code (see Chapter 16, Packages , for more details). Packaged cursors are essentially black boxes. This is advantageous to developers because they never have to code or even see the SELECT statement. They only need to know what records the cursor returns, in what order it returns them, and which columns are in the column list.

When cursor information is limited on this kind of "need to know" basis, it protects developers and the overall application from change. Suppose that a year from now the WHERE clause of a query has to change. If a packaged cursor is not used, then each program that has a hardcoded or local cursor will have to be modified to meet the new specification. If, on the other hand, all developers simply access the same cursor, then changes will only need to be made to that packaged declaration of the cursor. The programs can then be recompiled to automatically support this change.