11.5 How do I…Use cursor variables for more flexibility?Problem
I need to declare a cursor variable in my PL/SQL code to associate it with different cursors dynamically. I want to assign new cursors to the cursor variable and pass the cursor variable as a parameter to stored procedures and functions. How do I create cursor variables for more flexibility?
Technique
A cursor is tied to a static query, whereas a cursor variable is dynamically associated with a query at runtime. If you associate different queries with a cursor variable, columns returned by each query must match the return type of the cursor variable. A cursor variable is similar to a cursor because you can fetch the current row from the result set by using the FETCH statement. Think of a cursor variable as a pointer to which you can assign new queries. The real advantage of using cursor variables is that you can pass them as parameters to functions and procedures.
In PL/SQL, you can use the REF type modifier to construct a reference (or pointer) to an object. The datatype of the pointer is ref_object_type, where ref is simply a short form for REFERENCE and object_type is the name of an existing object type. Because a cursor variable is a pointer to a cursor, it has a datatype of REF CURSOR. Creating references to other object types is covered in Chapter 16.
Two steps are required to create cursor variables. First, you define a REF CURSOR type; then, you declare cursor variables of that type. You can define REF CURSOR types using the syntax:
TYPE ref_cursor_type IS REF CURSOR RETURN return_type;
ref_cursor_type is a new datatype used in subsequent declarations of cursor variables, and return_type is a record type corresponding with the columns returned by any cursor associated with the cursor variable. Refer to How-To 10.8 for more information on record types. The RETURN clause is optional, and when included, it causes the cursor variable to be strongly typed. A weakly typed cursor variable can be defined by omitting the RETURN clause as:
TYPE ref_cursor_type IS REF CURSOR;
Columns returned by a query must match the return type of a strongly typed cursor variable or else a compilation error occurs. Any query, however, can be associated with a weakly typed cursor variable. After the type is defined, cursor variables can be declared like any other variables, for example:
DECLARE cv_ref_ref_cursor_type;
The OPEN FOR statement is used to open a cursor variable and associate a query with it. The syntax for opening a cursor variable is:
OPEN cv_ref FOR query; The following steps show how you can control cursor variables in your PL/SQL programs.
Steps
1. Run SQL*Plus and connect as the WAITE user account. CHP11_10.SQL, shown in Figure 11.10, creates the sample tables and data used in this How-To. Run the file to create the sample table and data. The DEPT11 and EMP11 tables created by the script are used in the following steps.
SQL> START CHP11_10.sql
Table created.
1 row created.
1 row created.
Table created.
1 row created.
2. Run the CHP11_11.SQL file in SQL*Plus, as shown in Figure 11.11. The PL/SQL block contained in the file uses a cursor variable to fetch records from the DEPT11 table.
Line 2 defines a cursor reference type using the REF CURSOR keywords. The cursor reference is strongly typed as the record type that must be returned by any cursor associated with the cursor variable defined using the RETURN clause. A cursor variable of the cursor reference type is declared in line 3. A record variable corresponding with all columns of the DEPT11 table is declared in line 4. Lines 6 and 7 open a cursor variable by associating it with a query on the DEPT11 table. Note the use of the FOR keyword in the OPEN statement. The loop in lines 8 through 13 is used to fetch records from the cursor variable. There is no difference in this loop if you use either a cursor or a cursor variable.
3. Run the CHP11_12.SQL file in SQL*Plus. The package example contained in the file uses a procedure to associate the cursor variable to point to one of two different cursors, based on the value of a selector parameter. Figure 11.12 shows the results of the operation.
First, a package specification is created. A cursor reference type is defined in line 2 of the package specification. A weakly typed cursor reference is defined by omitting the RETURN clause after the REF CURSOR keywords. Lines 3 and 4 declare the procedure contained in the package.
Second, the package body is created using the CREATE OR REPLACE PACKAGE BODY keywords. Note that the cursor variable is an IN OUT parameter to the procedure. In lines 6 and 8, the cursor variable is associated with a query on the DEPT11 table or the EMP11 table depending on the value of the TBL parameter. Because the cursor reference is weakly typed, cursors returning different record types can be associated with the cursor variable. The next step makes use of this package.
4. Run the CHP11_13.SQL file in SQL*Plus, as shown in Figure 11.13. The PL/SQL block contained in the file calls the OPEN_CV procedure to open the cursor variable and point it to a query on either the EMP11 or DEPT11 table. For the sake of simplicity, a single row is fetched from the cursor variable in each case.
Line 2 declares a cursor variable of the cursor reference type defined in the package specification of the previous step. A record variable corresponding with columns of the DEPT11 table is declared in line 3, and a record variable corresponding with columns of the EMP11 table is declared in line 4. Line 6 calls the OPEN_CV procedure to open a cursor variable by associating it with a query on the DEPT11 table. A record is fetched from the cursor variable, and column values are displayed. Line 10 closes the cursor variable before reopening it by calling the OPEN_CV procedure in line 11 to point it to a query on the EMP11 table. The first record in the EMP11 table is fetched using the cursor variable, and column values are displayed.
How It Works
The REF CURSOR keywords are used to define a cursor reference type, and cursor variables of that type can then be declared. If the REF CURSOR type is defined in a package, you can declare cursor variables of that cursor reference type in any PL/SQL block, function, or procedure.
Columns returned by a query must match the return type of a strongly typed cursor variable, whereas a weakly typed cursor variable can be associated with any query. This enables you to declare cursor variables without knowing the cursor it will receive.
Three steps are required to control a cursor variable: OPEN FOR, FETCH, and CLOSE. Cursor variables follow the same scoping and instantiation rules as any other variables declared in a block.
Step 1 creates the sample tables used in the following steps. Step 2 demonstrates the use of a strongly typed cursor variable. Columns returned by an associated query must be type-compatible with the return type of the cursor variable or else a compilation error occurs. Step 3 creates a package to implement a weakly typed cursor reference. Step 4 shows the use of a weakly typed cursor variable. If the records fetched from the cursor variable don’t match that of the receiving record variable, a ROW_MISMATCH predefined exception is raised. This exception can be trapped to try a fit into a record variable with the expected datatypes of columns returned. If you try to fetch, close, or apply cursor attributes to a cursor variable that is not open, the INVALID_CURSOR predefined exception is raised.
Comments
It is a good practice to use strongly typed cursor variables types because they are less error-prone: The compiler lets you associate a strongly typed cursor variable only with type-compatible queries. Weakly typed cursor variables are more flexible, however, because the compiler lets you associate a weakly typed cursor variable with any query. Cursor variables are available to every PL/SQL client and can be bind variables. The associated query can reference bind variables as well.
The %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN cursor attributes (refer to How-To 11.3) can be used with cursor variables, which cannot be declared in a package because they don’t have a persistent state. Parameters cannot be passed to a cursor variable, nor can you associate a FOR UPDATE query with a cursor variable. Cursor variables cannot be used with dynamic SQL, a NULL value cannot be assigned to a cursor variable, and collection elements cannot store cursor variable values. Always remember that creating a cursor variable creates a pointer, not an object.