Iterative Statements in PL/SQL

In the last post on PL/SQL, we had seen Conditional Statements and how they can be used. Today we will be learning about Iterative Statements in PL/SQL.

As we all know the types of Iterative statements that are there in a programming language. PL/SQL also has the same iterative statements and the functioning is also the same, but the only thing different is their syntax. 

So lets not waste any time and move on to the topic.

LOOP and EXIT statement 

LOOP is one the iterative controls and it helps us to execute a sequence of statements multiple times. There are three kinds of LOOP statements :

* LOOP

* WHILE-LOOP

* FOR-LOOP

LOOP 

LOOP is the simplest of them all. It encloses a sequence of statements between the keywords LOOP and END LOOP which are executed.

SYNTAX

LOOP

equence_of_statements


END LOOP;

The EXIT Statement

An EXIT statement is used to complete the loop if no further processing is required. A user can place one or more EXIT statement inside the LOOP depending on the requirement, but the EXIT statement cannot be placed outside the LOOP.

The EXIT statement also has two forms :

* EXIT

* EXIT-WHEN

The EXIT statement will force the LOOP to complete unconditionally whereas the EXIT-WHEN will force the LOOP to complete only when the condition is met.

Example to use LOOP : 

LOOP

counter:=counter+1;

dbms_output.put_line(counter);

IF counter > 20 THEN

EXIT;

END IF;


END LOOP;

EXIT-WHEN Statement 

As we can understand from the name itself, that EXIT-WHEN will allow the LOOP to complete conditionally.

When the EXIT statement is encountered then the WHEN statement is evaluated and thus the condition is checked.

SYNTAX

EXIT [When condition]

EXAMPLE to use EXIT-WHEN :

LOOP

counter:=counter+1;

dbms_output.put_line(counter);

EXIT When counter > 20;


END LOOP;

WHILE LOOP

As we know that the WHILE-LOOP associates a condition with a sequence of statements enclosed by the keywords LOOP and END LOOP.

SYNTAX for WHILE-LOOP

WHILE condition LOOP

< statements >

END LOOP;

Example of WHILE-LOOP

WHILE counter <=50 LOOP

sum:=sum+counter

dbms_output.put_line(sum);

END LOOP;

FOR LOOP

FOR LOOP iterative over a specified range of integers. 

SYNTAX of FOR LOOP

FOR <variable> IN [REVERSE]

<lower>...<upper>

LOOP

<statements>

END LOOP;

The index variable <variable> has the following properties :

* Its datatype is NUMBER and need not be declared.

* Its scope is only within the FOR LOOP

* The index variable can be referenced inside the FOR LOOP, but can't be modified.

The REVERSE keyword is used should anyone want to use the iteration in the reverse order that is from <upper> to <lower>.

Example of FOR LOOP

FOR counter IN 1..20

LOOP

INSERT INTO Test VALUES(counter);

END LOOP;

FOR counter IN REVERSE 1..20

LOOP

INSERT INTO Test VALUES(counter);

END LOOP;