Cursor FOR loop

Besides the "classic" FOR loop, PL/SQL makes available also a "cursor" FOR loop. The syntax very similar:

FOR record IN (...)
LOOP
(...)
END LOOP;

But here, record is implicitely declared by PL/SQL to match the required type, and after the IN clause, a cursor_name or a SELECT statement is expected.

It makes sense using an explicit SELECT only when the code is simple, otherwise a cursor is the preferred option.

Printing the european countries to the output buffer could be done easily with a cursor FOR-SELECT loop:

begin
for my_rec in (select * from countries where region_id = 1)
loop
dbms_output.put_line(my_rec.country_id || ' ' || my_rec.country_name);
end loop;
end;

But, as we said, it is cleaner doing the same through a FOR-CURSOR loop:

declare
cursor eu_cur is
select * from countries where region_id = 1;
begin
for my_rec in eu_cur
loop
dbms_output.put_line(my_rec.country_id || ': ' || my_rec.country_name);
end loop;
end;

Actually, we could write FOR-CURSOR/SELECT equivalent code explicitely creating and managing a recordset and cursor in this way:

declare
cursor eu_cur is
select * from countries where region_id = 1;

my_rec eu_cur%ROWTYPE;
begin
open eu_cur;
loop
fetch eu_cur into my_rec;
exit when eu_cur%notfound;

dbms_output.put_line(my_rec.country_id || ': ' || my_rec.country_name);
end loop;
close eu_cur;
end;

Your choice ...

Chapter 5 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is about loops.

No comments:

Post a Comment