FETCH (Stored Procedures Form) | Teradata Vantage - FETCH (Stored Procedures Form) - Teradata Vantage - Analytics Database

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.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Executable.

Stored procedures only.

Syntax

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

Syntax Elements

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.

Usage Notes

• When There Are No Rows in the Response Set
  If there are no rows in the response set at the time you execute FETCH, the system returns the following runtime exception:

  • SQLCODE is set to 7362
  • SQLSTATE is set to ‘02000’

  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.

  If the cursor is not open, the system returns the following runtime exception:

  • SQLCODE is set to 7631
  • SQLSTATE is set to ‘24501’

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

  IF a mismatch is identified at …	THEN the database returns …
  compilation	compilation error SPL1027.
  runtime	a runtime exception: SQLCODE is set to 7608 SQLSTATE is set to ‘T7608’

  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.

  To work around this restriction, you can do either:

  • Explicitly CAST data types in the cursor select list from a predefined type to a UDT or from a UDT to a predefined type if you have also defined a cast to the target type that specifies the AS ASSIGNMENT option.
  • Call a method that returns the target 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
  If you do not specify NEXT or FIRST, or if you specify NEXT, and the cursor is positioned on or after the last row in the response set, or if there is no data, then the stored procedure positions the cursor after the last row and the system returns the following completion condition:

  • SQLCODE is set to 7632
  • SQLSTATE is set to ‘02000’

  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.

  If you specify FIRST, but there is no data, the system returns the following completion condition:

  • SQLCODE is set to 7632
  • SQLSTATE is set to ‘02000’

  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;