elmama
​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


Explicit PL/SQL cursor loop syntax 


Here's a quick low-down on the various explicit cursors/loops available in PL/SQL with sample code snippets to show their use.

1)           DECLARE
                       cursor c1 is select n1, n2 from table1 where col1 = 1;
               BEGIN
                        For c1_rec in c1
                        Loop
                                insert into table2(col2,col3)        values(c1_rec.n1,c1_rec.n2);
                        End Loop;
                END;



2)             DECLARE
                         cursor c1 is select n1 from table1
                 BEGIN
                         Open c1;
                         Loop
                             Fetch c1 into :id;
                                      ..
                                      ..
                                      ..

                             Exit when c1%notfound
                         End Loop;
                  END;



3)               for c1_rec in (select n1 from table1)
                  Loop
                          If c1_rec.n1 > 5
                                   ..
                                   ..
                                   ..
                          End if;
                  End loop;


4)               DECLARE
                           cursor c1(low number default 0, high number default 100)
                           is select n1 from table1 where n1 between low and high
                   BEGIN
                           Open c1(50,100)
                           Loop
                                  ..
                                  ..
                                  ..
                           End Loop;
                   END;


Explicit cursors have the following four attributes associated with them which can be used to control the flow of your program during their use.

%FOUND     -            e.g       IF c1%FOUND THEN ...........
%ISOPEN    -            e.g       IF c1%ISOPEN THEN ..........
%NOTFOUND -        e.g       EXIT WHEN c1%NOTFOUND
%ROWCOUNT -      e.g        IF C!%ROWCOUNT > 0 THEN ............