 |
|
Oracle Tips by Burleson |
Using PL/SQL Cursors
A cursor requires a set of steps in order to be
used. These steps are as follows:
1.
Define the cursor
2. Open the cursor
3.
Process all the rows in a loop
-
Fetch
a row
-
Check that a new row was returned
-
Process the row, return to set 4
-
End the loop
4.
Close the cursor
The cursor is defined in the declaration section
of the PL/SQL block. We will see later that some cursors can also be
defined in the procedural code. The cursor is then opened, which
causes the database to establish a consistent view. Normally in a
loop, the rows are then fetched into a variable or record. In PL/SQL,
you normally will test to ensure that new rows were returned by the
fetch before processing the row. If a new row was not returned, the
loop is terminated. Once all the rows have been processed, the cursor
is closed to release all the cursor’s database resources. The next
example demonstrates each step in using a cursor.
SQL> create or
replace function max_store
2 return varchar2
3 as
4 cursor c1 is
5 select
6 store_name, sum(quantity) qty
7 from store join sales using (store_key)
8 group by store_name;
9
10 r_c1 c1%rowtype;
11 largest number := 0;
12 lg_name store.store_name%type;
13 begin
14 open c1;
15 loop
16 fetch c1 into r_c1;
17 exit when c1%notfound;
18 if r_c1.qty > largest then
19 largest := r_c1.qty;
20 lg_name := r_c1.store_name;
21 end if;
22 end loop;
23 close c1;
24
25 return ('Highest Sales is: '||
26 lg_name||' '||largest);
27 end;
28 /
Function
created.
SQL> select
max_store from dual;
MAX_STORE
-------------------------------------------------
Highest Sales is: hot wet and sexy books 24700
The cursor c1 is defined on lines 4 through
8. Line 10 defines a record to hold a row of fetched data. To ensure
robustness in the function, the record is defined as a c1%rowtype.
As with table %rowtype, the record will
contain each of the columns in the cursor definition, and they will be
the same datatype as the underlying columns in the database. The
cursor is opened on line 14.
Once line 14 is executed, the database will
maintain a consistent view for the entire life of the cursor. Lines
15-22 define the loop that is used to process the rows. Line 16
fetches a row of data and places it into the record variable r_c1.
Line 17 checks the %notfound attribute to ensure that a new row
was fetched.
If the fetch did not return a new row, then %notfound
will be true and the loop will exit. Lines 18-20 process the data in
the record. Once the loop exits (the fetch does not return a new
row), line 23 closes the cursor, freeing all the database resources.
The programmer can also use the while loop to
process a cursor, but must insure that one fetch takes place before
the condition test at the start of the loop.
SQL> declare
2 cursor c2 is
3 select initcap(author_last_name)
from author;
4 l_name author.author_last_name%type;
5 begin
6 open c2;
7 fetch c2 into l_name;
8 while c2%found loop
9 dbms_output.put_line(l_name);
10 fetch c2 into l_name;
11 end loop;
12 close c2;
13 end;
14 /
Jones
Hester
…
Smith
PL/SQL
procedure successfully completed.
In the above example, the cursor returns only one
column and it is fetched into a variable rather than a record. The
variable is defined on line 4. Line 7 executes the initial fetch.
This must happen before starting the while loop to initialize the
c2%found attribute. The WHILE loop will
fetch a new row of data at the bottom of the loop on line 10 allowing
the condition test to occur before processing the new row.
A cursor can be used to determine the average
quantity of all the orders in the sales table. Of course to
find the average you can use the avg function but this example
is demonstrating using the cursor %rowcount attribute.
SQL> declare
2 cursor c3 is
3 select quantity from sales;
4 n_c3 number; -- holds cursor value
5 average number := 0;
6 counter number := 0;
7 begin
8 open c3;
9 loop
10 fetch c3 into n_c3;
11 exit when c3%notfound;
12 average := average + n_c3;
13 end loop;
14 counter := c3%rowcount;
15 close c3;
16 dbms_output.put_line('Average Quantity = '||
17 average/counter);
18 end;
19 /
Average
Quantity = 1105.5
The average sales value is the sum of all the
quantities in the sales table, divided by the number of orders
(or rows). The variable average was used to sum all the
quantities. Line 14 assigned the value of the cursor %rowcount
attribute to the variable counter. Notice that the assignment
was executed before closing the cursor. Once the cursor is closed,
c3%rowcount becomes undefined.
The previous examples have fetched into a cursor
%rowtype
and into variables. If the cursor
returns multiple values, it can fetch them into multiple variables in
the same manner as the SELECT INTOquery. In the
example below, the cursor selects three columns into three variables.
The block lists the stores that have above average sales. First it
must find the average sales, then the code list the stores with sales
above average. Notice that the cursor is opened and processed twice.
As stated, to reprocess a cursor result set, it must be closed and
reopened.
SQL> declare
2 cursor c4 is
3 select store_name, store_key,
sum(quantity) as qty
4 from store join sales using (store_key)
5 group by store_name, store_key;
6
7 v_name store.store_name%type;
8 v_key store.store_key%type;
9 n_qty number := 0;
10 sumer number := 0;
11 average number := 0;
12 begin
13 open c4;
14 loop
15 fetch c4 into v_name, v_key, n_qty;
16 exit when c4%notfound;
17 sumer := sumer + n_qty;
18 end loop;
19 average := sumer/c4%rowcount;
20 close c4;
21
22 dbms_output.put_line(
'Average Store Sales: '||average);
23 open c4;
24 loop
25 fetch c4 into v_name, v_key, n_qty;
26 exit when c4%notfound;
27 if average < n_qty then
28 dbms_output.put_line(initcap(v_name)||',
29 '||v_key||', '||n_qty);
30 end if;
31 end loop;
32 close c4;
33 end; /
Average Store
Sales: 11055
Hot Wet And Sexy Books, S107, 24700
Eaton Books, S109, 12120
Wee Bee Books, S104, 13700
Borders, S102, 21860
Books For Dummies, S105, 13000
The cursor fetch placed the returned column values
in the variables v_name, v_key, and n_qty. The
example opened, processed, and closed the cursor twice. The first
time, the only value used was qty and as such, the example
cursor was poorly implemented. A real cursor should never bring back
unused data. The code should have defined a second cursor that only
returned the qty column.
Cursors can also be nested. The example below
uses two cursors to list authors and the books they have sold.
SQL> declare
2 cursor c5 is
3 select initcap(author_last_name) a_name,
4 author_key
5 from author;
6 cursor c6 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 group by initcap(book_title), author_key;
13 r_c5 c5%rowtype;
14 r_c6 c6%rowtype;
15 begin
16 open c5;
17 loop
18 fetch c5 into r_c5;
19 exit when c5%notfound;
20 dbms_output.put_line(chr(10)||
r_c5.a_name);
21 begin
22 open c6;
23 loop
24 fetch c6 into r_c6;
25 exit when c6%notfound;
26 if r_c5.author_key = r_c6.author_key then
27 dbms_output.put_line (r_c6.bk_title||
28 ', '||r_c6.sales);
29 end if;
30 end loop;
31 close c6;
32 end;
33 end loop;
34 close c5;
35 end;
36 /
Jones
Windows Success, 18200
Hester
Windows Success, 18200
Pay No Taxes And Go To Jail, 16400
Oracle9i Sql Tuning, 1100
Weaton
Unix For Experts, 1400
Piano Greats, 16290
Reduce Spending The Republican Way, 2750
… Results
truncated for brevity
The outer cursor
(c5) is
efficient, returning only those columns needed. The inner cursor(c6)
however brings back all the book information for all authors each time
it is opened. If the database contained thousands of books, this
block would be very inefficient. What is needed is a way to make the
inner cursor (c6) return only those rows belonging to the
specific author. This can be achieved by passing the cursor a
variable or parameter.
 |
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. |
|
