OPEN-FOR Statement

The OPEN-FOR statement executes the SELECT statement associated with a cursor variable. It allocates database resources to process the statement, identifies the result set (the rows that meet the conditions), and positions the cursor variable before the first row in the result set.

With the optional USING clause, the OPEN-FOR statement processes a dynamic SELECT statement that returns multiple rows: it associates a cursor variable with the SELECT statement, executes the statement, identifies the result set, positions the cursor before the first row in the result set, and zeroes the rows-processed count kept by %ROWCOUNT.

Syntax

open_for_statement ::=

open_for_statement
Description of the illustration open_for_statement.gif

using_clause ::=

using_clause
Description of the illustration using_clause.gif

Keyword and Parameter Descriptions

cursor_variable_name

A cursor variable or parameter (without a return type), previously declared within the current scope.

host_cursor_variable_name

A cursor variable, which must be declared in a PL/SQL host environment and passed to PL/SQL as a bind argument (hence the colon (:) prefix). The data type of the cursor variable is compatible with the return type of any PL/SQL cursor variable.

select_statement

A string literal, string variable, or string expression that represents a multiple-row SELECT statement (without the final semicolon) associated with cursor_variable_name. It must be of type CHAR, VARCHAR2, or CLOB (not NCHAR or NVARCHAR2).

dynamic_string

A string literal, string variable, or string expression that represents any SQL statement. It must be of type CHAR, VARCHAR2, or CLOB.

USING

Used only if select_statment includes placeholders, this clause specifies a list of bind arguments.

bind_argument

Either an expression whose value is passed to the dynamic SQL statement (an in bind), or a variable in which a value returned by the dynamic SQL statement is stored (an out bind). The default parameter mode for bind_argument is IN.

Usage Notes

You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To open the host cursor variable, you can pass it as a bind argument to an anonymous PL/SQL block. You can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens five cursor variables in a single round-trip:

/* anonymous PL/SQL block in host environment */
BEGIN
  OPEN :emp_cv FOR SELECT * FROM employees;
  OPEN :dept_cv FOR SELECT * FROM departments;
  OPEN :grade_cv FOR SELECT * FROM salgrade;
  OPEN :pay_cv FOR SELECT * FROM payroll;
  OPEN :ins_cv FOR SELECT * FROM insurance
END;

Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.

Unlike cursors, cursor variables do not take parameters. Instead, you can pass whole queries (not just parameters) to a cursor variable. Although a PL/SQL stored subprogram can open a cursor variable and pass it back to a calling subprogram, the calling and called subprograms must be in the same instance. You cannot pass or return cursor variables to procedures and functions called through database links. When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller.

Examples

Related Topics