Login

Username:

Password: 

Join us Now |  Forgot Password? | Forgot UserName?

PL/SQL

Learn step by step



Iterative Statements in PL/SQL

Iterative control Statements are used when we want to repeat the execution of one or more statements for specified number of times.
There are three types of loops in PL/SQL:

  • Simple Loop
  • While Loop
  • For Loop

Basic Loop

The BASIC loop repeats until a condition is met. Because the condition is tested at the end of the loop, the BASIC loop will always execute at least once.

LOOP

[Sequence of statements]

EXIT [condition]

END LOOP;
declare
num number := 1;
begin
loop
dbms_output.put_line ('The Number is '||num);
num := num +1;
if num > 10
Then
dbms_output.put_line('Existing the Basic loop.');
exit;
end if;
end loop;
end;
/

Output

The Number is 1
The Number is 2
The Number is 3
The Number is 4
The Number is 5
The Number is 6
The Number is 7
The Number is 8
The Number is 9
The Number is 10
Exiting the Basic loop.
While Loop

A WHILE LOOP statement in PL/SQL programming language repeatedly executes a target statement as long as a given condition is true

WHILE condition LOOP
  sequence_of_statements
END LOOP;
DECLARE
a number(2) := 10;
BEGIN
WHILE a < 16 LOOP
dbms_output.put_line('value of a: ' || a);
a := a + 1;
END LOOP;
END;
/

Output

  • value of a: 10
  • value of a: 11
  • value of a: 12
  • value of a: 13
  • value of a: 14
  • value of a: 15
For Loop

FOR LOOP allows you to execute code repeatedly for a fixed number of times.

FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
{...statements...}
END LOOP;
-- available online in file 'sample1'
DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN -- i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;

Output

SELECT * FROM temp ORDER BY col1;
NUM_COL1 NUM_COL2  CHAR_COL
-------- --------  ---------
       1      100  i is odd
       2      200  i is even
       3      300  i is odd
       4      400  i is even
       5      500  i is odd
       6      600  i is even
       7      700  i is odd
       8      800  i is even
       9      900  i is odd
      10     1000  i is even


Related Videos