This is default featured slide 1 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 2 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 3 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 5 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

 

Friday, November 25, 2011

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;