Cursor with a While Loop:
Lets modify the previous simple loop program to use while loop.
1> DECLARE
2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
4> emp_rec emp_cur%rowtype;
5> BEGIN
6> IF NOT sales_cur%ISOPEN THEN
7> OPEN sales_cur;
8> END IF;
9> FETCH sales_cur INTO sales_rec;
10> WHILE sales_cur%FOUND THEN
11> LOOP
12> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
13> || ' ' ||emp_cur.salary);
15> FETCH sales_cur INTO sales_rec;
16> END LOOP;
17> END;
18> /
In the above example, in line no 10 we are using %FOUND to evaluate if the first fetch statement in line no 9 returned a row, if true the program moves into the while loop. In the loop we use fetch statement again (line no 15) to process the next row.
If the fetch statement is not executed once before the while loop the while condition will return false in the first instance and the while loop is skipped. In the loop, before fetching the record again, always process the record retrieved by the first fetch statement, else you will skip the first row.
