Definition
The OPEN-FOR statement executes the query associated with a cursor variable. It's an important statement of the dynamic sql Management.
It allocates database resources to process the query and identifies the result set – the rows that meet the query conditions.
The OPEN-FOR statement permit the use of SQL dynamic :
- for DML (SELECT, UPDATE, INSERT, DELETE)
- but not for DDL (CREATE, ALTER, DROP). You must see the PL/SQL - Execute Immediate for this purpose.
You use three statements to control a cursor variable :
- OPEN-FOR,
First, you OPEN a cursor variable FOR a multi-row query. 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.
- FETCH,
Then, you FETCH rows from the result set.
- and CLOSE.
When all the rows are processed, you CLOSE the cursor variable.
Articles Related
Fetching
into a record with a cursor
As the following example shows, you can fetch rows from the result set of a dynamic multi-row query into a record:
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec emp%ROWTYPE;
sql_stmt VARCHAR2(200);
my_job VARCHAR2(15) := 'CLERK';
BEGIN
sql_stmt := 'SELECT * FROM emp WHERE job = :j';
OPEN emp_cv FOR sql_stmt USING my_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
-- process record
END LOOP;
CLOSE emp_cv;
END;
/
in a collection (BULK COLLECT INTO)
Bulk binding lets Oracle bind a variable in a SQL statement to a collection of values. The collection type can be any PL/SQL collection type (index-by table, nested table, or varray). The collection elements must have a SQL datatype such as CHAR, DATE, or NUMBER. Three statements support dynamic bulk binds: EXECUTE IMMEDIATE, FETCH, and FORALL.
See PL/SQL - Bulk Collect - Fetch collection of (records|Collection)
Example 1
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(25);
emp_cv EmpCurTyp;
empids NumList;
enames NameList;
sals NumList;
BEGIN
OPEN emp_cv FOR 'SELECT employee_id, last_name FROM employees';
FETCH emp_cv BULK COLLECT INTO empids, enames;
CLOSE emp_cv;
END;
/
Example 2
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
l_tab t_bulk_collect_test_tab;
l_cursor SYS_REFCURSOR;
BEGIN
-- With Open For Statement
OPEN l_cursor FOR 'SELECT * FROM bulk_collect_test';
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line('Dynamic FETCH : ' || l_tab.count);
-- With Execute Immediate Statement
EXECUTE IMMEDIATE 'SELECT * FROM bulk_collect_test'
BULK COLLECT INTO l_tab;
DBMS_OUTPUT.put_line('Dynamic EXECUTE: ' || l_tab.count);
END;
/