Cursor Rules
- No more than 16 cursors can be open at any one time in a given application.
- Whether or not 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 they can be expressed in internal hexadecimal notation.
Cursor Support Statements in Preprocessor2
This section explains how the various SQL statements that support cursors fit into a coherent whole in embedded SQL.
- Use DECLARE cursor_name CURSOR FOR the data returning statement to associate a cursor name with a multirow data returning statement.
You do not need to use a cursor to process a singleton SELECT.
- Use the following statements to manipulate the declared cursor.
Statement Function OPEN cursor_name Executes 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 currently fetched row from its base table UPDATE ... WHERE CURRENT OF cursor_name Updates the currently 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 | Executes 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. |