Lesson 4 | Explicit Cursors |
Objective | Describe Explicit Cursors |
Explicit Cursors in PL/SQL
When you need to process multiple records one at a time through a SELECT
statement, you can use an explicit cursor.
A set of records returned by a query associated with an explicit cursor is called an active or result set. By default, the first record within the result set is the current record of the cursor.
When you execute the FETCH
statement, data from the current record is copied into variables, and the next record becomes the current record.
You will learn more about the basic structure of a cursor block, including the FETCH
statement, in the next lesson.
The following Slide Show further explains explicit cursors.
- An explicit cursor is a SELECT statement that is defined within the declaration section of the PL/SQL code
- You can get more information about the cursor through the following attributes.
- Example of an explicit cursor
Explicit Cursor Guidelines
In this example, the cursor is a
virtual table[1], because the data selected has the same structure of a table, that is,
records and columns, but it exists for the duration of execution of the SQL statement.
When to use Explicit Cursors
An explicit cursor can be used when:
- Multiple records must be processed within a
SELECT
statement
- You wish to have better control in cursor execution
- You wish to handle exceptions within a PL/SQL block
In the next lesson, we will define the basic structure of a cursor block.
Explicit Cursors
An explicit cursor is a session cursor that you construct and manage. You must declare and define an explicit cursor, giving it a name and associating it with a query (typically, the query returns multiple rows). Then you can process the query result set in either of these ways:
- Open the explicit cursor (with the OPEN statement), fetch rows from the result set (with the FETCH statement), and close the explicit cursor (with the CLOSE statement).
- Use the explicit cursor in a cursor FOR LOOP statement
You cannot
- assign a value to an explicit cursor,
- use it in an expression, or
- use it as a formal subprogram parameter or host variable.
You can do those things with a
cursor variable.
Unlike an implicit cursor, you can reference an
explicit cursor or cursor variable by its name. Therefore, an explicit cursor or cursor variable is called a named cursor.
[1]virtual table: A table stored in the computer’s memory. Virtual tables themselves are not stored in the database; rather, the definition of the view is stored and given a name. Users call up that name, and the view is created (from base tables) on the fly. When a user closes the view, it "disappears" from memory, only to be recreated the next time its name is invoked.