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
A cursor variable or parameter (without a return type), previously declared within the current scope.
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.
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
A string literal, string variable, or string expression that represents any SQL statement. It must be of type
Used only if
select_statment includes placeholders, this clause specifies a list of bind arguments.
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
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;
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
OUT mode. That way, the subprogram can pass an open cursor back to the caller.