Question:
I need assistance understanding
how to use a cursor in PL/SQL. Can you show examples of
PL/SQL cursor usage?
Answer:
PL/SQL cursors are a way to do a row-by-row
processing of Oracle tables. There are several types
of PL/SQL cursors:
- Implicit PL/SQL cursors
- Explicit PL/SQL cursors
- PL/SQL ref cursors
A PL/SQL cursor is used to retrieve and process zero or
more rows of data. There are a number of steps to use a cursor and
depending on how you implement the cursor, you can control each step
or have the database perform them. A PL/SQL cursor is based on a SELECT
statement, normally declared in the declaration section of a PL/SQL
block. The statement is not restricted as to the columns retrieved or
number of tables joined.
Any valid SQL statement can be used to implement a
PL/SQL cursor. Cursors are used to retrieve and process data. There is no
cursor for inserts, updates or deletes. In the following set of
examples we will step through the process of defining and using
cursors. In the example function my_func, a cursor named c1
is defined in the function's declaration section.
create or
replace function my_func
return number
as
cursor c1
is
select store_name, sum(quantity) qty
from store join sales using (store_key)
group by store_name;
In this example, the cursor c1 joins the
store and sales tables. Since we are going place the rows
in a record, the column sum(quantity) must be aliased to a
valid column name. If all the columns are going to be returned, you
can use the SELECT * clause like this:
cursor c2 is
select * from author;
Normally a cursor definition determines what data
will be retrieved by the cursor. However, sometime a cursor is
defined as a variable that can return different columns based on how
it is later used. These cursors can include the RETURNING clauseto define the columns that the cursor will return.
cursor c3
returning book%rowtype;
Using cursors with a RETURNING clausewill be discussed further in the discussions of REF CURSORS.
Once declared, the PL/SQL cursor can be used in your
code. To use a cursor you must first open it. When a cursor is
opened, the SQL statement is executed and the table rows involved in
the query is identified by the database. The database's consistent
view mechanism will ensure that all data retrieved by the cursor is
consistent as of the moment the cursor is opened. The database will
maintain this consistent view until the cursor is closed. For this
reason, it is important to close the cursor as soon as processing
permits.
open c1;
A cursor must be closed to release the database
resources it is holding. As long as the cursor is open, the database
must maintain the consistent view.
close c1;
Once the cursor is closed, it again becomes a
definition. One important note is that exceptions thrown while
processing a cursor can jump pass the CLOSE statement.
It is important to ensure that the cursor is closed in the exception
handling routine if exceptions are raised.
If you attempt to open a cursor that is already
open, the cursor will raise an exception. Likewise, if you attempt to
close a cursor that is not open, the cursor will raise an exception.
Since you need to insure that the cursor is closed, use the cursor
attribute %isopen to test the cursor's state before attempting
to close the cursor. Below is a code fragment of an exception handler
that tests a cursor's state and closes the cursor if open. We will
discuss all of the cursor attributes in the next section.
exception
when others then
if c1%isopen then close c1;
end if;
end;
The exception handler tests the cursor's state
before closing it.
When a PL/SQL cursor is opened, the rows of data are not
retrieved by the database. The actual rows must be retrieved, one at
a time using the FETCH clause. Before you can fetch
a row, you must create a variable to hold the returned data.
As with the SELECT INTOquery,
there are two options for defining these variables: separate variables
for each returned column, or use a record based on the cursor row
%type. Using the example cursor c1 defined earlier, the
first example defines separate variables for each column.
cursor c1 is
select
store_name,
sum(quantity) qty
from
store
join
sales using (store_key)
group by
store_name;
st_name
store.store_name%type;
total number;
begin
open c1;
fetch c1 into st_name, total;
The cursor fetch returns two values and they are
placed in the defined variables. As with the SELECT INTOquery, the order of the columns and the list of variables in your
PL/SQL code must match. If the cursor returns a large number of
values or if the number of values may change, it is better to create
the variable as a cursor %rowtype.
cursor c1 is
select
store_name,
sum(quantity) qty
from
store
join
sales using (store_key)
group by
store_name;
r_c1
c1%rowtype;
begin
open c1;
fetch c1 into r_c1;
Each time a fetch is executed the next row of data
is returned. Cursor rows are accessed in the order that they are
returned. If you require data from a row that has already been
processed, you must close the cursor, reopen it and reprocess the data
to the required row.
When the last row is fetched, all subsequent
fetches will return the last row. We need a way to get information
about the cursor state so that we can determine when the last row has
been fetched. Oracle provides cursor attributes to allow you to
determine the cursor's state.
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 INTO query. 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.
=========================================================
This chapter gives us a detailed
explanation of all available cursor types in Oracle with
numerous examples, facts,
and tips through the below topics,
1.
Introduction to the cursor
fundamentals, its execution cycle, and attributes.
2.
Implicit cursors and their
different types.
3.
Explicit cursors, their
different types, and the parameterized cursors.
4.
Reference cursors and their
different types.
5.
Cursor Expressions as nested
cursors and arguments.
6.
TO_REFCURSOR and
TO_CURSOR_NUMBER APIs in the DBMS_SQL package.
7.
Implicit result sets in R12.1
explaining the RETURN_RESULT and GET_NEXT_RESULT APIs in the
DBMS_SQL package.
8.
Restrictions in the reference
cursors.
PL/SQL Cursor Fundamentals
The Oracle server allocates a chunk
of private temporary workspace in the SGA called the context
area for processing every single SQL statement encountered
inside a PL/SQL block. This memory area holds the SQL query,
its parsing information and the result set returned by the
SQL query. The result set can then be processed either row
by row or in bulk. As the context area is managed internally
by the Oracle server, we do not have any control over it.
The cursor can hold n
number of rows, but can process only one row at a time. The
record set held by the cursor is called as the active set.
Note: Cursors do not have a physical
size as they are mere pointers to the memory area where the
query results are stored.
The cursors are classified as Implicit cursors and
Explicit cursors based on how manageable they are by the
programmers.
The implicit cursors are created and
managed by the Oracle server internally without the
programmer intervention, whereas, on the other hand, the
programmers are completely responsible for managing the
complete execution cycle of the explicit cursors.
PL/SQL Cursor Execution Cycle
The cursor execution cycle is common
for both the implicit and the explicit cursors, where it
involves the stages that
describe the processing of an SQL statement that is
associated with the cursor. The execution cycle for the
implicit cursors are taken care by the Oracle server itself,
but in the case of explicit cursors, the programmers are
fully responsible for implementing and controlling its
execution cycle.
The execution cycle is mainly divided
into three different stages and for the proper use of a
cursor, all these steps have to be followed in their
mentioned sequential order.
Related PL/SQL cursor articles:
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
