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:
Define a cursor
Open the cursor
Fetch the records into variables for processing
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.
Name of the cursor.
Optional return clause for the cursor.
A valid SQL SELECT statement.
Name of the cursor.
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:
Parameters make a cursor more reusable.
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.