Skip Headers

PL/SQL User's Guide and Reference
10g Release 1 (10.1)

Part Number B10807-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

CLOSE Statement

The CLOSE statement indicates that you are finished fetching from a cursor or cursor variable, and that the resources held by the cursor can be reused.

Syntax

Description of close_statement.gif follows
Description of the illustration close_statement.gif

Keyword and Parameter Description


cursor_name, cursor_variable_name, host_cursor_variable_name

When you close the cursor, you can specify an explicit cursor or a PL/SQL cursor variable, previously declared within the current scope and currently open.

You can also specify a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.

Usage Notes

Once a cursor or cursor variable is closed, you can reopen it using the OPEN or OPEN-FOR statement, respectively. You must close a cursor before opening it again, otherwise PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN. You do not need to close a cursor variable before opening it again.

If you try to close an already-closed or never-opened cursor or cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.

Example

DECLARE
   CURSOR emp_cv IS SELECT * FROM employees WHERE first_name = 'John';
   emp_rec employees%ROWTYPE;
BEGIN
   OPEN emp_cv;
   LOOP
       FETCH emp_cv INTO emp_rec;
       EXIT WHEN emp_cv%NOTFOUND;
   END LOOP;
   CLOSE emp_cv; /* Close cursor variable after last row is processed. */
END;
/

Related Topics

FETCH Statement, OPEN Statement, OPEN-FOR Statement, "Querying Data with PL/SQL".