Positioned Cursors | Teradata Vantage - Positioned Cursors - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

The ANSI/ISO SQL standard defines an updatable (positioned) cursor. This means that an application can define a cursor for a query and then update results rows using the same cursor.

Using a Positioned Cursor to Update or Delete a Row in a Stored Procedure

Following is the general process flow for updating or deleting a row using a FOR loop cursor in a stored procedure:
  1. Specify a FOR statement with the appropriate cursor specification.
  2. Fetch one row with each iteration of the FOR statement.

    The cursor then points to the next row in the response set.

  3. Update or delete the fetched row using the WHERE CURRENT OF clause with an UPDATE or DELETE statement, respectively.
  4. Continue the FOR iteration loop until the last row is fetched.
  5. Close the cursor by terminating the FOR statement.
Following is the general process flow for updating or deleting a row using a DECLARE CURSOR cursor in a stored procedure.
  1. Specify a DECLARE CURSOR statement with the appropriate cursor specification.
  2. Open the cursor with an OPEN cursor_name statement.
  3. Execute FETCH statements to fetch one row at a time from the response set.

    The next cursor movement depends on the scrollability option you specify.

    Option Next Cursor Movement
    FIRST First row in the result set
    NEXT Next row in the result set
  4. Update or delete a fetched row using the WHERE CURRENT OF clause with an UPDATE or DELETE statement, respectively.
  5. Close the cursor by performing a CLOSE cursor_name statement.

Using a Cursor to Update a Row in Preprocessor2

  1. Declare a cursor for a SELECT statement.
  2. Open the cursor using an OPEN statement.
  3. Retrieve a row using the FETCH statement.
  4. Update or delete the fetched row using the WHERE CURRENT OF clause with an UPDATE or DELETE statement, respectively.
  5. Close the cursor using a CLOSE statement.

Positioned Cursors for SELECT AND CONSUME Statements in Preprocessor2

SELECT AND CONSUME statements in positioned cursors are not valid.

When you select a row from a queue table using a SELECT AND CONSUME statement, the system automatically consumes that row; therefore, it makes no sense to execute SELECT AND CONSUME statements in positioned cursors because once selected, a consumed row cannot be deleted or updated. This means that any SELECT AND CONSUME statement executed from a positioned cursor fails, even though it does not attempt to delete or update any rows.

Because all cursors coded in a Preprocessor2 application default to being positioned cursors when you precompile the code with the TRANSACT or -tr preprocessor directives set to ANSI, this means that you cannot execute SELECT AND CONSUME statements from a cursor in an ANSI-style embedded SQL application.

Features Supporting Positioned Cursors

Several features enable ANSI/ISO SQL:2011-standard positioned cursor functionality, including:
  • WHERE CURRENT OF clause in DELETE and UPDATE statements
  • FOR CHECKSUM clause of the LOCKING request modifier (embedded SQL only)
  • FOR UPDATE clause in SELECT statements (stored procedures only)

WHERE CURRENT OF Clause

After you declare a positioned cursor, the WHERE CURRENT OF clause allows the DELETE and UPDATE statements to act on the row pointed to by the cursor.

For example, the following DELETE statement deletes the current customer row from the cursor named x01.

EXEC SQL
DELETE FROM customer
WHERE CURRENT OF x01;

FOR CHECKSUM Clause

Positioned cursors do not recognize resource locking levels. Instead, they assume that all actions involving a cursor are done within a single transaction and that terminating that transaction closes any open cursors.

The FOR CHECKSUM clause of the LOCKING request modifier, a Teradata extension to the ANSI/ISO SQL:2011standard adds to this functionality.

When you do not specify a LOCKING request modifier, all SELECT statements use a READ level lock. Positioned updates and deletes both default to a WRITE severity row hash lock.

The FOR CHECKSUM clause is not supported for stored procedures.

How CHECKSUM Locking Works

CHECKSUM locking is similar to ACCESS locking, but it adds checksums to the rows of a results table to allow a test of whether a row in the cursor has been modified by another user or session at the time an update is being made through the cursor.

If an application specifies an ACCESS lock and then issues a cursor UPDATE or DELETE, the row to be changed might have been altered by another application between the time the first application read the row and the time it issued the cursor UPDATE or DELETE statement.

If the checksum changes because another application has updated the row since it was last read by the current application, the current application receives an error.

The system returns an error to the application whenever any of the following requirements for CHECKSUM locking are not met:
  • The object locked must be a table
  • You must follow the LOCKING request modifier by a positioned cursor SELECT
  • The table you specify in the LOCKING request modifier must be the same as the table referenced in the FROM clause of the SELECT statement that follows it

CHECKSUM locks are valid only when used with a SELECT statement opened by a positioned cursor.

Example: LOCKING with CHECKSUM

This example uses CHECKSUM locking on the table named t.

LOCKING TABLE t
FOR CHECKSUM
SELECT i, text
FROM t;

Rules for Using Positioned Cursors

Positioned UPDATEs and DELETEs must be in the same transaction as the SELECT that opened the cursor they are using.

The following items are not updatable:
  • Dynamic cursors
  • Multistatement requests
The following items are not allowed in an SQL statement controlled by a positioned cursor:
  • Tables with triggers defined on them
  • Joins between multiple base tables
  • DISTINCT keyword
  • GROUP BY clause
  • HAVING clause
  • WITH clause
  • ORDER BY clause
  • Aggregate operators
  • Set operators
  • Correlated subqueries
  • Select lists having any of the following: duplicate column names, expressions, or functions

In a stored procedure, you can specify the FOR UPDATE clause in the DECLARE CURSOR statement to define a positioned cursor. If you do not specify the FOR UPDATE clause, the system returns a warning that the cursor is not updatable.

Multiple UPDATEs of a currently fetched row or UPDATEs followed by a DELETE of the currently fetched row are allowed.

Positioned updates and deletes must occur within the same transaction that contains the SELECT statement that defined the cursor.

When the application attempts a positioned UPDATE or DELETE against a row selected by an invalid SELECT, the system returns an error and rolls back the impacted transaction.

When a program attempts to UPDATE or DELETE a row using a WHERE CURRENT OF clause for a non-positioned cursor, the system returns an error stating that the cursor is not updatable.

The following table describes whether positioned cursors are valid based on the session mode under which they are created:
Session mode Validity
ANSI valid
Teradata not valid

Performance Optimization Guidelines for Positioned Cursors

Row Hash Locks

Because of the number of row hash locks required to implement cursor updates on large sets of data, you should use positioned updates and deletes for small sets of data only. When too many row hash locks are imposed, the transaction fails and aborts with a lock table overflow error.

Either avoid long duration transactions when using positioned cursors, or use CHECKSUM locking to avoid locking conflicts that might prevent other applications from reading or updating the tables used by your application. Note that CHECKSUM locking is not supported for stored procedures.

When the number of row hash locks becomes excessive and a lock table overflow error occurs, the Teradata Database issues a transaction level abort for any SQL application that receives the error.

Cursor Conflicts

Cursor conflicts can occur within a single transaction. Such conflicts occur when the system opens:
  • The system opens two cursors on the same table at the same time within a transaction, and one of the cursors attempts a positioned update or delete on a row in the table that is currently the subject of a positioned update or delete request by the other cursor.
  • The system opens a cursor on a table, makes a searched update or delete on that table, and then the cursor attempts to execute a positioned update or delete on the table.
  • The system opens a cursor on a table, makes a positioned update or delete through the cursor, and then attempts a searched update or delete on the same table.

The system returns a cursor conflict warning in all these situations, but executes the requested delete or update.

Related Topics

For more information about:
  • SELECT AND CONSUME, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
  • Cursors and Preprocessor2, see Cursor Rules and Teradata® Preprocessor2 for Embedded SQL Programmer Guide, B035-2446.
  • Examples of implementing positioned cursors in an embedded SQL client application, see Teradata® Preprocessor2 for Embedded SQL Programmer Guide, B035-2446.
  • Examples of implementing positioned cursors in stored procedures, see FOR.