Oracle PL/SQL - Fetching Data with Cursor Variables

Introduction

The following code uses one cursor variables.

The first OPEN FOR statement includes the query itself.

The second OPEN FOR statement references a variable whose value is a query.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- w  w w  .  ja va  2  s  .  com

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    cv SYS_REFCURSOR;  -- cursor variable
  3
  4    v_lastname  emp.last_name%TYPE;  -- variable for last_name
  5    v_jobid     emp.job_id%TYPE;     -- variable for job_id
  6
  7    query_2 VARCHAR2(200) := 'SELECT * FROM emp';
  8
  9    v_emp emp%ROWTYPE;  -- record variable row of table
 10
 11  BEGIN
 12    OPEN cv FOR
 13      SELECT last_name, job_id FROM emp ORDER BY last_name;
 14
 15    LOOP  -- Fetches 2 columns into variables
 16      FETCH cv INTO v_lastname, v_jobid;
 17      EXIT WHEN cv%NOTFOUND;
 18      DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
 19    END LOOP;
 20
 21    OPEN cv FOR query_2;
 22
 23    LOOP  -- Fetches entire row into the v_emp record
 24      FETCH cv INTO v_emp;
 25      EXIT WHEN cv%NOTFOUND;
 26      DBMS_OUTPUT.PUT_LINE( RPAD(v_emp.last_name, 25, ' ') ||
 27                                 v_emp.job_id );
 28    END LOOP;
 29
 30    CLOSE cv;
 31  END;
 32  /
King                     CODER
Lee                      TESTER
King                     CODER
Lee                      TESTER

PL/SQL procedure successfully completed.

SQL>

Related Topic