In PL/SQL Release 2.3, available with the release of Oracle Server Release 7.3, you can create and use cursor variables. Unlike an explicit cursor, which names the PL/SQL work area for the result set, a cursor variable is instead a (Release 2.3) reference to that work area. Explicit and implicit cursors are both static in that they are tied to specific queries. The cursor variable can be opened for any query, even different queries within a single program execution.
The most important benefit of the cursor variable is that it provides a mechanism for passing results of queries (the rows returned by fetches against a cursor) between different PL/SQL programs -- even between client and server PL/SQL programs. Prior to PL/SQL Release 2.3, you would have had to fetch all data from the cursor, store it in PL/SQL variables (perhaps a PL/SQL table), and then pass those variables as arguments. With cursor variables, you simply pass the reference to that cursor. This improves performance and streamlines your code.
It also means that the cursor is, in effect, shared among the programs which have access to the cursor variable. In a client-server environment, for example, a program on the client side could open and start fetching from the cursor variable, then pass that variable as an argument to a stored procedure on the server. This stored program could then continue fetching and pass control back to the client program to close the cursor. You can also perform the same steps between different stored programs, on the same or different database instances.
This process, shown in Figure 6.2 , offers dramatic new possibilities for data sharing and cursor management in PL/SQL programs.
The code you write to take advantage of cursor variables is very similar to that for explicit cursors. The following example declares a cursor type (called a REF CURSOR type) for the company table, then opens, fetches from, and closes the cursor:
DECLARE /* Create the cursor type. */ TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE; /* Declare a cursor variable of that type. */ company_curvar company_curtype; /* Declare a record with same structure as cursor variable. */ company_rec company%ROWTYPE; BEGIN /* Open the cursor variable, associating with it a SQL statement. */ OPEN company_curvar FOR SELECT * FROM company; /* Fetch from the cursor variable. */ FETCH company_curvar INTO company_rec; /* Close the cursor object associated with variable. */ CLOSE company_curvar; END;
That looks an awful lot like explicit cursor operations, except for the following:
While the syntax is very similar, the fact that the cursor variable is a variable opens up many new opportunities in your programs. These are explored in the remainder of this section.
Cursor variables let you:
One of the key design requirements for cursor variables was that as much as possible the semantics used to manage cursor objects would be the same as that of static cursors. While the declaration of a cursor variable and the syntax for opening it are enhanced, the following cursor operations are unchanged for cursor variables:
Because the syntax for these aspects of cursor variables remain unchanged, I won't cover them again in the remainder of this section. Instead I will focus on the new capabilities available and the changed syntax required for cursor variables.
The syntax for creating a referenced cursor type is as follows:
TYPE cursor_type_name IS REF CURSOR [ RETURN return_type ];
where cursor_type_name is the name of the type of cursor and return_type is the RETURN data specification for the cursor type. The return_type can be any of the data structures valid for a normal cursor RETURN clause, defined using the %ROWTYPE attribute or by referencing a previously-defined record TYPE.
Notice that the RETURN clause is optional with the REF CURSOR type statement. Both of the following declarations are valid:
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE; TYPE generic_curtype IS REF CURSOR;
The first form of the REF CURSOR statement is called a strong type because it attaches a record type (or row type) to the cursor variable type at the moment of declaration. Any cursor variable declared using that type can only be used with SQL statement and FETCH INTO data structures which match the specified record type. The advantage of a strong REF TYPE is that the compiler can determine whether or not the developer has properly matched up the cursor variable's FETCH statements with its cursor object's query list.
The second form of the REF CURSOR statement, in which the RETURN clause is missing, is called a weak type . This cursor variable type is not associated with any record data structure. Cursor variables declared without the RETURN clause can be used in much more flexible ways than the strong type. They can be used with any query, with any rowtype structure -- varying even within the course of a single program.
The syntax for declaring a cursor variable is:
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.
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.
You assign a value (the cursor object) to a cursor when you OPEN the cursor. So the syntax for the OPEN statement is now modified in PL/SQL Release 2.3 to accept a SELECT statement after the FOR clause, as shown below:
OPEN cursor_name FOR select_statement;
where cursor_name is the name of a cursor or cursor variable and select_statement is a SQL SELECT statement.
For strong REF CURSOR type cursor variables, the structure of the SELECT statement (the number and datatypes of the columns) must match or be compatible with the structure specified in the RETURN clause of the type statement. Figure 6.4 offers an example of the kind of compatibility required. Figure 6.4 " contains the full set of compatibility rules.
If cursor_name is a cursor variable defined with a weak REF CURSOR type, you can OPEN it for any query, with any structure. In the following example, I open (assign a value to) the cursor variable twice, with two different queries:
DECLARE TYPE emp_curtype IS REF CURSOR; emp_curvar emp_curtype; BEGIN OPEN emp_curvar FOR SELECT * FROM emp; OPEN emp_curvar FOR SELECT employee_id FROM emp; OPEN emp_curvar FOR SELECT company_id, name FROM company; END;
That last open didn't even have anything to do with the employee table!
If the cursor variable has not yet been assigned to any cursor object, the OPEN FOR statement implicitly creates an object for the variable.
If at the time of the OPEN the cursor variable already is pointing to a cursor object, then OPEN FOR does not create a new object. Instead, it reuses the existing object and attaches a new query to that object. The cursor object is maintained separately from the cursor or query itself.
FETCH <cursor variable name> INTO <record name>; FETCH <cursor variable name> INTO <variable name>, <variable name> ...;
When the cursor variable was declared with a strong REF CURSOR type, the PL/SQL compiler makes sure that the data structure(s) listed after the INTO keyword are compatible with the structure of the query associated with cursor variable.
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.
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:
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 ]
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;
This section examines in more detail the rules and issues regarding the use of cursor variables in your programs. This includes rowtype matching rules, cursor variable aliases, and scoping issues.
Remember that the cursor variable is a reference to a cursor object or query in the database. It is not the object itself. A cursor variable is said to "refer to a given query" if either of the following is true:
You can perform assignment operations with cursor variables and also pass these variables as arguments to procedures and functions. In order to perform such actions between cursor variables (and to bind a cursor variable to a parameter), the different cursor variables must follow a set of compile-time and runtime rowtype matching rules.
These are the rules that PL/SQL follows at compile-time:
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.
These are the rules that PL/SQL follows at run time:
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.
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.
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;
You can pass a cursor variable as an argument in a call to a procedure or function. When you use a cursor variable in the parameter list of a program, you need to specify the mode of the parameter and the datatype (the REF CURSOR type).
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:
See Chapter 16 for more information on this feature.
Just like other parameters, a cursor variable argument can have one of the following three modes:
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:
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.