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.
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’