In the previous section we learned that a sequence
of steps is followed to define and use a cursor.
1.Open the cursor.
2.Start a loop.
3.Fetch a row of data.
4.Check for a new row, exit if not.
5.Process the data.
6.End the loop.
7.Close the cursor.
Since these steps are almost always followed,
Oracle provides a way to let PL/SQL perform most of the steps. This
is called the CURSOR FOR
loop. As in the name, it uses a FOR loop to process the cursor.
SQL> declare
2 cursor c8 is
3 select
4 initcap(author_last_name) l_name,
5 initcap(author_first_name) f_name
6 from author;
7 begin
8 for r_c8 in c8 loop
9 dbms_output.put_line(r_c8.f_name||' '||
10 r_c8.l_name);
11 end loop; 12 end; /
Mark Jones
Alvis Hester
Erin Weaton Pierre Jeckle
Lester Withers
Juan Petty
Louis Clark
Minnie Mee
Dirk Shagger
Diego Smith
The CURSOR FORis a very
efficient cursor. Lines 2 through 6 define the cursor. Line 8
handles opening the cursor, fetching the rows, testing for new rows,
and exiting when a new row is not returned. Notice that the record
r_c8 is not defined in the declaration section. The CURSOR FOR
automatically defines r_c8 as a c8%rowtype. The scope
for r_c8 is only inside the CURSOR FOR loop. Likewise, the
cursor attributes for cursor c8 are defined only inside the
CURSOR FOR loop.
If you define the record r_c8 in the
declare section of the PL/SQL block, the variable you defined will go
out of scope when the CURSOR FORdefines the
record r_c8 and will return in scope when the loop ends. If
there is a requirement to access the cursor attributes from outside
the loop, a variable must be defined and the cursor attribute copied
into it before the loop ends. The CURSOR FOR closes the cursor at the
END LOOP clause. Below is an example that calculates the average of
total store sales by store, using the cursor %rowcount
attribute.
SQL> declare
2 cursor c9 is
3 select sum(quantity) qty
4 from sales group by store_key;
5 sumer number := 0;
6 counter number := 0;
7 begin
8 for r_c9 in c9 loop
9 sumer := sumer + r_c9.qty;
10 counter := c9%rowcount;
11 end loop;
12 dbms_output.put_line('Average is '||
13 sumer/counter);
14 exception
15 when others then
16 if c9%isopen then close c9;
17 end if;
18 end; /
Average is 11055
In order to count the number of items summed, the
attribute c9%rowcount must be assigned inside the loop at line
10. Also note that an exception happening inside the loop will jump
to the exceptions handler which must ensure that the cursor is
properly closed.
The CURSOR FORcan also be
passed parameters. It follows the same rules as the manual cursor in
that if the cursor is defined with a parameter, it must be opened with
a parameter, or the cursor will raise an exception.
SQL> declare
2 cursor c10(v_avg in number) is
3 select store_name, sum(quantity) qty
4 from store join sales using (store_key)
5 group by store_name
6 having sum(quantity) > v_avg;
7 begin
8 for r_c10 in c10 (11055) loop
9 dbms_output.put_line(initcap(
10 r_c10.store_name)||
' '||r_c10.qty);
11 end loop;
12 end; /
Borders 21860
Books For Dummies 13000
Hot Wet And Sexy Books 24700
Wee Bee Books 13700
Eaton Books 12120
The variable v_avg is defined as an IN
parameter. This designation is optional as a variable can only be
passed as mode IN, and the passed parameter can also be assigned a
default value. If a default value is assigned, then the cursor can be
opened without passing a parameter and it will use the defined default
value.
Notice in the example below that v_avg is
assigned a default value in line 2, and the cursor is opened without
passing a parameter in line 8.
SQL> declare
2 cursor c10(v_avg number:= 11055) is
3 select store_name, sum(quantity) qty
4 from store join sales using (store_key)
5 group by store_name
6 having sum(quantity) > v_avg;
7 begin
8 for r_c10 in c10 loop
9 dbms_output.put_line(initcap(
10 r_c10.store_name)||
' '||r_c10.qty);
11 end loop;
12 end;
13 /
Borders 21860
Books For Dummies 13000
Hot Wet And Sexy Books 24700
Wee Bee Books 13700
Eaton Books 12120
Another nice feature of the CURSOR FORis the
ability to define the cursor inline. Below is a cursor that is not
declared in the declaration section of the executing block. The
cursor definition is included in the CURSOR FOR.
SQL> begin
2 for r_c11 in
(select author_last_name l_name,
3 author_first_name f_name
4 from author) loop
5 dbms_output.put_line(initcap(
6 r_c11.l_name||
', '||r_c11.f_name));
7 end loop;
8 end;
9 /
Jones, Mark
Hester, Alvis
Weaton, Erin
Jeckle, Pierre
Withers, Lester
Petty, Juan
Clark, Louis
Mee, Minnie
Shagger, Dirk
Smith, Diego
Defining a cursor inline has the advantage of self
documenting the cursor in the code. Instead of going back to the
declare section to determine the cursor definition, the programmer
maintaining the code can see the definition right there in the code
that calls it.
However, if the cursor is used multiple times, it
will be in multiple locations in the code, reducing maintainability.
Another problem with defining the cursor inline is
that the cursor will have no defined name. Therefore you will not be
able to access the cursor attributes. This can be a problem if an
exception is raised inside the cursor that jumps out of the loop.
You can get around this problem by wrapping the
loop inner statements in a separate block with an exception handler.
begin
for r_c11 in (select author_last_name l_name,
author_first_name f_name
from author) loop
begin
dbms_output.put_line(initcap(r_c11.l_name||
', '||r_c11.f_name));
-- lots of other stuff to do.
exception
when others then
-- handle all exceptions
end;
end loop;
end;
The exception handler must deal with all the
exceptions without raising new ones unless it terminates the program.
Any exception raised in the exception handler will jump to the end of
the loop and leave the cursor open.
Another powerful feature of the CURSOR FORis the
update feature. This allows the programmer to efficiently update data
returned by the cursor.
The above book excerpt is from:
Easy Oracle PL/SQL Programming
Get Started
Fast with Working PL/SQL Code Examples
