For Loop in PL/SQL

One of the statement  in oracle I use most is for loop in pl/sql. This for loop is very handy to use and the basic statement comes like this:


for variable in 1..x 
loop
   statement;
end loop;


The reverse loop statement is:

for variable in reverse 1..x 

loop

   statement;

end loop;

The example code comes like this

for v_cnt in 1 .. 100

       

loop

insert into table_x (pk, text) values (v_cnt, 'count_' || v_cnt);

end loop;

    for v_cnt in reverse 1 .. 100

loop

insert into table_x (pk, text) values (v_cnt, 'count_' || v_cnt);

end loop;

For in pl/sql could also work with cursor, this make handy as I said before. I like to use for loop in pl/sql with cursor so I don't have open and close the cursor, the for loop will do it automatically. Here is how for loop in pl/sql works:


for c_cur in (select statement)
loop
  statement;
end loop;

for c_cur in c_name 
loop
  statement;
end loop;


Where c_name is the declared cursor and c_cur is the variable (you don't need to declare it).

For example:


for c_cur in (select a, b from tab_x) 
loop
  insert into tab_y values(c_cur.a, c_cur.b);
end loop;



for c_cur in declared_cursor
loop
  insert into tab_y values(c_cur.a, c_cur.b);
end loop;

Read More