OPEN (Stored Procedures Form) | Teradata Vantage - OPEN (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

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

Invocation

Executable.

Stored procedures only.

Syntax

OPEN cursor_name [ USING { SQL_identifier | SQL_parameter } [,...] ] ;
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.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Authorization

None.

Returning a Result Set

The OPEN statement opens a result set cursor and executes 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 n th variable corresponds to the n th marker.
  • You cannot execute 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;

Related Topics

For more information about: