CLOSE | Teradata Vantage - CLOSE - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
vqj1592443206677.ditamap
dita:ditavalPath
vqj1592443206677.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

Closes an open cursor and releases the resources held by the cursor while it was open.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Executable.

Stored procedures and embedded SQL.

Syntax

CLOSE cursor_name

Syntax Elements

cursor_name
The name of an open cursor to be closed.

Usage Notes

Do not CLOSE a result set cursor. If the stored procedure closes the result set cursor, the result set is deleted and not returned.

The cursor identified by cursor_name must be previously declared.

The cursor identified by cursor_name must be open.

If cursor_name is not open at the time CLOSE is submitted within a stored procedure, the following runtime exception occurs:
  • SQLCODE is set to 7631
  • SQLSTATE is set to ‘24501’

When control passes from a compound stored procedure statement, the stored procedure implicitly closes all open cursors declared within the body of that compound statement.

You cannot execute CLOSE as a dynamic SQL statement.

Example: Opening the Cursor

The following CLOSE example is valid because the cursor identified by cursor name projcursor is OPEN before the CLOSE.

CREATE PROCEDURE sp1 (OUT par1 INTEGER, OUT Par2 CHAR(30))
BEGIN
  DECLARE projcursor CURSOR FOR
    SELECT *
    FROM project
    ORDER BY projid;
  OPEN projcursor;
  Label1:
  LOOP:
    FETCH projcursor INTO par1, par2;
    IF (SQLSTATE = '02000') THEN
      LEAVE label1;
    END IF;
  END LOOP label1;
  CLOSE projcursor;
END;

Example: Closing the Cursor

In the following example, CLOSE explicitly closes projcursor. The empcursor cursor is OPEN and there is no explicit CLOSE. In this case, empcursor closes implicitly when the stored procedure terminates.

CREATE PROCEDURE sp1 (IN par1 CHAR(5))
BEGIN
  DECLARE projcursor CURSOR FOR
    SELECT *
    FROM project
    ORDER BY projid;
  DECLARE empcursor CURSOR FOR
    SELECT *
    FROM employee
    WHERE dept_code = par1;
  OPEN projcursor;
  OPEN empcursor;
  CLOSE projcursor;
END;

Related Information

  • For CLOSE, see OPEN (Embedded SQL Form) and OPEN (Stored Procedures Form).
  • For COMMIT and ROLLBACK statements closing open cursors, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
  • For other DML statements that can be used by stored procedure and embedded SQL applications, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.