 |
|
PL/SQL Cursors
Oracle Tips by
Burleson
|
A cursor is a reference to a private SQL area inside Oracle.
Figure 2.7 illustrates the functionality of a cursor.
There are two types of cursors: implicit and explicit. PL/SQL also
provides a CURSOR FOR loop that makes the process of looping
through the cursor data simpler.
Explicit Cursors
An explicit cursor is defined in the DECLARE section
of a PL/SQL block, as shown in Listing 2.12.
Listing 2.12 The declaration of an explicit cursor.
DECLARE
CURSOR Students_cur
IS
SELECT last_name, first_name, middle_name, gpa
FROM STUDENTS
WHERE gpa > 3.0;
The use of explicit cursors allows PL/SQL programs to handle sets
of rows, rather than being forced to query a single row for each
operation. Each row that meets the criteria defined by the cursor’s
WHERE clause can be processed individually.
Several PL/SQL statements are used to deal with explicit cursors:
-
OPEN—Parses a cursor and prepares the
result set for retrieval. If the cursor is already open, an error
will occur.
-
CLOSE—Disables the cursor. If the
specified cursor hasn’t been opened, an error will result.
-
FETCH—Retrieves the next row of the
result set into variables for processing inside the PL/SQL block. If
the specified cursor hasn’t been opened, an error will occur.
A cursor is not an array; once a row has been bypassed using the
FETCH statement, it is impossible to go backwards inside the
cursor. The only way to get back to a row that has been bypassed is to
close and reopen the cursor.
The CURSOR FOR Loop
The CURSOR FOR loop allows you to handle cursors without
using the OPEN, FETCH, and CLOSE statements.
Listing 2.13 shows a CURSOR FOR loop.
Listing 2.13 A CURSOR FOR loop.
DECLARE
CURSOR Students_cur
IS
SELECT last_name, first_name, middle_name, gpa
FROM STUDENTS
WHERE gpa > 3.0;
BEGIN
FOR Students_rec IN Students_cur LOOP
<statements>
END LOOP;
END;
This type of loop automatically opens the cursor, loops through
each row in the result set, and closes the cursor when the last row
has been processed. In this example, the loop index variable
Students_rec is implicitly declared by the cursor for loop as a
%ROWTYPE variable based on the structure of the cursor (%ROWTYPE
will be discussed later in this chapter).
Cursors can be used with any type of loop available in PL/SQL; the
use of CURSOR FOR loops is not required.
Implicit Cursors
An implicit cursor is created by Oracle for all SQL
statements that manipulate data (even if the query returns only a
single row). If a query has a chance of returning multiple rows, it’s
a good idea to use an explicit cursor to retrieve the rows instead of
using a single SELECT statement.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
