Cursor with a FOR Loop:
When using FOR LOOP you need not declare a record or variables to store the cursor values, need not open, fetch and close the cursor. These functions are accomplished by the FOR LOOP automatically.
General Syntax for using FOR LOOP:
FOR record_name IN cusror_name
LOOP
process the row…
END LOOP;
Let’s use the previous example to learn how to use for loops in cursors.
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> FOR emp_rec in sales_cur
7> LOOP
8> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
9> || ' ' ||emp_cur.salary);
10> END LOOP;
11>END;
12> /
In the above example, when the FOR loop is processed a record ‘emp_rec’of structure ‘emp_cur’ gets created, the cursor is opened, the rows are fetched to the record ‘emp_rec’ and the cursor is closed after the last row is processed. By using FOR Loop in your program, you can reduce the number of lines in the program.
NOTE: In the examples given above, we are using backward slash ‘/’ at the end of the program. This indicates the oracle engine that the PL/SQL program has ended and it can begin processing the statements.
