Cursor FOR LOOP Statement

The cursor FOR LOOP statement implicitly declares its index as a record variable of the row type that a specified cursor returns and opens a cursor. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or if an exception is raised.

Topics:

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

cursor_for_loop_statement ::=

Description of the illustration cursor_for_loop_statement.gif

See statement ::=.

Semantics

record_name

An identifier for the index. The cursor FOR LOOP statement implicitly declares record_name as a record variable whose field names match the column names of the rows that cursor_name or select_statement returns.

The variable record_name is local to the cursor FOR LOOP statement. Statements inside the loop can reference record_name and its fields. Any calculated column must have an alias if you want to reference it inside the loop—for details, see "When Explicit Cursors Need Column Aliases". Statements outside the loop cannot reference record_name. After the cursor FOR LOOP statement runs, record_name is undefined.

cursor_name

The name of an explicit cursor that is not already open when the cursor FOR LOOP is entered.

actual_cursor_parameter

An actual parameter that corresponds to a formal parameter of the explicit cursor cursor_name. For more information, see "Explicit Cursors that Accept Parameters".

select_statement

A SQL SELECT statement (not a PL/SQL SELECT INTO statement). For this SELECT statement, PL/SQL declares, opens, fetches from, and closes an implicit cursor. However, because select_statement is not an independent statement, the implicit cursor is internal—you cannot reference it with the name SQL.

label

A label that identifies cursor_for_loop_statement (see the syntax diagram statement ::= and the description of label). CONTINUE, EXIT, and GOTO statements can reference this label.

Labels improve readability, especially when LOOP statements are nested, but only if you ensure that the label at the end of the LOOP statement matches one of the labels at the beginning of the same statement (the compiler does not check).

Examples

• Example 6-7, "Fetching with a Cursor"

• Example 6-10, "Fetching Bulk Data with a Cursor"

Related Topics

In this chapter:

• "Basic LOOP Statement"

• "CONTINUE Statement"

• "EXIT Statement"

• "Explicit Cursor"

• "FETCH Statement"

• "FOR LOOP Statement"

• "FORALL Statement"

• "OPEN Statement"

• "WHILE LOOP Statement"

In other chapters:

• "Looping Through Multiple Rows (Cursor FOR LOOP Statement)"