NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-25
CLOSE Statement
CLOSE Statement
CLOSE is a DML and dynamic SQL statement that closes a cursor in a host program.
After the CLOSE executes, the result table for the cursor (the output that results from
the execution of the SELECT for the cursor) no longer exists.
cursor
is the name of an open cursor to close.
:cursor-variable
is a host variable of type CHAR or VARCHAR that stores the name of an open
cursor to close.
Considerations—CLOSE
•
Authorization requirements
There are no authorization requirements for closing a cursor.
•
Effect on locks
Closing a cursor defined with REPEATABLE access does not affect locks. Locks on
audited tables are released when the TMF transaction finishes or aborts; locks on
nonaudited tables must be released with UNLOCK TABLE.
Closing a cursor defined with STABLE access for an audited table releases the row
lock acquired on the last FETCH only for a row that was not updated or deleted
using the cursor; locks on rows that were updated or deleted are not released until
the TMF transaction ends.
Closing a cursor defined with STABLE access for a nonaudited table releases the
row lock acquired on the last FETCH.
•
COMMIT WORK automatically closes cursors that reference audited tables. You
can use CLOSE only on a cursor for an audited table using STABLE or
REPEATABLE access within the same TMF transaction as the last FETCH on the
cursor. (If no FETCH statements execute after the cursor is opened, you can use
CLOSE on the cursor in any transaction or outside of a transaction.)
•
If your program is a server and the TMF transaction was started in a requester, the
program must close cursors to release space used by the cursors and to free locks
before returning control to the requester.
•
You can use the FREE RESOURCES statement instead of the CLOSE statement to
close all open cursors. After the cursor is closed, you must reopen the cursor before
referring to it in any statement.
CLOSE { cursor }
{ :cursor-variable }