Cursors and Embedded SQL | VantageCloud Lake - Cursors and Embedded SQL - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Cursor Rules

  • No more than 16 cursors can be open at any one time in a given application.
  • Whether a cursor can be positioned depends on how you set the precompiler directives TRANSACT or -tr, as specified by the following table:
    Setting Default Type
    ANSI Positioned
    BTET Not positioned

    Teradata SQL does not support the ANSI/ISO SQL standard FOR READ ONLY and FOR UPDATE clauses for cursors.

  • An application that opens 16 cursors can only issue one of the following as the next statement:
    • CLOSE
    • COMMIT (if in COMMIT mode)
    • FETCH
    • LOGOFF
    • POSITION
    • REWIND
  • Construct cursor and dynamic statement identifiers from the following list of valid characters:
    • Uppercase Letters
    • Lowercase Letters
    • $
    • @
    • #
    • Digits
    • Underscores
  • Cursor and dynamic statement identifiers must begin with a national character and cannot exceed 18 characters.
  • A cursor and dynamic statement identifier cannot be an SQL keyword.
  • For purposes of comparison between identifiers, the case of letters is not significant.

    The preprocessor accepts statements in uppercase, lowercase or mixed case.

  • To support multibyte character sets, cursor and dynamic statement names can have multibyte characters, and can be expressed in internal hexadecimal notation.

Cursor Support Statements in Preprocessor2

This section explains how the SQL statements that support cursors fit into a coherent whole in embedded SQL.

  1. Use DECLARE cursor_name CURSOR FOR the data returning statement to associate a cursor name with a multirow data returning statement.

    You need not use a cursor to process a singleton SELECT.

  2. Use the following statements to manipulate the declared cursor.
    Statement Function
    OPEN cursor_name Runs the request (or requests) defined by the DECLARE CURSOR statement
    FETCH cursor_name INTO Uses the opened cursor to retrieve successive individual rows from the result set into host variables, using host language statements to increment the cursor based on a WHENEVER statement, or on testing the value of status codes returned to SQLCODE or SQLSTATE after each FETCH
    DELETE ... WHERE CURRENT OF cursor_name Deletes the fetched row from its base table
    UPDATE ... WHERE CURRENT OF cursor_name Updates the fetched row
    POSITION cursor_name Moves the cursor either forward or backward to the first row of the specified statement
    REWIND cursor_name Moves the cursor to the first row of the first (or only) statement of a request
    CLOSE cursor_name Closes the open cursor_name and terminates the data returning statement specified by the DECLARE CURSOR statement

Cursor Actions and Outcomes

Action SQL Statement Result
Define a statement or request to be associated with a cursor. DECLARE CURSOR Defines the association between a cursor and an SQL data returning statement.
Open a cursor. OPEN Runs the SQL data returning statement defined in DECLARE CURSOR.
Retrieve the next row in the result table. FETCH Retrieves a row from the result table.
Move the cursor to the first row of a specific SQL statement. POSITION

REWIND

 Positions the cursor to the first row of the result table of the named statement.
Update a row. UPDATE ... WHERE CURRENT OF Updates the contents of the current row.
Delete a row. DELETE ... WHERE CURRENT OF Deletes the current row from the table.
Close the cursor. CLOSE Terminates the retrieval process.