FETCH Statement

The FETCH statement retrieves rows of data from the result set of a multiple-row query—one at a time, several at a time, or all at once—and stores the data in variables, records, or collections.

Topics:

• Syntax

• Semantics

• Usage

• Examples

• Related Topics

Syntax

fetch_statement ::=

Description of the illustration fetch_statement.gif

See:

• into_clause ::=

• bulk_collect_into_clause ::=

• numeric_expression ::=

Semantics

cursor_name

The name of an open explicit cursor. To open an explicit cursor, use the "OPEN Statement".

cursor_variable_name

The name of an open cursor variable. To open a cursor variable, use the "OPEN FOR Statement". The cursor variable can be a formal subprogram parameter (see "Cursor Variables as Subprogram Parameters").

host_cursor_variable_name

The name of a cursor variable declared in a PL/SQL host environment, passed to PL/SQL as a bind argument, and then opened. To open a cursor variable, use the "OPEN FOR Statement". Do not put space between the colon (:) and host_cursor_variable_name.

into_clause

To have the FETCH statement retrieve one row at a time, use this clause to specify the variables or record in which to store the column values of a row that the cursor returns. For more information about this clause, see "RETURNING INTO Clause".

The data type of a host cursor variable is compatible with the return type of any PL/SQL cursor variable.

bulk_collect_into_clause [ LIMIT numeric_expression ]

To have the FETCH statement retrieve all rows at once, omit LIMIT numeric_expression.

To limit the number of rows that the FETCH statement retrieves at once, specify LIMIT numeric_expression.

Use bulk_collect_into_clause to specify one or more collections in which to store the rows that the FETCH statement returns. For more information about bulk_collect_into_clause, see "RETURNING INTO Clause".

Restrictions on bulk_collect_into_clause 

• You cannot use bulk_collect_into_clause in client-side programs.

• When the FETCH statement requires implicit data type conversions, bulk_collect_into_clause can have only one collection_name or host_array_name.

Usage

A sequence of FETCH statements always runs out of data to retrieve, and no exception is raised when a FETCH statement returns no data. To detect this condition, use the cursor attribute %FOUND or %NOTFOUND, described in "Named Cursor Attribute".

Examples

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

• Example 6-9, "Fetching the Same Cursor Into Different Variables"

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

• Example 6-33, "Fetching from Cursor Variable into Record"

• Example 6-34, "Fetching from Cursor Variable into Collections"

• Example 6-46, "Trying to Fetch with FOR UPDATE Cursor After COMMIT Statement"

• Example 7-4, "Native Dynamic SQL with OPEN FOR, FETCH, and CLOSE Statements"

• Example 12-16, "Bulk-Fetching from a Cursor Into One or More Collections"

• Example 12-17, "Bulk-Fetching from a Cursor Into a Collection of Records"

Related Topics

In this chapter:

• "Assignment Statement"

• "CLOSE Statement"

• "Cursor Variable"

• "Explicit Cursor"

• "OPEN Statement"

• "OPEN FOR Statement"

• "RETURNING INTO Clause"

• "SELECT INTO Statement"

In other chapters:

• "Fetching Data with Explicit Cursors"

• "Fetching Bulk Data with Explicit Cursors"

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

• "Fetching from Cursor Variables"

• "OPEN FOR, FETCH, and CLOSE Statements"

• "Retrieving Query Results into Collections"