ORACLE FOR LOOP STATEMENT

ORACLE FOR LOOP STATEMENT

               Oracle for loop used to iterate n number of times.this number is fixed.

syntax:

               for  variable in  starting_value..final_value

               loop

                          block of statements

               end loop;

for ex:  Display first 10 numbers using LOOP Statement.

begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end; 

In this case i is local variable automatically created.

initial Value is 1 final value is 10,  auto increment by 1.

output:

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

PL/SQL procedure successfully completed.

for ex:  Display first 10 numbers using FOR LOOP REVERSE Statement.

begin
dbms_output.put_line('Using for loop statement in PL/SQL');
for i in reverse 1..10
loop
dbms_output.put_line(i);
end loop;
end;

OUTPUT:
SQL> /
Using for loop statement in PL/SQL
10
9
8
7
6
5
4
3
2
1

PL/SQL procedure successfully completed.

Note: i value cannot be changed inside for loop.

Implicit Cursors using for loop

Implicit cursors are automatically opened and closed by 'for loop statement.'

declare

cursor c1 is select * from emp;

begin

dbms_output.put_line('Implicit cursors using for loop');

 for i in reverse c1

loop

dbms_output.put_line(i.empno||' '||i.ename||' '||i.sal||' '||i.deptno);

end loop;

end;

OUTPUT:

SQL> /
Implicit cursors using for loop
9003 peter 1200 10
7369 SMITH 800 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 JONES 2975 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7782 CLARK 2450 10
7788 SCOTT 3000 20
7839 KING 5000 10
7844 TURNER 1500 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20
7934 MILLER 1300 10

PL/SQL procedure successfully completed.

 

Tags: ORACLE FOR LOOP STATEMENT,ORACLE FOR LOOP STATEMENT syntax,ORACLE FOR LOOP STATEMENT reverse, implicit cursors using oracle for loop.