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

Introduction

To change the result set, you must close the cursor, change the value of the variable, and then open the cursor again.

Demo

SQL>
SQL>-- w ww.j av  a 2  s .  c  o  m
SQL> drop table emp;

Table dropped.

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, 90);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 80);
SQL>
SQL>
SQL> DECLARE
  2    sal           emp.salary%TYPE;
  3    sal_multiple  emp.salary%TYPE;
  4    factor        INTEGER := 2;
  5
  6    CURSOR c1 IS
  7      SELECT salary, salary*factor FROM emp;
  8
  9  BEGIN
 10    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
 11    OPEN c1; 
 12    LOOP
 13      FETCH c1 INTO sal, sal_multiple;
 14      EXIT WHEN c1%NOTFOUND;
 15      DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
 16      DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
 17    END LOOP;
 18    CLOSE c1;
 19
 20    factor := factor + 1;
 21
 22    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
 23    OPEN c1;  -- PL/SQL evaluates factor
 24    LOOP
 25      FETCH c1 INTO sal, sal_multiple;
 26      EXIT WHEN c1%NOTFOUND;
 27      DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
 28      DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
 29    END LOOP;
 30    CLOSE c1;
 31  END;
 32  /
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