Using Explicit Cursors  «Prev 

Opening Cursor in PL/SQL

Syntax and example of opening a cursor

cursor_name
Name of the cursor
argument_1,…,argument_n
A series of arguments. The arguments specified are optional.
OPEN pets;
Name of the cursor

Opening and Closing Cursor Variables

After declaring a cursor variable, you can open it with the OPEN FOR statement, which does the following:
  1. Associates the cursor variable with a query (typically, the query returns multiple rows)
    The query can include placeholders for bind variables, whose values you specify in the USING clause of the OPEN FOR statement.
  2. Allocates database resources to process the query
  3. Processes the query; that is:
    1. Identifies the result set If the query references variables, their values affect the result set.
    2. If the query has a FOR UPDATE clause, locks the rows of the result set
  4. Positions the cursor before the first row of the result set
You need not close a cursor variable before reopening it (that is, using it in another OPEN FOR statement). After you reopen a cursor variable, the query previously associated with it is lost. When you no longer need a cursor variable, close it with the CLOSE statement, thereby allowing its resources to be reused. After closing a cursor variable, you cannot fetch records from its result set or reference its attributes. If you try, PL/SQL raises the predefined exception INVALID_CURSOR. You can reopen a closed cursor variable.