11.4 How do I…Use cursor parameters to enhance cursor reusability?Problem
I need to use the same cursor to fetch different results, depending on the values of variables in the PL/SQL block. If I use PL/SQL variables in the cursor declaration itself, I have to write the same cursor several times, each time tying it up with a set of variables. I need to pass parameters to the cursor so that I can write the cursor once and pass parameters to it whenever I open the cursor. How do I use cursor parameters to enhance cursor reusability?
Technique
Passing parameters to cursors is similar to passing parameters to stored procedures and functions. The only difference is that OUT and IN OUT mode parameters cannot be specified with cursors. Parameters passed to a cursor are specified along with their datatypes while defining the cursor. The syntax used to define a cursor with parameters is:
CURSOR cursor_name [(parameter_name1 datatype1[, parameter_name2 datatype2]...)] IS query;
The scope of parameters is limited to the cursor definition. In other words, they cannot be accessed in the PL/SQL block in which the cursor is defined. The parameters passed can be used in the query to control the output generated. Typically, the parameters passed to the cursor are used in the WHERE clause of the SELECT statement, but they can be referenced anywhere in the query defining the cursor. Default values can be specified for the parameters using the DEFAULT keyword or using the := notation.
The OPEN statement is used to open the cursor and pass parameter values to the cursor. The syntax for opening a cursor that takes parameters looks like:
OPEN cursor_name [(parameter_value1[, parameter_value2]...)];
If parameters have default values in the cursor definition, the cursor can be opened without specifying any parameters. After the OPEN statement is executed, the query is performed using the parameter values provided in the OPEN statement, and the result set can be fetched using the FETCH statement. A cursor FOR loop can be used in a similar fashion to implicitly open a cursor that takes parameters.
If you are using packages, cursors can be packaged just like stored subprograms. You can separate the cursor declaration in the package header from the cursor definition in the package body. This way, you can change the cursor body without having to change the cursor declaration. The cursor declaration in the package header uses the following syntax:
CURSOR cursor_name [(parameter_name1 datatype1[, parameter_name2 datatype2]...)] RETURN return_type;
return_type must represent a scalar datatype or a PL/SQL user-defined record or a row in a table.
Steps
1. Run SQL*Plus and connect as the WAITE user account. If you have not done so already, run the CHP11_1.SQL file from step 1 of How-To 11.1. The DEPT11 table is created with some sample data.
2. The next step demonstrates the method used to create a cursor with parameters. Run the CHP11_8.SQL in SQL*Plus, and the PL/SQL block contained in the file creates a cursor with parameters. Cursor parameters enable the same cursor to be used with variable information in the query. The results of the operation are shown in Figure 11.8.
Lines 2 through 7 contain the declarative section of the block. The cursor defined in lines 2 to 6 uses a parameter, which is used in the WHERE clause in line 6 to control the output of the query. A default value for the parameter can be supplied using the := operator or DEFAULT keyword as with stored procedures. Line 7 uses the %ROWTYPE attribute to declare a record variable representing all columns of the DEPT11 table. Line 9 opens the cursor and passes 2 as a parameter to the cursor. A result set that matches the selection criteria in the WHERE clause of the query is then ready to be fetched from the cursor. The loop in lines 10 through 14 fetches rows from the cursor and displays the DEPT_NAME column value in SQL*Plus.
The two records with DEPT_NO values greater than two are returned by the cursor. The DEPT_NAME column value is displayed after each record is fetched from the cursor. When the %NOTFOUND attribute evaluates to TRUE, the loop is exited.
3. Run the CHP11_9.SQL in SQL*Plus, as shown in Figure 11.9. The PL/SQL block contained in the file uses a FOR loop to fetch the result set of a cursor with parameters.
The cursor defined in lines 2 through 7 contains a parameter used in the WHERE clause in line 5. When the cursor is opened, the parameter must be supplied or an error will occur. Lines 9 through 13 contain the executable section of the block. In line 11, the cursor opened by the FOR loop passes a character parameter to the cursor, which is used in the WHERE clause when the query is executed.
When using a FOR loop to process records returned by the cursor, the cursor is opened and closed implicitly by the FOR loop. This is why the cursor does not have to be opened or closed explicitly using the OPEN and CLOSE calls, as with other loop structures. Also, note that each record returned by the cursor into the VNAME record variable is not declared explicitly in the declarative section. The cursor FOR loop contained in lines 10 through 12 loops through each record in the cursor and displays the VIEW_NAME column using the DBMS_OUTPUT.PUT_LINE procedure.
The parameter passed to the opened query causes only the records beginning with DBA_ to be returned by the query and displayed in SQL*Plus.
How It Works
A cursor is defined in the declarative section along with the parameters and their datatypes, followed by the query to be executed. Within the executable section, the parameters are passed when the cursor is opened—in the OPEN statement or in a FOR loop. Step 1 creates the DEPT11 table used for this How-To. Step 2 demonstrates the process of passing parameters to a cursor in the OPEN statement while opening the cursor. The process of opening the cursor with parameters can also be consolidated by using a cursor FOR loop to process a cursor. Step 3 demonstrates the operation of a cursor FOR loop. Note that the OPEN, FETCH, and CLOSE calls are eliminated while using cursor FOR loops. Cursors enable parameters to be specified, providing greater flexibility and promoting their reuse in similar queries.
Comments
Parameters can be passed to a query by specifying them with their datatypes with the cursor name. If the cursor is going to be used in several places in a PL/SQL block with different values in the WHERE clause for each invocation, that cursor is a good candidate to use parameters with. Using parameters makes the cursor more reusable because the result set is not tied to any variables used in the PL/SQL block. Coding for a cursor can be greatly simplified by using a FOR loop to process a cursor. Whenever possible, use cursors containing parameters so you can reuse cursors in your code.