 |
|
Oracle Tips by Burleson |
PL/SQL
Cursors
A 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 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
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 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 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.
 |
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. |
|
