ORACLE WHILE LOOP STATEMENT

ORACLE WHILE LOOP STATEMENT.

           Oracle while loop used for  conditional execution of block of code.

Syntax:

                  while condition

                  loop

                       block of statements

                   end loop;

ex1: Display 10 numbers using while loop

declare
n number;
begin
dbms_output.put_line('WHILE loop in PL/SQL');
n:=1;
while n <=10
loop
dbms_output.put_line(n);
n:=n+1;
end loop;
end;

Output
SQL> /
WHILE loop in PL/SQL
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

Ex 2: Display 10 numbers in reverse order

declare
n number;
begin
dbms_output.put_line('WHILE loop in PL/SQL');
n:=10;
while n >0
loop
dbms_output.put_line(n);
n:=n-1;
end loop;
end;
/
 

OUTPUT

SQL> /
WHILE loop in PL/SQL
10
9
8
7
6
5
4
3
2
1

 Ex 3: Display real numbers using while loop

declare
n number;
begin
dbms_output.put_line('WHILE loop in PL/SQL');
n:=10.10;
while n >0
loop
dbms_output.put_line(n);
n:=n-0.5;
end loop;
end;
/

OUTPUT
SQL> /
WHILE loop in PL/SQL
10.1
9.6
9.1
8.6
8.1
7.6
7.1
6.6
6.1
5.6
5.1
4.6
4.1
3.6
3.1
2.6
2.1
1.6
1.1
.6
.1

PL/SQL procedure successfully completed.

Ex 4: Display emp table record using While loop & cursors.

declare
cursor c1 is select * from emp;
e emp%rowtype;
begin
dbms_output.put_line('WHILE loop in PL/SQL');
open c1;
fetch c1 into e;
while c1%found
loop
fetch c1 into e;
dbms_output.put_line(e.empno||' '||e.sal||' '||e.deptno);
end loop;
close c1;
end;
/
 

OUTPUT: 

SQL> /
WHILE loop in PL/SQL
7369 800 20
7499 1600 30
7521 1250 30
7566 2975 20
7654 1250 30
7698 2850 30
7782 2450 10
7788 3000 20
7839 5000 10
7844 1500 30
7876 1100 20
7900 950 30
7902 3000 20
7934 1300 10
7934 1300 10

PL/SQL procedure successfully completed.

 Tags:ORACLE WHILE LOOP STATEMENT,using oracle while loop, while loop syntax,oracle while loop,oracle while loop and cursors.display data using oracle while loop.