Table of Contents

PL/SQL - OPEN-FOR, FETCH, and CLOSE statements

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 :

You use three statements to control a cursor variable :

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.

Then, you FETCH rows from the result set.

When all the rows are processed, you CLOSE the cursor variable.

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;
/