[Chapter 6] 6.10 Cursor Parameters

6.10 Cursor Parameters

You are probably familiar with parameters for procedures and functions. Parameters provide a way to pass information into and out of a module. Used properly, parameters improve the usefulness and flexibility of modules.

PL/SQL also allows you to pass parameters into cursors. The same rationale for using parameters in modules applies to parameters for cursors:

A parameter makes the cursor more reusable. Instead of hardcoding a value into the WHERE clause of a query to select particular information, you can use a parameter and then pass different values to the WHERE clause each time a cursor is opened.
A parameter avoids scoping problems. When you pass parameters instead of hardcoding values, the result set for that cursor is not tied to a specific variable in a program or block. If your program has nested blocks, you can define the cursor at a higher-level (enclosing) block and use it in any of the subblocks with variables defined in those local blocks.

You can specify as many cursor parameters as you want and need. When you OPEN the parameter, you need to include an argument in the parameter list for each parameter, except for trailing parameters that have default values.

When should you parameterize your cursor? I apply the same rule of thumb to cursors as to modules: if I am going to use the cursor in more than one place, with different values for the same WHERE clause, then I should create a parameter for the cursor.

Let's take a look at the difference between parameterized and unparameterized cursors. First, a cursor without any parameters:

CURSOR joke_cur IS
   SELECT name, category, last_used_date
     FROM joke;

The result set of this cursor is all the rows in the joke table. If I just wanted to retrieve all jokes in the HUSBAND category, I would need to add a WHERE clause as follows:

CURSOR joke_cur IS
   SELECT name, category, last_used_date
     FROM joke
    WHERE category = 'HUSBAND';

I didn't use a cursor parameter to accomplish this task, nor did I need to. The joke_cur cursor now retrieves only those jokes about husbands. That's all well and good, but what if I also would like to see lightbulb jokes and then chicken-and-egg jokes and finally, as my ten-year-old would certainly demand, all my knock-knock jokes?

6.10.1 Generalizing Cursors with Parameters

I really don't want to write a separate cursor for each different category -- that is definitely not a data-driven approach to programming. Instead, I would much rather be able to change the joke cursor so that it can accept different categories and return the appropriate rows. The best (though not the only) way to do this is with a cursor parameter:

DECLARE
   /*
   || Cursor with parameter list consisting of a single
   || string parameter.
   */
   CURSOR joke_cur (category_in VARCHAR2)
   IS
      SELECT name, category, last_used_date
        FROM joke
       WHERE category = UPPER (category_in);

   joke_rec joke_cur%ROWTYPE;

BEGIN
   /* Now when I open the cursor, I also pass the argument */
   OPEN joke_cur (:joke.category);
   FETCH joke_cur INTO joke_rec;

I added a parameter list after the cursor name and before the IS keyword. I took out the hardcoded "HUSBAND" and replaced it with "UPPER (category_in)" so that I could enter "HUSBAND", "husband", or "HuSbAnD" and the cursor would still work. Now when I open the cursor, I specify the value I wish to pass as the category by including that value (which can be a literal, constant, or expression) inside parentheses. At the moment the cursor is opened, the SELECT statement is parsed and bound using the specified value for category_in. The result set is identified and the cursor is readied for fetching.

6.10.2 Opening Cursors with Parameters

I can OPEN that same cursor with any category I like. Now I don't have to write a separate cursor to accommodate this requirement:

OPEN joke_cur (:joke.category);
OPEN joke_cur ('husband');
OPEN joke_cur ('politician');
OPEN joke_cur (:joke.relation || ' IN-LAW');

The most common place to use a parameter in a cursor is in the WHERE clause, but you can make reference to it anywhere in the SELECT statement, as shown here:

DECLARE
   CURSOR joke_cur (category_in VARCHAR2)
   IS
      SELECT name, category_in, last_used_date
        FROM joke
       WHERE category = UPPER (category_in);

Instead of returning the category from the table, I simply pass back the category_in parameter in the select list. The result will be the same either way, because my WHERE clause restricts categories to the parameter value.

6.10.3 Scope of Cursor Parameters

The scope of the cursor parameter is confined to that cursor. You cannot refer to the cursor parameter outside of the SELECT statement associated with the cursor. The following PL/SQL fragment will not compile because the program_name identifier is not a local variable in the block. Instead, it is a formal parameter for the cursor and is defined only inside the cursor:

DECLARE
   CURSOR scariness_cur (program_name VARCHAR2)
   IS
      SELECT SUM (scary_level) total_scary_level
        FROM tales_from_the_crypt
       WHERE prog_name = program_name;
BEGIN
   program_name := 'THE BREATHING MUMMY'; /* Illegal reference */
   OPEN scariness_cur (program_name);
END;

6.10.4 Cursor Parameter Modes

The syntax for cursor parameters is very similar to that of procedures and functions, with the restriction that a cursor parameter can be an IN parameter only. You cannot specify OUT or IN OUT modes for cursor parameters (see Chapter 15, Procedures and Functions , for more information on parameter modes).

The IN and IN OUT modes are used to pass values out of a procedure through that parameter. This doesn't make sense for a cursor. Values cannot be passed back out of a cursor through the parameter list. Information is retrieved from a cursor only by fetching a record and copying values from the column list with an INTO clause.

6.10.5 Default Values for Parameters

Cursor parameters can be assigned default values. Here is an example of a parameterized cursor with a default value:

CURSOR emp_cur (emp_id_in NUMBER := 0)
IS
   SELECT employee_id, emp_name
     FROM employee
    WHERE employee_id = emp_id_in;

So if Joe Smith's employee ID is 1001, the following statements would set my_emp_id to 1001 and my_emp_name to JOE SMITH:

OPEN emp_cur (1001);
FETCH emp_cur INTO my_emp_id, my_emp_name;

Because the emp_id_in parameter has a default value, I can also open and fetch from the cursor without specifying a parameter. If I do not specify a value for the parameter, the cursor uses the default value.