CLOSE Statement | VantageCloud Lake - CLOSE - Teradata Vantage

Closes an open cursor and releases the resources the cursor held while 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.

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 run 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, there is no explicit CLOSE, and 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 COMMIT and ROLLBACK.
• For other DML statements that can be used by stored procedure and embedded SQL applications, see Statement Syntax.