 |
|
Oracle Tips by Burleson |
Passing Parameter 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.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
