DECLARE CURSOR (Stored Procedures Form) | Teradata Vantage - DECLARE CURSOR (Stored Procedures 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 cursor.

Invocation

Nonexecutable.

Stored procedures only.

Syntax

DECLARE DECLARE cursor_name [ [NO] SCROLL ] CURSOR
  [ WITHOUT RETURN |
    WITH RETURN [ONLY] [ TO { CALLER | CLIENT } ]
  ]
  FOR { cursor_specification [ FOR { READ ONLY | UPDATE } ] |
        statement_name
      } [;]
cursor_name
The name of the cursor to be declared.
SCROLL
Specifies whether the declared cursor can fetch the next row in the result set, or fetch the first row in the result set from any location in that set.
  • SCROLL can either scroll forward to the next row in a result set or scroll directly to the first row in the result set.
  • NO SCROLL, which is the default, can only scroll forward to the next row in the result set.
NO SCROLL
Specifies whether the declared cursor can fetch the next row in the result set, or fetch the first row in the result set from any location in that set.
  • SCROLL can either scroll forward to the next row in a result set or scroll directly to the first row in the result set.
  • NO SCROLL, which is the default, can only scroll forward to the next row in the result set.
WITHOUT RETURN
The procedure does not return a result set.
WITHOUT RETURN is the default.
WITH RETURN
Specifies:
  • that the cursor is a result set cursor.
  • to return a result set to the current stored procedure (the procedure that opened the cursor) and to the caller of the procedure.
WITH RETURN TO CALLER
Specifies:
  • that the cursor is a result set cursor.
  • to return a result set to the current stored procedure (the procedure that opened the cursor) and to the caller of the procedure.
WITH RETURN ONLY
Specifies:
  • that the cursor is a result set cursor.
  • to return a result set only to the caller of the stored procedure.
WITH RETURN ONLY TO CALLER
Specifies:
  • that the cursor is a result set cursor.
  • to return a result set only to the caller of the stored procedure.
WITH RETURN TO CLIENT
Specifies:
  • that the cursor is a result set cursor.
  • to return a result set to the client (application such as BTEQ) and to the current stored procedure (the procedure that opened the cursor).
WITH RETURN ONLY TO CLIENT
Specifies:
  • that the cursor is a result set cursor.
  • to return a result set only to the client (application such as BTEQ).
cursor_specification
The SELECT statement that retrieves the rows the cursor reads or updates.
READ ONLY
Specifies that you can only use the cursor to read the rows in the result set.
This is the default.
UPDATE
Specifies that you can use the cursor to update or delete the rows in the result set.
If UPDATE is specified, one of the following must also be specified:
  • WITH RETURN ONLY
  • WITH RETURN ONLY TO CALLER
  • WITH RETURN TO CLIENT
  • WITH RETURN ONLY TO CLIENT
statement_name
The identifier for the dynamic form of the DECLARE CURSOR statement.

ANSI Compliance

The ONLY keyword, and the TO CALLER and TO CLIENT options are Teradata extensions to the ANSI/ISO SQL:2011 standard.

Authorization

None.

DECLARE CURSOR and FOR Statements

FOR statements also define cursors.

General Rules

  • You must specify a cursor declaration after any local declarations and before any handler declarations
  • The cursor name must be unique within the declaration of the same compound statement.
  • If you do not specify an explicit scrollability clause, NO SCROLL is the default and the cursor can only scroll forward.
  • If you do not specify an explicit updatability clause, FOR READ ONLY is the default.
  • To create a positioned cursor, specify an explicit FOR UPDATE clause. That is, the cursor can be used for delete and update operations on its result rows.

Rules for Returning Result Sets

  • Specify the number of result sets with the DYNAMIC RESULT SETS clause in the CREATE/REPLACE PROCEDURE statement.
  • If you specify one of the WITH RETURN clauses, the stored procedure returns a result set to the current procedure, to the client, or to the caller for each result set cursor you declare.
  • Specifying WITH RETURN is the same as specifying WITH RETURN TO CALLER.
  • Specifying WITH RETURN ONLY is the same as specifying WITH RETURN ONLY TO CALLER.
  • If you specify WITH RETURN ONLY, the stored procedure that opens the cursor cannot use the cursor to fetch rows from the result set.
  • If you specify WITH RETURN or WITH RETURN TO CALLER, you cannot specify FOR UPDATE.
  • If you specify TO CLIENT, the result set is returned to the client application even if called from a nested stored procedure.
  • If you specify WITH RETURN ONLY TO CLIENT, the stored procedure returns the result set to the client, not to the stored procedure or external stored procedure that called the target procedure.
  • If more than one stored procedure specifies WITH RETURN, the system returns the result sets in the order opened.
  • Leave the result set cursors open to return the result sets to the current stored procedure, caller, or client. The system does not return a result set if the result set cursor is closed.
The returned result set:
  • Inherits the response attributes (response mode, keep response, LOB response mode) of the caller, not of the stored procedure that created it. For example, if you submit a CALL in BTEQ, the system sends the result set to the stored procedure in Indicator mode and sends the result set to BTEQ in Field mode.
  • Is based on the collation of the stored procedure, not the caller or session collation.

Example: Using a Cursor in a Stored Procedure

The following example illustrates the correct use of a cursor in a stored procedure. The declarations occur at lines 6 and 10.

CREATE PROCEDURE spsample1()
BEGIN
  L1: BEGIN
      DECLARE vname CHARACTER(30);
      DECLARE vamt INTEGER;
      DECLARE empcursor CURSOR FOR
        SELECT empname, salary
        FROM empdetails
        ORDER BY deptcode;
      DECLARE deptcursor CURSOR FOR
        SELECT deptname
        FROM department;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
        BEGIN
          OPEN empcursor;
          ...
        END;
        ...
        ...
  END L1;
END;

Example: Using an Implicit FOR READ ONLY Cursor

The following example illustrates an implicit FOR READ ONLY cursor. The stored procedure does not specify a FOR UPDATE clause in the declaration of empcursor, so it is FOR READ ONLY by default.

CREATE PROCEDURE sp1()
BEGIN
  DECLARE empcursor CURSOR FOR
    SELECT *
    FROM employee
    WHERE deptcode = 101
    ORDER BY empid;
   ...
END;

Example: Using an Explicitly Declared FOR READ ONLY Cursor

The following example illustrates an explicitly declared FOR READ ONLY cursor.

CREATE PROCEDURE sp1()
BEGIN
  DECLARE empcursor CURSOR FOR
    SELECT *
    FROM employee
    WHERE deptcode = 101
  FOR READ ONLY;
    ...
END;

Example: Using a FOR UPDATE Cursor

The following example illustrates a FOR UPDATE cursor.

CREATE PROCEDURE sp1()
BEGIN
  DECLARE empcursor CURSOR FOR
    SELECT *
    FROM employee
    WHERE deptcode = 101
  FOR UPDATE;
      ...
END;

Example: Using WITH RETURN ONLY TO CLIENT

The following example illustrates the use of WITH RETURN ONLY TO CLIENT.

DECLARE results1 CURSOR WITH RETURN ONLY TO CLIENT FOR
       SELECT store, item, on_hand 
       FROM inventory
       ORDER BY store, item;
OPEN results1;

Example: Using Dynamic SQL Statements in a Stored Procedure

The following example illustrates the use of dynamic SQL statements in a stored procedure defined without a WITH RETURN clause.

CREATE PROCEDURE GetEmployeeSalary
(IN EmpName VARCHAR(100), OUT Salary DEC(10,2))
BEGIN
  DECLARE SqlStr VARCHAR(1000);
  DECLARE C1 CURSOR FOR S1;
  SET SqlStr = 'SELECT Salary FROM EmployeeTable WHERE EmpName = ?';
  PREPARE S1 FROM SqlStr;
  OPEN C1 USING EmpName;
  FETCH C1 INTO Salary;
  CLOSE C1;
END;

Example: Using the Dynamic Form of the DECLARE CURSOR Statement

The following example illustrates the dynamic form of the DECLARE CURSOR statement. The cursor statement specifies a result cursor with a dynamic SELECT.

DECLARE statement1_str VARCHAR(500);
DECLARE result_set CURSOR WITH RETURN ONLY FOR stmt1;

SET statement1_str = 'SELECT store, item, on_hand FROM inventory ORDER BY store, item;'
PREPARE stmt1 FROM statement1_str;
OPEN result_set;

Example: Using a Dynamic Parameter Marker

The following example illustrates the use of a dynamic parameter marker. The data for the dynamic parameter marker is passed in the OPEN statement.

DECLARE Store_num INTEGER;
DECLARE statement1_str VARCHAR(500);
DECLARE result_set CURSOR WITH RETURN ONLY FOR stmt1;

SET statement1_str = 'SELECT store, item, on_hand'
               ' FROM inventory WHERE store = ? ORDER BY store, item;'
PREPARE stmt1 FROM statement1_str;
SET Store_num = 76;
OPEN result_set USING Store_num;

Related Topics

For more information about: