Comments

Monday, August 20, 2012

Iterative Statements in PL/SQL

Posted by on Monday, August 20, 2012 Read our previous post
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;

© 2010 Code 2 Learn