 | Chapter 6 Database Interaction and Cursors |  |
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?
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.
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.
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;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.
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.
|  | |
| 6.9 Cursor Attributes |  | 6.11 SELECT FOR UPDATE in Cursors |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.