DECLARE CURSOR (Stored Procedures Form) | VantageCloud Lake - DECLARE CURSOR (Stored Procedures Form) - Teradata Vantage

The stored procedures form of DECLARE CURSOR associates a cursor with a SELECT or other data returning statement within the body of a stored procedure FOR statement.

ANSI Compliance

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

Required Privileges

None.

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
      } [;]

Syntax Elements

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 the following:

• 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 the following:

• 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 the following:

• 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 the following:

• 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 the following:

• 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 the following:

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

Usage Notes

• DECLARE CURSOR Usage Notes (All Forms)
• 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 shows 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 shows an implicit FOR READ ONLY cursor. The stored procedure does not specify a FOR UPDATE clause in the declaration of empcursor, so the cursor 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 shows 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 shows 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 shows 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 shows 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 shows 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 shows 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 Information

• SCROLL, see FETCH (Stored Procedures Form).
• NO SCROLL, see FETCH (Stored Procedures Form).
• Specifying the number of result sets with the DYNAMIC RESULT SETS clause, see CREATE PROCEDURE and REPLACE PROCEDURE (External Form) and CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form).
• Differences between DECLARE CURSOR and FOR statements, see "General Rules" in Cursors and Stored Procedures.
• Positioned cursors, see Positioned Cursors.