OPEN (Stored Procedures Form) | VantageCloud Lake - OPEN (Stored Procedures Form) - Teradata Vantage

Opens a declared cursor in a stored procedure and runs the SQL statement specified in its declaration.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Executable.

Stored procedures only.

Syntax

OPEN cursor_name [ USING { SQL_identifier | SQL_parameter } [,...] ] ;

Syntax Elements

cursor_name

The name of the cursor to be opened.

USING

Variables used as input to the SQL statement specified by cursor_name that must be declared before the OPEN statement.

SQL_identifier

A valid SQL identifier.

SQL_parameter

An SQL parameter.

Usage Notes

• Returning a Result Set

  The OPEN statement opens a result set cursor and runs the static or dynamic SELECT statement, which produces the result set. The system creates the result spool file, and positions the cursor before the first row of the spool file.

• General Rules
  • You must previously declare the cursor identified by cursor_name.
  • The cursor identified by cursor_name must not already be open.
• Rules for USING Clause
  • The number of variables specified must be the same as the number of parameter markers (the question mark character) in the identified statement. The nth variable corresponds to the nth marker.
  • You cannot run OPEN as a dynamic SQL statement.
  • You can only use the USING clause in an OPEN cursor when the cursor is a dynamic SQL form.
  • No more than 15 cursors can be open at one time. If an application has 15 cursors open, no other request can be issued until one or more cursors are closed.

Example: Using the OPEN Statement

The following example is valid because the OPEN statement follows a valid cursor declaration statement in the same scope.

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

Example: Using the OPEN Cursor Statement and the USING Clause

In this example, the OPEN cursor statement is extended to allow a USING clause.

CREATE PROCEDURE abc (IN data1v VARCHAR(10), IN data2v VARCHAR(10) )
   DYNAMIC RESULT SETS 1
  BEGIN
     DECLARE sql_stmt1 VARCHAR(100);
     DECLARE sales DECIMAL(8,2);
     DECLARE item INTEGER;
     DECLARE cstmt CURSOR WITH RETURN ONLY FOR stmt1;
     SET sql_stmt1 = 'SELECT  T1.item, T1.sales FROM T1 WHERE ?'
                     '= store_name AND ? = region;';
     PREPARE stmt1 FROM sql_stmt1;
     OPEN cstmt USING data1v, data2v;
  END;