Oracle PL/SQL - Variable in Cursor Variable Query-Result Set Change

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

Introduction

To change the result set, change the value of the variable and open the cursor variable again for the same query.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w w  w.  j  av  a2  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    sal           emp.salary%TYPE;
  3    sal_multiple  emp.salary%TYPE;
  4    factor        INTEGER := 2;
  5
  6    cv SYS_REFCURSOR;
  7
  8  BEGIN
  9    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
 10
 11    OPEN cv FOR
 12      SELECT salary, salary*factor
 13      FROM emp;   -- PL/SQL evaluates factor
 14
 15    LOOP
 16      FETCH cv INTO sal, sal_multiple;
 17      EXIT WHEN cv%NOTFOUND;
 18      DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
 19      DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
 20    END LOOP;
 21
 22    factor := factor + 1;
 23
 24    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
 25
 26    OPEN cv FOR
 27      SELECT salary, salary*factor
 28      FROM emp;   -- PL/SQL evaluates factor
 29
 30    LOOP
 31      FETCH cv INTO sal, sal_multiple;
 32      EXIT WHEN cv%NOTFOUND;
 33      DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
 34      DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
 35    END LOOP;
 36
 37    CLOSE cv;
 38  END;
 39  /
factor = 2
sal          = 24000
sal_multiple = 48000
sal          = 25000
sal_multiple = 50000
factor = 3
sal          = 24000
sal_multiple = 72000
sal          = 25000
sal_multiple = 75000

PL/SQL procedure successfully completed.

SQL>

Related Topic