DECLARE CURSOR (Request Form) | Teradata Vantage - DECLARE CURSOR (Request Form) - 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™

Purpose

Defines and assigns a name to a request cursor.

Invocation

Nonexecutable preprocessor declaration.

Embedded SQL only.

Syntax

DECLARE cursor_name CURSOR FOR 'request_specification'
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.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Authorization

None.

Rules

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’