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.

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.

An explicit cursor can be used when:

1. Multiple records must be processed within a SELECT statement
2. You wish to have better control in cursor execution
3. You wish to handle exceptions within a PL/SQL block

In the next lesson, we will define the basic structure of a cursor block.

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:

1. 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).
2. Use the explicit cursor in a cursor FOR LOOP statement

You cannot

1. assign a value to an explicit cursor,
2. use it in an expression, or
3. 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.