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 is the simplest of them all. It encloses a sequence of statements between the keywords LOOP and END LOOP which are executed.
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 :
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 :
LOOPcounter:=counter+1;dbms_output.put_line(counter);IF counter > 20 THENEXIT;END IF;END LOOP;
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.
EXIT [When condition]
EXAMPLE to use EXIT-WHEN :
LOOPcounter:=counter+1;dbms_output.put_line(counter);EXIT When counter > 20;END 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 LOOPsum:=sum+counterdbms_output.put_line(sum);END 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..20LOOPINSERT INTO Test VALUES(counter);END LOOP;
FOR counter IN REVERSE 1..20LOOPINSERT INTO Test VALUES(counter);END LOOP;