Passing Parameters to PL/SQL Cursors

A cursor is passed a parameter in very much the same way a procedure is passed a parameter except that the parameter can only be IN mode. Like the procedure, the cursor definition will declare an unconstrained datatype for the passed variable.

cursor c7 (v_key varchar2) is
select initcap(book_title) bk_title,
sum(quantity) sales, author_key
from
book join sales using (book_key)
join book_author using (book_key)
where author_key = v_key
group by initcap(book_title), author_key;

The cursor above is defined as accepting a parameter with a datatype of varchar2. In this example, the variable is used in the WHERE clause to filter the results before grouping. By substituting cursor c7 for c6 in the above example, the PL/SQL block becomes much more efficient because the inner cursorreturns only the row belonging to the specified author.

SQL> declare
2 cursor c5 is
3 select initcap(author_last_name) a_name,
4 author_key
5 from author;
6 cursor c7 (v_key varchar2) is
7 select initcap(book_title) bk_title,
8 sum(quantity) sales, author_key
9 from
10 book join sales using (book_key)
11 join book_author using (book_key)
12 where author_key = v_key
13 group by initcap(book_title), author_key;
14 r_c5 c5%rowtype;
15 r_c7 c7%rowtype;
16 begin
17 open c5;
18 loop
19 fetch c5 into r_c5;
20 exit when c5%notfound;
21 dbms_output.put_line(chr(10)||
r_c5.a_name);
22 begin
23 open c7(r_c5.author_key);
24 loop
25 fetch c7 into r_c7;
26 exit when c7%notfound;
27 dbms_output.put_line (r_c7.bk_title||
28 ', '||r_c7.sales);
29 end loop;
30 close c7;
31 end;
32 end loop;
33 close c5;
34 end;
35 /

The results returned in this example are the same as the previous example and to save space were omitted. This example using c7 is much more efficient than using c6 because it eliminated returning unneeded data in cursor c7 and also allows the removal of the IF/THEN clause with its condition test. Notice line 23 opened the cursor and passed the current author_key from the outer cursor. If line 23 did not pass in the parameter when the cursor was opened, PL/SQL would throw an exception.

open c7;
*
ERROR at line 23:
ORA-06550: line 23, column 7:
PLS-00306: wrong number or types of arguments in
call to 'C7'
ORA-06550: line 23, column 7:
PL/SQL: SQL Statement ignored

Let's close with one final note about cursors. This example was used to demonstrate nested cursors and passing parameters to cursors. Even though the final version was far more efficient that the first version, it still violates an important rule of PL/SQL programming, do not execute SQL inside a loop unless there is no other way to get the data.

The example could have been written to execute one cursor that returned all the needed data: author_name, book_title, and sales. And then it could have use a loop to process the results. This method would be even more efficient as it would execute only one query against the database. So far we have manually controlled the cursor and there is many times when you want that level of control. However, Oracle has provided a streamlined method of using cursors with a FOR loop.