Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
|
|
View PDF |
The OPEN-FOR
statement executes the query associated with a cursor variable. It allocates database resources to process the query and identifies the result set -- the rows that meet the query conditions. The cursor variable is positioned before the first row in the result set. For more information, see "Using Cursor Variables (REF CURSORs)".
With the optional USING
clause, the statement associates a cursor variable with a query, executes the query, identifies the result set, positions the cursor before the first row in the result set, then zeroes the rows-processed count kept by %ROWCOUNT
. For more information, see "Building a Dynamic Query with Dynamic SQL". Because this statement can use bind variables to make the SQL processing more efficient, use the OPEN-FOR-USING
statement when building a query where you know the WHERE
clauses in advance. Use the OPEN-FOR
statement when you need the flexibility to process a dynamic query with an unknown number of WHERE
clauses.
Syntax
open for statement ::=
using clause ::=
Keyword and Parameter Description
bind_argument
An expression whose value is passed to the dynamic SELECT
statement.
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 previously 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.
select_statement
A query associated with cursor_variable
, which returns a set of values. The query can reference bind variables and PL/SQL variables, parameters, and functions. The syntax of select_statement
is similar to the syntax for select_into_statement
defined in "SELECT INTO Statement", except that the cursor select_statement
cannot have an INTO
clause. The length of the dynamic string cannot exceed 32767K.
USING clause
This optional clause specifies a list of bind arguments. At run time, bind arguments in the USING
clause replace corresponding placeholders in the dynamic SELECT
statement.
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 variable 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 do not need to 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 procedure or function 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.
You use three statements to process a dynamic multi-row query: OPEN-FOR-USING
, FETCH
, and CLOSE
. First, you OPEN
a cursor variable FOR
a multi-row query. Then, you FETCH
rows from the result set. When all the rows are processed, you CLOSE
the cursor variable.
The dynamic string can contain any multi-row SELECT
statement (without the terminator). The string can also contain placeholders for bind arguments. However, you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.
Every placeholder in the dynamic string must be associated with a bind argument in the USING
clause. Numeric, character, and string literals are allowed in the USING
clause, but Boolean literals (TRUE
, FALSE
, NULL
) are not. To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".
Any bind arguments in the query are evaluated only when the cursor variable is opened. To fetch from the cursor using different bind values, you must reopen the cursor variable with the bind arguments set to their new values.
Dynamic SQL supports all the SQL datatypes. For example, bind arguments can be collections, LOB
s, instances of an object type, and refs. As a rule, dynamic SQL does not support PL/SQL-specific types. For instance, bind arguments cannot be BOOLEAN
s or index-by tables.
For examples, see the following:
Related Topics