While loop Oracle

While loop is an another looping structure. It is also called conditional loop. It evaluates the condition before each iteration executes. While loop is effective when how number of iterations are not known prior to the execution.

Syntax:-

while condition
loop
—- your code here
end loop;

Examples :-

1) Print all odd numbers less than 10

SQL> set serveroutput on
SQL> declare
2 n integer;
3 begin
4 n := 1;
5 while n <= 10 loop
6 dbms_output.put_line(n);
7 n := n + 2;
8 end loop;
9 end;
10 /
1
3
5
7
9

PL/SQL procedure successfully completed.

Note that if we omit the condition n := n + 2 then while loop will fall in infinite loop.

So always be cautious when constructing a while loop.

Read also – For loop , goto statement

Advertisements

For loop Oracle

FOR LOOP is the most used loop structure in Oracle.  FOR LOOP executes specified number of times which defined in the definition. So generally no need of any specific exit command for FOR LOOPs. FOR LOOP is suitable when the number of iterations are known.

Examples:-

1)  Printing 1 to 10 in ascending order

SQL> set serveroutput on
SQL> begin
  2  for i in 1..10 loop
  3    dbms_output.put_line(‘value of i = ‘ || i);
  4  end loop;
  5  end;
  6  /
value of i = 1
value of i = 2
value of i = 3
value of i = 4
value of i = 5
value of i = 6
value of i = 7
value of i = 8
value of i = 9
value of i = 10

PL/SQL procedure successfully completed.

2)  Printing 1 to 10 in descending order – Using reverse keyword

SQL> begin
  2  for i in reverse 1..10  loop
  3    dbms_output.put_line(‘value of i = ‘ || i);
  4  end loop;
  5  end;
  6  /
value of i = 10
value of i = 9
value of i = 8
value of i = 7
value of i = 6
value of i = 5
value of i = 4
value of i = 3
value of i = 2
value of i = 1

PL/SQL procedure successfully completed.

Note :- In the above example variable ‘i’ is called index. Index variable cannot be modified programmatically ( or cannot be used as assignment target). see below

SQL> begin
2  for i in 1..10  loop
3    dbms_output.put_line(‘value of i = ‘ || i);
4    i := 5;
5  end loop;
6  end;
7  /
i := 5;
*
ERROR at line 4:
ORA-06550: line 4, column 3:
PLS-00363: expression ‘I’ cannot be used as an assignment target
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

Read also – While loop , goto statement