Oracle PL/SQL - Fetching from Cursor Variable into Collections

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Cursor Variables

Introduction

The following code fetches from a cursor variable into two collections, nested tables, using the BULK COLLECT clause of the FETCH statement.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   w  w  w. java  2  s  . c  o m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL> DECLARE
  2    TYPE empcurtyp IS REF CURSOR;
  3    TYPE namelist IS TABLE OF emp.last_name%TYPE;
  4    TYPE sallist IS TABLE OF emp.salary%TYPE;
  5    emp_cv  empcurtyp;
  6    names   namelist;
  7    sals    sallist;
  8  BEGIN
  9    OPEN emp_cv FOR
 10      SELECT last_name, salary FROM emp
 11      ORDER BY salary DESC;
 12
 13    FETCH emp_cv BULK COLLECT INTO names, sals;
 14    CLOSE emp_cv;
 15    -- loop through the names and sals collections
 16    FOR i IN names.FIRST .. names.LAST
 17    LOOP
 18      DBMS_OUTPUT.PUT_LINE
 19        ('Name = ' || names(i) || ', salary = ' || sals(i));
 20    END LOOP;
 21  END;
 22   /
Name = Lee, salary = 25000
Name = King, salary = 24000

PL/SQL procedure successfully completed.

SQL>

Related Topic