General Rules
No more than 15 cursors can be open at any one time in a stored procedure.
- uppercase letters
- lowercase letters
- $
- @
- #
- digits
- underscores
- POSITION
- REWIND
- SQL transaction statements
DECLARE CURSOR Statement and FOR Statement Cursors
- You can declare cursors in a DECLARE CURSOR statement or FOR loop control statement.
- The SELECT statement you specify in the FOR statement or DECLARE CURSOR (stored procedures form) statement is called the cursor specification.
- You can use both positioned and non-positioned cursors in a stored procedure.
- Cursors declared in a FOR statement and in a DECLARE CURSOR statement differ in the following ways.
FOR Loop Cursor | DECLARE CURSOR Cursor |
---|---|
The scope of the cursor is confined to the FOR statement in which it is defined. The scope of column name or its correlation name in a FOR loop cursor is restricted to the body of the FOR statement. In the case of nested FOR statements, you can reference the cursor name you specify in an outer FOR statement in statements inside the inner FOR statement(s). |
The scope of the cursor is the BEGIN … END compound statement in which it is declared. The scope of a cursor is the compound statement and its nested compound statements, if any. In nested compound statements, the scope of a cursor you specify in an outer compound statement includes all the inner compound statements. |
A positioned DELETE or UPDATE statement referencing the cursor makes it updatable. | The FOR UPDATE option makes the cursor updatable. |
OPEN, FETCH and CLOSE take place implicitly as part of the FOR loop execution. Each iteration of the FOR statement fetches the next row, if it exists, for an open cursor. |
You must explicitly specify OPEN, FETCH or CLOSE. If you specify CLOSE for a result set cursor, the result set will not be returned. |
You can label FOR statements in which cursors are declared. | You cannot label DECLARE CURSOR statements. |
The FOR cursor_name statement implicitly opens a cursor for the SELECT statement you specify as the cursor specification. | The OPEN cursor_name statement opens a cursor for the SELECT statement you specify as the cursor specification. |
Cursor Support
Support is somewhat different depending on whether a cursor is opened by a FOR loop statement or by a cursor declared by a DECLARE CURSOR statement.
FOR Loop Cursor Support
For a FOR loop statement, the following dummy iteration statement opens a cursor for the specified cursor.
FOR for_loop_variable AS [cursor_name CURSOR FOR] cursor_specification DO statement END FOR;
where cursor_specification is a single SELECT statement and statement can be one or more SQL control or DML statements.
- Fetches one row of data from the result set into the for_loop_variable on each iteration.
- Increments the cursor on each iteration, fetching the next row of data, if it exists.The WHERE CURRENT OF forms of DELETE and UPDATE perform as follows:
- DELETE … WHERE CURRENT OF cursor_name deletes the currently fetched row from its base table.
- UPDATE … WHERE CURRENT OF cursor_name updates the currently fetched row in its base table.
DECLARE CURSOR Cursor Support
For cursors defined by a DECLARE CURSOR statement, you must submit explicit OPEN cursor_name and FETCH cursor_name statements.
Note that for a result set cursor, if you specify CLOSE, the result set will not be returned.
Related Topics
For more details on the use of cursors in stored procedures, see DECLARE CURSOR (Stored Procedures Form), Cursors and FOR.