Download PL/SQL User's Guide and Reference

OPEN-FOR-USING Statement
OPEN-FOR-USING Statement
The OPEN-FOR-USING 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" on page 7-4.
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_using_statement
cursor_variable_name
OPEN
FOR
:
dynamic_string
host_cursor_variable_name
,
USING
bind_argument
;
Keyword and Parameter Description
cursor_variable_name
A weakly typed cursor variable (one without a return type) previously declared within
the current scope.
bind_argument
An expression whose value is passed to the dynamic SELECT statement.
dynamic_string
A string literal, variable, or expression that represents a multi-row SELECT statement.
host_cursor_variable_name
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.
USING ...
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 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.
PL/SQL Language Elements
13-97