Fetching with a Cursor
The FETCH statements retrieve the rows in the result set one at a time. After each and every fetch, the cursor advance to the next row in the result set. An illustration is a shown:
FETCH c1 INTO my_empno, my_ename, my_deptno;
For each column the value returned by the query related with the cursor, there should be a parallel, type-compatible variable in the INTO list. Normally, you use the FETCH statement in the following way:
LOOP
FETCH c1 INTO my_record;
EXIT WHEN c1%NOTFOUND;
-- process data record
END LOOP;
The query can reference the PL/SQL variables within its scope. Though, any variables in the query are evaluated only when the cursor is opened. In the illustration, each retrieved the salary is multiplied by 2, even though the factor is incremented after each fetch:
DECLARE
my_sal emp.sal%TYPE;
my_job emp.job%TYPE;
factor INTEGER := 2;
CURSOR c1 IS SELECT factor*sal FROM emp WHERE job = my_job;
BEGIN
...
OPEN c1; -- here factor equals 2
LOOP
FETCH c1 INTO my_sal;
EXIT WHEN c1%NOTFOUND;
factor := factor + 1; -- does not affect FETCH
END LOOP;
END;
To change the result set or the values of the variables in the query, you should close and reopen the cursor with the input variables set to their new values.
Though, you can use a different INTO list on separate fetches with similar cursor. Each fetch retrieves another row and assigns values to the target variables, as the illustration shows:
DECLARE
CURSOR c1 IS SELECT ename FROM emp;
name1 emp.ename%TYPE;
name2 emp.ename%TYPE;
name3 emp.ename%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO name1; -- this fetches first row
FETCH c1 INTO name2; -- this fetches second row
FETCH c1 INTO name3; -- this fetches third row
...
CLOSE c1;
END;