Using Explicit Cursors
Retrieving Data from a Set Using Cursors and Cursor Variables
Developing and Using Stored Procedures 4-33
Implicit cursors
can be used in PL/SQL without explicit code to process the
cursor itself. A result set that is returned by the cursors can be used
programmatically, but there is no programmatic control over the cursor itself.
Explicit cursors
allow you to programmatically manage the cursor, and give you a
detailed level of control over record access in the result set.
Each user session may have many open cursors, up to the limit set by the initialization
parameter
OPEN_CURSOR
S, which is
50
by default. You should ensure that your
applications close cursors to conserve system memory. If a cursor cannot be opened
because the
OPEN_CURSORS
limit is reached, contact the database administrator to
alter the
OPEN_CURSORS
initialization parameter.
Using Explicit Cursors
The implicit cursor, such as in a
FOR...LOOP
, are generally more efficient than an
explicit cursor. However, explicit cursors may be more appropriate for your program,
and they also allow you to manage specific in-memory areas as a named resource.
An explicit cursor has the attributes described in the following table:
An explicit cursor must be defined as a variable of the same type as the columns it
fetches; the data type of the record is derived from the cursor definition. Explicit
cursors must be opened and may then retrieve rows within a
LOOP...EXIT WHEN
structure and then closed. The general form for using cursors follows:
DECLARE
CURSOR
cursor_name
type
IS
query_definition
;
OPEN
cursor_name
LOOP
FETCH
record
;
EXIT WHEN
cursor_name
%NOTFOUND;
...; -- process fetched row
END LOOP;
CLOSE
cursor_name
;
This is what happens during the life time of a cursor:
The
OPEN
statement parses the query identified by the cursor, binds the inputs,
and ensures that you can successfully fetch records from the result set.
The
FETCH
statement runs the query, and then finds and retrieves the matching
rows. You will need to define and use local variables as buffers for the data
returned by the cursor, and then process the specific record.
See Also:
Oracle Database Concepts for information about cursors
Cursor Attribute
Description
%NOTFOUND
Returns
TRUE
or
FALSE
, based on the results of the last fetch.
%FOUND
Returns
TRUE
or
FALSE
, based on the results of the last fetch; negation
of the
%NOTFOUND
results.
%ROWCOUNT
Returns the number of rows fetched. Can be called at any time after the
first fetch. Also returns the number of rows affected from
UPDATE
and
DELETE
statements.
%ISOPEN
Returns
TRUE
if a cursor is still open.
