Oracle basic loop statement

Oracle basic loop statement

                   Oracle 11g basic loop statement, it loops forever/until buffer overflow.


Synatx:
                       loop
                                block of statments
                       end loop;


for ex:  Display first 10 numbers using LOOP Statement.
                 declare
                     n number;
                 begin
                       n:=1;
                      dbms_output.put_line('Using Loop Stmt in PL/SQL Block');
                       loop
                            exit when n>10;
                            dbms_output.put_line(n);
                            n:=n+1;
                        end loop;
                    end;

Output:

SQL> /
Using Loop Stmt in PL/SQL Block
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.


for ex2:   Looping SQL Records 
                The following example loops all records in the emp table, reads empno and his salary into cursor as temorary buffer, 
l_eno,l_sal are PL/SQL variables to hold data.

Note: there should be some condition to exit the loop.  in this case when all records read(i.e no more records found in the table ) then exit the loop
 
  declare
                    l_eno emp.empno%type;
                    l_sal emp.sal%type;
                   cursor c1 is select empno,sal from emp;
                 begin
      open c1;
                      dbms_output.put_line('Using cursors in Loop Statment');
                      dbms_output.put_line('EmpNo  Salary');
                     loop
                        fetch c1 into l_eno,l_sal;
         exit when c1%notfound;
                        dbms_output.put_line(l_eno||' '||l_sal);
                        end loop;
                        close c1;

                    end;




OUTPUT
 
SQL> /
Using cursors in Loop Statmenet
EmpNo  Salary
9003 1200
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
7900 950
7902 3000
7934 1300

PL/SQL procedure successfully completed.

Tags: Oracle basic loop statement,Oracle loop statement, loop statement in oracle 11g,Exit loop in oracle, looping in oracle,exit when in loop statement, Open cursor in PL/SQL.