Oracle PL/SQL - Fetching Data with Cursor Variables

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. 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.-- from ww  w. j a  v a 2  s  .co 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    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