FETCH (Stored Procedures Form) | Teradata Vantage - FETCH (Stored Procedures Form) - Advanced SQL Engine - Teradata Database

Purpose

Positions a cursor to the next row (default) or any specified row of a response set and assigns the values in that row to local variables or parameters.

Invocation

Executable.

Stored procedures only.

Syntax

FETCH [ [ NEXT | FIRST ] FROM ] cursor_name
  INTO { local_variable_name | parameter_reference } [,...] ;

NEXT

Fetches the next row from the response set, if it exists.

NEXT is the default.

FIRST

Fetches the first row from the response set.

cursor_name

The name of an open selection cursor, including a cursor that was allocated, from which a row is to be fetched.

local_variable_name

The name of the local variable into which the fetched row is to be assigned.

Both predefined data types and UDTs (except VARIANT_TYPE UDTs) are supported.

parameter_reference

The name of the INOUT or OUT parameter into which the fetched row is to be assigned.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Authorization

None.

When There Are No Rows in the Response Set

The system handles this runtime warning condition within the procedure. If it is not handled, the procedure continues from the next statement following the failed fetch operation.

Assignment Order for Fetched Rows

The system assigns row values to local variables or output parameters in the order you declared those variables and parameters in the INTO list.

General Rules

The specified cursor must be open when you submit FETCH.

The number of values FETCH returns must match the number of local variables and output parameters you declared in the INTO list.

The data types of the fetched columns must match the data types you specified for the local variables or OUT parameters to which they are assigned.

This is particularly true for UDT types, because the system does not implicitly apply any casts defined for a type.

You cannot indicate a simple target specification that names table columns in the INTO list. If you specify a non-valid INTO list, the system returns error SPL1028 during compilation.

Instead, you must specify output parameters (INOUT and OUT) or local variables.

Rules for FIRST and NEXT

The output parameter or local variable you specified in the INTO list for this value is not changed in this case.

If you specify FIRST, you must specify SCROLL in the declaration for the referenced cursor.

If you do not specify SCROLL, the system returns error SPL1132 at compilation.

The output parameter or local variable you specified in the INTO list for this value is not changed in this case.

Example: Returning Columns Assigned to Local Variables with Matching Data Types

The following example demonstrates that the cursor referenced by the FETCH statement is a valid cursor specification that returns columns correctly assigned to local variables with matching data types.

CREATE PROCEDURE sp1()
BEGIN
  DECLARE var1 INTEGER;
  DECLARE var2 CHARACTER(30)
  DECLARE projcursor CURSOR FOR
    SELECT projid, projectdesc
    FROM project
    ORDER BY projid;
  OPEN projcursor;
   WHILE (SQLCODE=0)
   DO
    FETCH projcursor INTO var1, var2;
   END WHILE;
  CLOSE projcursor;
END;

Example: Using the FETCH Statement and the WHILE Loop

In the following example, the FETCH statement after the WHILE loop raises completion condition SQLCODE 7632 and SQLSTATE '02000' and returns the message no rows found because the cursor is already positioned after the last row in the result set:

CREATE PROCEDURE sp1 (OUT par1 CHARACTER(50))
BEGIN
  DECLARE var1 INTEGER;
  DECLARE projcursor CURSOR FOR
    SELECT projid, projectdesc
    FROM project;
  OPEN projcursor;
   WHILE (SQLCODE = 0)
   DO
    FETCH projcursor INTO var1, par1;
   END WHILE;
  FETCH projcursor INTO var1, par1;
  CLOSE projcursor;
END;

Example: Using Fetch Orientation in the FETCH Statement

The following example illustrates the usage of fetch orientation in the FETCH statement. Assume that the project table contains 10 rows at the time execution of sp1 begins.

The first FETCH statement returns the first row, and the second FETCH returns the second row if no other rows have been fetched since projcursor was opened.

CREATE PROCEDURE sp1 (OUT par1 INTEGER)
BEGIN
  DECLARE var1 CHARACTER(5);
  DECLARE var2 INTEGER;
  DECLARE projcursor SCROLL CURSOR FOR
    SELECT projectstatus
    FROM project;
  OPEN projcursor;
    FETCH FIRST projcursor INTO var1;
    ...
    FETCH NEXT projcursor INTO var1;
    ...
  CLOSE projcursor;
END;

Example: Using FETCH FIRST

The following example illustrates the usage of FETCH FIRST. Assume that the project table is empty at the time execution of sp1 begins.

The FETCH statement raises the completion condition SQLCODE 7632 and SQLSTATE '02000' and returns the message no rows found because the table does not contain any rows.

CREATE PROCEDURE sp1 (OUT par1 INTEGER)
BEGIN
  DECLARE var1 CHARACTER(5);
  DECLARE var2 INTEGER;
  DECLARE projcursor SCROLL CURSOR FOR
    SELECT projectstatus
    FROM project;
  OPEN projcursor;
    FETCH FIRST projcursor INTO var1;
    ...
  CLOSE projcursor;
END;