DECLARE CURSOR (Request Form) | Teradata Vantage - DECLARE CURSOR (Request Form) - Advanced SQL Engine - Teradata Database

The request form of DECLARE CURSOR associates a cursor with an arbitrary Teradata SQL request, typically a multistatement request specified within an SQL string literal.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Syntax

DECLARE cursor_name CURSOR FOR 'request_specification'

Syntax Elements

cursor_name

The name of the cursor to be declared.

request_specification

A literal character string enclosed in apostrophes comprising any number of SQL statements separated by semicolons.

By default, the string is delimited by apostrophes (‘  ‘).

You can override this default using the QUOTESQL preprocessor parameter. Apostrophes syntactically distinguish the declaration of a request cursor from the other categories of cursor.

Usage Notes

• Usage Notes (All Forms)
• Statements in request_specification cannot include any of the following SQL statements:
  • CHECKPOINT
  • CLOSE
  • COMMIT
  • CONNECT
  • DATABASE
  • DESCRIBE
  • ECHO
  • EXECUTE
  • EXECUTE IMMEDIATE
  • FETCH
  • LOGON
  • OPEN
  • POSITION
  • PREPARE
  • REWIND
  • SET BUFFERSIZE
  • SET CHARSET
  • SET SESSION
• You can continue request_specification from line to line according to the syntax for continuation of string literals in the client language (embedded SQL only).
• Statements in request_specification cannot be Preprocessor2 declaratives (embedded SQL only).
• When the system opens the cursor, it updates the SQLCA to reflect the success (SQLCODE in the SQLCA is 0, SQLSTATE is set to ‘00000’) of one of the following:
  • The first statement of the request
  • The failure of the request, where failure is defined as an implicit rollback of the transaction
• A failure condition always overrides a success report. If successful, the activity count displays in the third SQLERRD element in the SQLCA. To obtain the results of executing other statements of the request, use the POSITION statement (embedded SQL only).
• If any of the statements in request_specification are data returning statements, the application program must use the POSITION statement to position to the appropriate result set to retrieve the response data set.
• OPEN automatically sets the position to the first statement of the request, so a POSITION statement is not required in this case.
• Use a FETCH statement with an appropriate host variable list (INTO clause) or output SQLDA (USING DESCRIPTOR clause) (embedded SQL only).

Example: Omitting Details of Continuation of a Literal Character String

The following example omits the details of continuation of a literal character string from line to line, the rules for which are determined by the client language.

DECLARE Ex CURSOR FOR
   ’UPDATE employee SET salary = salary * 1.08
    WHERE deptno = 500;
    SELECT deptname, name, salary
    FROM employee, department
    WHERE employee.deptno = department.deptno
    ORDER BY deptname, name’