Processing Queries by Using Explicit Cursors 24Ć5
Overview
The Oracle7 Server uses work areas called “private SQL areas” to execute SQL
statements and store processing information. PL/SQL cursors let you name a private
SQL area and access its stored information. The cursor directs all phases of
processing.
Cursor Type
Description
Implicit Declared by PL/SQL implicitly for all DML and PL/SQL
SELECT statements.
Explicit Declared and named by the programmer and manipulated
through specific statements within the block’s executable
actions.
Recall that the SELECT statement in PL/SQL must only return a single row. PL/SQL
actually attempts to fetch two rows from an implicit cursor: one to satisfy the query,
and a second to see if further rows were returned. One method to eliminate this extra
fetch is to use an explicit cursor.
Explicit Cursor Functions
D Can process beyond the first row returned by the query, row by row.
D Keep track of which row is currently being processed.
D Allow the programmer to manually control them in the PL/SQL block.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder24Ć6
Processing Queries by Using Explicit Cursors 24Ć7
Controlling Explicit Cursors
Now that you have a conceptual understanding of cursors, review the steps to use
them. The syntax for each step follows on the next pages.
Controlling Explicit Cursors Using Four Commands
1. Declare the cursor.
Declare the cursor by naming it and defining the structure of the query to be
performed within it.
2. Open the cursor.
The OPEN statement executes the query and binds any variables that are
referenced. Rows identified by the query are called the active set and are now
available for fetching.
3. Fetch data from the cursor.
The FETCH statement loads the current row from the cursor into variables. Each
fetch causes the cursor to move its pointer to the next row in the active set.
Therefore, each fetch will access a different row returned by the query.
In the flow diagram on the left page, each fetch tests the cursor for any existing
rows. If rows are found, it loads the current row into variables, else it closes the
cursor.
4. Close the cursor.
The CLOSE statement releases the active set of rows. It is now possible to reopen
the cursor to establish a fresh active set.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder24Ć8
Processing Queries by Using Explicit Cursors 24Ć9
Controlling Explicit Cursors continued
Declaring the Cursor
Use the CURSOR statement to declare an explicit cursor. You can define parameters
to allow substitution of values into the query when the cursor is opened. You can also
reference variables within the query, but you must declare them before the cursor
statement.
Syntax
DECLARE
CURSOR cursor_name IS
select_statement;
where: cursor_name is a PL/SQL identifier.
select_statement is a SELECT statement without an INTO
clause.
Note: Do not include the INTO clause within the cursor declaration because it
appears later within the FETCH statement.
1
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder24Ć10
Processing Queries by Using Explicit Cursors 24Ć11
Controlling Explicit Cursors continued
Opening the Cursor
Open the cursor to execute the query and identify the active set after specifying
values for all input variables. The cursor will now point to the first row in the active
set.
Syntax
OPEN cursor_name;
where: cursor_name is the name of the previously-declared cursor.
Note: If the query returns no rows when the cursor is opened, PL/SQL does not raise
an exception. However, you can test the cursor’s status after a fetch.
2
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder24Ć12
Processing Queries by Using Explicit Cursors 24Ć13
Controlling Explicit Cursors continued
Fetching Data from the Cursor
Use the FETCH statement to retrieve the current row values into output variables.
After the fetch, you can manipulate the variables by further statements.
Syntax
FETCH cursor_name INTO variable1, variable2, . . .;
where: cursor_name is the name of the previously declared cursor.
variable is an output variable to store the results.
Guidelines
D Include the same number of variables within the INTO clause of the FETCH
statement as output columns in the SELECT statement, and be sure that the
datatypes are compatible.
D Match each variable to correspond to the columns positionally.
D Alternatively, define a record for the cursor and reference the record in the
FETCH INTO clause.
D Test to see if the cursor contains rows. If a fetch acquires no values, that is, there
are now rows left to process in the active set and no error is recorded.
3
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder24Ć14
Không có nhận xét nào:
Đăng nhận xét