Using Explicit Cursors  «Prev  Next»

Lesson 5Structure of a Cursor Block
ObjectiveDefine the basic Structure of a Cursor Block

Structure of a PL/SQL Cursor Block

So far, you have learned about the different types of cursors, their advantages, and their disadvantages.
An explicit cursor block has four basic PL/SQL sections that are necessary for processing data selected within the cursor:
  1. Define a cursor
  2. Open the cursor
  3. Fetch the records into variables for processing
  4. Close the cursor

Define a cursor


The following MouseOver explains the syntax used to define a cursor and variables in the syntax. It also shows an example of using the syntax to define a cursor block. This definition does not include return specification.
PL/SQL cursor components
  1. Name of the cursor.
  2. Optional return clause for the cursor.
  3. A valid SQL SELECT statement.
  4. Name of the cursor.
  5. A valid SQL SELECT statement.
CURSOR <cursor_name> [([parameter_1 [,parameter_2])]
 [RETURN return_specification] is
  SELECT <select_clause>;
Example: 
CURSOR pets is 
  SELECT PRODUCT_NAME, CURRENT_INVENTORY_COUNT
    FROM PRODUCT WHERE PRODUCT_NAME LIST 'Fish%';

Define Cursor
You can pass parameters using the optional parameter list.
When a cursor is defined, Oracle allocates memory for the cursor. The name of an explicit cursor is not a PL/SQL variable; instead, it is an undeclared identifier used to point or refer to a query. You cannot assign values to a cursor name, nor can you use a cursor name within an expression.

Passing parameters

The rationale behind PL/SQL allowing you to pass parameters into a cursor is:
  1. Parameters make a cursor more reusable.
  2. Parameters avoid scoping problems.

The scope of a cursor parameter is confined to that cursor. You cannot refer to the cursor parameter outside the SELECT statement associated with that cursor.
In the next lesson, we will look at the rest of the structure of a cursor block.