6.12.3.1 Declaring cursor variables

The syntax for declaring a cursor variable is:

cursor_name cursor_type_name;

where cursor_name is the name of the cursor and cursor_type_name is the name of the type of cursor previously defined with a TYPE statement.

Here is an example of the creation of a cursor variable:

DECLARE
   /* Create a cursor type for sports cars. */
   TYPE sports_car_cur_type IS REF CURSOR RETURN car%ROWTYPE;

   /* Create a cursor variable for sports cars. */
   sports_car_cur sports_car_cur_type;
BEGIN
   ...
END;

It is very important to distinguish between declaring a cursor variable and creating an actual cursor object -- the result set identified by the cursor SQL statement. The cursor variable is nothing more than a reference or pointer. A constant is nothing more than a value, whereas a variable points to its value. Similarly, a static cursor acts as a constant, whereas a cursor variable points to a cursor object. These distinctions are shown in Figure 6.3. Notice that two different cursor variables in different programs both refer to the same cursor object.

Figure 6.3: The referencing character of cursor variables

Declaration of a cursor variable does not create a cursor object. To do that, you must instead use the OPEN FOR syntax to create a new cursor object and assign it to the variable.

6.12.5.1 Strong and weak REF CURSOR types

If the cursor variable is of the weak REF CURSOR type, the PL/SQL compiler cannot perform the same kind of check. Such a cursor variable can FETCH into any data structures, because the REF CURSOR type it is not identified with a rowtype at the time of declaration. At compile time, there is no way to know which cursor object (and associated SQL statement) will be assigned to that variable.

Consequently, the check for compatibility must happen at run time, when the FETCH is about to be executed. At this point, if the query and the INTO clause do not structurally match (and PL/SQL will use implicit conversions if necessary and possible), then the PL/SQL runtime engine will raise the predefined ROWTYPE_MISMATCH exception.

6.12.5.2 Handling the ROWTYPE_MISMATCH exception

Before PL/SQL actually performs its FETCH, it checks for compatibility. As a result, you can trap the ROWTYPE_MISMATCH exception and attempt to FETCH from the cursor variable using a different INTO clause -- and you will not have skipped any rows in the result set.

Even though you are executing a second FETCH statement in your program, you will still retrieve the first row in the result set of the cursor object's query. This functionality comes in especially handy for weak REF CURSOR types.

In the following example, a centralized real estate database stores information about properties in a variety of tables, one for homes, another for commercial properties, etc. There is also a single, central table which stores an address and a building type (home, commercial, etc.). I use a single procedure to open a weak REF CURSOR variable for the appropriate table, based on the street address. Each individual real estate office can then call that procedure to scan through the matching properties:

1. Define my weak REF CURSOR type:

   TYPE building_curtype IS REF CURSOR;

2. Create the procedure. Notice that the mode of the cursor variable parameter is IN OUT:

   PROCEDURE open_site_list
      (address_in IN VARCHAR2,
       site_cur_inout IN OUT building_curtype)
   IS
      home_type CONSTANT INTEGER := 1;
      commercial_type CONSTANT INTEGER := 2;
   
      /* A static cursor to get building type. */
      CURSOR site_type_cur IS
         SELECT site_type FROM property_master
          WHERE address = address_in;
      site_type_rec site_type_cur%ROWTYPE;
   
   BEGIN
      /* Get the building type for this address. */
      OPEN site_type_cur;
      FETCH site_type_cur INTO site_type_rec;
      CLOSE site_type_cur;
   
      /* Now use the site type to select from the right table.*/
      IF site_type_rec.site_type =  home_type
      THEN
         /* Use the home properties table. */
         OPEN site_cur_inout FOR
            SELECT * FROM home_properties
             WHERE address LIKE '%' || address_in || '%';
   
      ELSIF site_type_rec.site_type =  commercial_type
      THEN
         /* Use the commercial properties table. */
         OPEN site_cur_inout FOR
            SELECT * FROM commercial_properties
             WHERE address LIKE '%' || address_in || '%';
      END IF;
   END open_site_list;

3. Now that I have my open procedure, I can use it to scan properties.

In the following example, I pass in the address and then try to fetch from the cursor, assuming a home property. If the address actually identifies a commercial property, PL/SQL will raise the ROWTYPE_MISMATCH exception (incompatible record structures). The exception section then fetches again, this time into a commercial building record, and the scan is complete.[2]

[2] The "prompt" and "show" programs referenced in the example interact with users and are not documented here.

DECLARE
   /* Declare a cursor variable. */
   building_curvar building_curtype;

   /* Define record structures for two different tables. */
   home_rec home_properties%ROWTYPE;
   commercial_rec commercial_properties%ROWTYPE;
BEGIN
   /* Get the address from the user. */
   prompt_for_address (address_string);

   /* Assign a query to the cursor variable based on the address. */
   open_site_list (address_string, building_curvar);

   /* Give it a try! Fetch a row into the home record. */
   FETCH building_curvar INTO home_rec;

   /* If I got here, the site was a home, so display it. */
   show_home_site (home_rec);
EXCEPTION
   /* If the first record was not a home... */
   WHEN ROWTYPE_MISMATCH
   THEN
      /* Fetch that same 1st row into the commercial record. */
      FETCH building_curvar INTO commercial_rec;

      /* Show the commercial site info. */
      show_commercial_site (commercial_rec);
END;

6.12.6.1 Compile-time rowtype matching rules

These are the rules that PL/SQL follows at compile-time:

• Two cursor variables (including procedure parameters) are compatible for assignments and argument passing if any of the following are true:

  • Both variables (or parameters) are of a strong REF CURSOR type with the same <rowtype_name>.

  • Both variables (or parameters) are of some weak REF CURSOR type, regardless of the <rowtype_name>.

  • One variable (parameter) is of any strong REF CURSOR type, and the other is of any weak REF CURSOR type.

• A cursor variable (parameter) of a strong REF CURSOR type may be OPEN FOR a query that returns a rowtype which is structurally equal to the <rowtype_name> in the original type declaration.

• A cursor variable (parameter) of a weak REF CURSOR type may be OPEN FOR any query. The FETCH from such a variable is allowed INTO any list of variables or record structure.

In other words, if either of the cursor variables are of the weak REF CURSOR type, then the PL/SQL compiler cannot really validate whether the two different cursor variables will be compatible. That will happen at runtime; the rules are covered in the next section.

6.12.6.2 Run-time rowtype matching rules

These are the rules that PL/SQL follows at run time:

• A cursor variable (parameter) of a weak REF CURSOR type may be made to refer to a query of any rowtype regardless of the query or cursor object to which it may have referred earlier.

• A cursor variable (parameter) of a strong REF CURSOR type may be made to refer only to a query which matches structurally the <rowtype_name> of the RETURN clause of the REF CURSOR type declaration.

• Two records (or lists of variables) are considered structurally matching with implicit conversions if both of the following are true:

  • The number of fields is the same in both records (lists).

  • For each field in one record (or variable on one list), a corresponding field in the second list (or variable in second list) has the same PL/SQL datatype, or one which can be converted implicitly by PL/SQL to match the first.

• For a cursor variable (parameter) used in a FETCH statement, the query associated with the cursor variable must structurally match with implicit conversions the record or list of variables of the INTO clause of the FETCH statement. This is, by the way, the same rule used for static cursors.

6.12.6.3 Cursor variable aliases

If you assign one cursor variable to another cursor variable, those two cursor variables become aliases for the same cursor object. They share the reference to the cursor object (result set of the cursor's query). An action taken against the cursor object through one variable is also available to and reflected in the other variable.

The following anonymous block illustrates the way cursor aliases work:

1  DECLARE
2     TYPE curvar_type IS REF CURSOR;
3     curvar1 curvar_type;
4     curvar2 curvar_type;
5     story fairy_tales%ROWTYPE;
6  BEGIN
7     /* Assign cursor object to curvar1. */
8     OPEN curvar1 FOR SELECT * FROM fairy_tales;
9
10    /* Assign same cursor object to curvar2. */
11    curvar2 := curvar1;
12
13    /* Fetch first record from curvar1. */
14    FETCH curvar1 INTO story;
15
16    /* Fetch second record from curvar2. */
17    FETCH curvar2 INTO story;
18
19    /* Close the cursor object by referencing curvar2. */
20    CLOSE curvar2;
21
22    /* This statement raises INVALID_CURSOR exception! */
23    FETCH curvar1 INTO story;
24 END;

The following table is an explanation of cursor variable actions.

Any change of state in a cursor object will be seen through any cursor variable which is an alias to that cursor object.

6.12.6.4 Scope of cursor object

The scope of a cursor variable is the same as that of a static cursor: the PL/SQL block in which the variable is declared (unless declared in a package, which makes the variable globally accessible). The scope of the cursor object to which a cursor variable is assigned, however, is a different matter.

Once an OPEN FOR creates a cursor object, that cursor object remains accessible as long as at least one active cursor variable refers to that cursor object. This means that you can create a cursor object in one scope (PL/SQL block) and assign it to a cursor variable. Then, by assigning that cursor variable to another cursor variable with a different scope, the cursor object remains accessible even if the original cursor variable has gone out of scope.

In the following example I use nested blocks to demonstrate how the cursor object can persist outside of the scope in which it was originally created:

DECLARE
   /* Define weak REF CURSOR type, cursor variable
      and local variable */
   TYPE curvar_type IS REF CURSOR;
   curvar1 curvar_type;
   do_you_get_it VARCHAR2(100);
BEGIN
   /*
   || Nested block which creates the cursor object and
   || assigns it to the curvar1 cursor variable.
   */
   DECLARE
      curvar2 curvar_type;
   BEGIN
      OPEN curvar2 FOR SELECT punch_line FROM jokes;
      curvar1 := curvar2;
   END;
   /*
   || The curvar2 cursor variable is no longer active,
   || but "the baton" has been passed to curvar1, which
   || does exist in the enclosing block. I can therefore
   || fetch from the cursor object, through this other
   || cursor variable.
   */
   FETCH curvar1 INTO do_you_get_it;
END;

6.12.7.1 Identifying the REF CURSOR type

In your program header, you must identify the REF CURSOR type of your cursor variable parameter. To do this, that cursor type must already be defined.

If you are creating a local module within another program (see Chapter 15 for more information about local modules), then you can also define the cursor type in the same program. It will then be available for the parameter. This approach is shown below:

DECLARE
   /* Define the REF CURSOR type. */
   TYPE curvar_type IS REF CURSOR RETURN company%ROWTYPE;

   /* Reference it in the parameter list. */
   PROCEDURE open_query (curvar_out OUT curvar_type)
   IS
      local_cur curvar_type;
   BEGIN
      OPEN local_cur FOR SELECT * FROM company;
      curvar_out := local_cur;
   END;
BEGIN
   ...
END;

If you are creating a standalone procedure or function, then the only way you can reference a pre-existing REF CURSOR type is by placing that type statement in a package. All variables declared in the specification of a package act as globals within your session, so you can then reference this cursor type using the dot notation as shown below:

1. Create the package with a REF CURSOR type declaration:

   PACKAGE company
   IS
      /* Define the REF CURSOR type. */
      TYPE curvar_type IS REF CURSOR RETURN company%ROWTYPE;
   END package;

2. In a standalone procedure, reference the REF CURSOR type by prefacing the name of the cursor type with the name of the package:

   PROCEDURE open_company (curvar_out OUT company.curvar_type) IS
   BEGIN
      ...
   END;

See Chapter 16 for more information on this feature.

6.12.7.2 Setting the parameter mode

Just like other parameters, a cursor variable argument can have one of the following three modes:

IN

Can only be read by program

OUT

Can only be written to by program

IN OUT

Read/write in program

Remember that the value of a cursor variable is the reference to the cursor object and not the state of the cursor object. In other words, the value of a cursor variable does not change after you fetch from or close a cursor.

Only two operations, in fact, may change the value of a cursor variable change, that is, the cursor object to which the variable points:

• An assignment to the cursor variable

• An OPEN FOR statement

If the cursor variable already pointed to a cursor object, then the OPEN FOR wouldn't actually change the reference. It would simply change the query associated with the object.

The FETCH and CLOSE operations affect the state of the cursor object, but not the reference to the cursor object itself, which is the value of the cursor variable.

Here is an example of a program which has cursor variables as parameters:

PROCEDURE assign_curvar
   (old_curvar_in IN company.curvar_type,
    new_curvar_out OUT company.curvar_type)
IS
BEGIN
   new_curvar_out := old_curvar_in;
END;

This procedure copies the old company cursor variable to the new variable. The first parameter is an IN parameter because it appears only on the right-hand side of the assignment. The second parameter must be an OUT (or IN OUT) parameter, because its value is changed inside the procedure. Notice that the curvar_type is defined within the company package.