To change the result set, you must close the cursor, change the value of the variable, and then open the cursor again.
SQL> SQL>-- ww w .j ava2 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>