PL/SQL User's Guide and Reference

Contents Index Home Previous Next

OPEN Statement

Description

The OPEN statement executes the multi-row query associated with an explicit cursor. It also allocates resources used by Oracle to process the query and identifies the result set, which consists of all rows that meet the query search criteria. The cursor is positioned before the first row in the result set. For more information, see "Managing Cursors" [*].

Syntax

open_statement ::= 

OPEN cursor_name [(cursor_parameter_name[, 
  cursor_parameter_name]...)];

Keyword and Parameter Description

cursor_name

This identifies an explicit cursor previously declared within the current scope and not currently open.

cursor_parameter_name

This identifies a cursor parameter; that is, a variable declared as the formal parameter of a cursor. A cursor parameter can appear in a query wherever a constant can appear. For the syntax of cursor_parameter_ declaration, see "Cursors" [*].

Usage Notes

Generally, PL/SQL parses an explicit cursor only the first time it is opened and parses a SQL statement (thereby creating an implicit cursor) only the first time the statement is executed. All the parsed SQL statements are cached. A SQL statement must be reparsed only if it is bumped out of the cache by a new SQL statement.

So, although you must close a cursor before you can reopen it, PL/SQL need not reparse the associated SELECT statement. If you close, then immediately reopen the cursor, a reparse is definitely not needed.

Rows in the result set are not retrieved when the OPEN statement is executed. The FETCH statement retrieves the rows. With a FOR UPDATE cursor, the rows are locked when the cursor is opened.

If a cursor is currently open, you cannot use its name in a cursor FOR loop.

If formal parameters are declared, actual parameters must be passed to the cursor. The values of actual parameters are used when the cursor is opened. The datatypes of the formal and actual parameters must be compatible. The query can also reference PL/SQL variables declared within its scope.

Unless you want to accept default values, each formal parameter in the cursor declaration must have a corresponding actual parameter in the OPEN statement. Formal parameters declared with a default value need not have a corresponding actual parameter. They can simply assume their default values when the OPEN statement is executed.

The formal parameters of a cursor must be IN parameters. Therefore, they cannot return values to actual parameters.

You can associate the actual parameters in an OPEN statement with the formal parameters in a cursor declaration using positional or named notation. For more information, see "Positional and Named Notation" [*].

Examples

Given the cursor declaration

CURSOR parts_cur IS SELECT part_num, part_price FROM parts;

the following statement opens the cursor:

OPEN parts_cur;

Given the cursor declaration

CURSOR emp_cur(my_ename CHAR, my_comm NUMBER DEFAULT 0)
   IS SELECT * FROM emp WHERE ...

any of the following statements opens the cursor:

OPEN emp_cur('LEE');
OPEN emp_cur('BLAKE', 300);
OPEN emp_cur(employee_name, 150);
OPEN emp_cur('TRUSDALE', my_comm);

In the last example, an actual parameter in the OPEN statement has the same name as its corresponding formal parameter in the cursor declaration. To avoid confusion, use unique identifiers.

Related Topics

CLOSE Statement, Cursors, FETCH Statement, LOOP Statements

Contents Index Home Previous Next