Name

CLOSE CURSOR Statement

Synopsis

The CLOSE CURSOR statement closes a server-side cursor created with a DECLARE CURSOR statement.

Platform

Command

DB2

Supported, with variations

MySQL

Not supported

Oracle

Supported

PostgreSQL

Supported

SQL Server

Supported, with variations

SQL2003 Syntax

CLOSE cursor_name

Keywords

cursor_name

Names a cursor previously created with the DECLARE CURSOR statement.

Rules at a Glance

The CLOSE statement closes a cursor and destroys the cursor result set. All the database platforms release any locks that were held by the cursor, though this is not specified in the ANSI standard. For example:

CLOSE author_names_cursor;

Programming Tips and Gotchas

You can also close a cursor implicitly using a COMMIT statement or, for cursors defined WITH HOLD, using a ROLLBACK statement.

DB2

DB2 supports an added optional WITH clause on the CLOSE statement:

CLOSE cursor_name [WITH RELEASE]

where:

WITH RELEASE

Attempts to release all read locks held by the cursor. Note that DB2 is not always able to release all locks since some locks may be in use by other operations.

The WITH RELEASE clause is ignored on cursors run under isolation level 3 CS or UR, when the cursor is defined in a method, when the cursor is defined in a function, or when the cursor is defined in a stored procedure that is called by a function or method. WITH RELEASE may allow phantom read and nonrepeatable read anomalies when run under isolation levels RS or RR. For a detailed explanation of isolation ...

Get SQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.