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

Purpose

Defines and assigns a name to a selection cursor.

Invocation

Nonexecutable preprocessor declaration.

Embedded SQL only.

Syntax

DECLARE cursor_name [ SCROLL ] CURSOR FOR {
  COMMENT |
  EXPLAIN |
  HELP |
  SHOW |
  SELECT |
  SELECT AND CONSUME
}

cursor_name: The name you assign to this cursor.; The name can be any valid SQL identifier.
SCROLL: The declared cursor can fetch the row in the response set based on the FETCH orientation declared.; If you do not specify SCROLL, the cursor can only scroll forward to the next row in the response set. This is the default.; Use SCROLL only when the SQL statement is a SELECT statement.
COMMENT: A valid SQL comment-returning COMMENT statement.
EXPLAIN: A valid SQL EXPLAIN request modifier.
HELP: A valid SQL HELP statement.
SHOW: A valid SQL SHOW statement.
SELECT: A valid embedded SQL SELECT statement.
SELECT AND CONSUME: A valid embedded SQL SELECT AND CONSUME statement.

ANSI Compliance

ANSI/ISO SQL:2011-compatible with Teradata extensions.

Authorization

None.

Rules

You cannot specify the SQL WITH … BY clause.

The SELECT privilege is required on all tables specified in the cursor declaration or on the database containing the tables.

You must define each host variable referenced in the cursor specification before the selection DECLARE CURSOR statement.

If the table you identify in the FROM clause is a grouped view (a view defined using a GROUP BY clause), table_expression cannot contain any of the following clauses:

WHERE
GROUP BY
HAVING

If you specify a UNION operator, the description of each result table in the union must be identical except for column names. All columns of the spool table formed by the union of the result tables are unnamed.

The result is the union of the individual result tables for each query in the union, with any duplicate rows eliminated.

If you specify an ORDER BY clause, each of its column specifications must specify a column of the spool table by name.

Any unsigned integer column reference in the ORDER BY clause must specify a column of the spool table by relative number.

You can reference a named column either by a column specification or an unsigned integer.

You must reference an unnamed column by an unsigned integer.

You cannot specify a DELETE or UPDATE embedded SQL statement on a SELECT AND CONSUME cursor.

A cursor for a queue table is always read-only in PP2 ANSI mode. Therefore, a positioned DELETE or UPDATE (that is, deleting or updating the most current fetched cursor row) is not allowed for a queue table cursor in PP2 ANSI mode.

A scrollable cursor is not allowed for multistatement requests in PP2 ANSI mode.

Example: Using DECLARE CURSOR to Order a Project by proj_id

DECLARE ex1 CURSOR FOR
SELECT *
FROM project
ORDER BY proj_id

Example: Using DECLARE CURSOR to Order a Project by Numbers

DECLARE ex3 CURSOR FOR
SELECT a, b, ’X’
FROM tablex
WHERE a > b
   
UNION
    
(SELECT a, b, ’Y’
FROM tabley
WHERE a > b
    
INTERSECT
   
SELECT a, b, ’Y’
FROM tablez
WHERE a > b)
ORDER BY 1,2

Example: Using DECLARE CURSOR to Order a Project by deptname and name

DECLARE ex2 CURSOR FOR
EXPLAIN SELECT deptname, name
FROM employee, department
WHERE employee.deptno = department.deptno
ORDER BY deptname, name

Example: Using DECLARE CURSOR for an Employee Help Table

DECLARE ex4 CURSOR FOR
HELP TABLE employee

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

SQL Stored Procedures and Embedded SQL