In a program, if a sequence of instructions are to be executed repeatedly until a specific condition is met, then the program is said to be in a loop. The ability to loop through the procedural logic or a body of code is very important in any programming language. It is very useful when a user wants to perform numerical computations in a PLSQL Loop and execute procedural logic involving SQL statements using loop counters that are fetched from the database tables. PLSQL Loop are classified into various types, such as A PLSQL Loop statement allows a statement or a group of statements to execute different number of times in the loop statement.

A simple PLSQL Loop has a simple syntax that begins with the loop keyword and closes with the end loop keyword followed by semicolon. The statement should be written between these two keywords and should be executed for each iteration. Simple loop contains two variables - I_counter and L_sum. I_counter acts as a loop counter and is initialized to a value, whereas, L_sum is used to hold the total value from the counter and is initialized to a value. The loop begins with the loop keyword. Inside the loop, I_sum and I_counter are added and assigns the result to I_sum. By increasing the loop counter, one can exit from the loop explicitly. If the value of the loop counter is greater than the assigned value, then the PLSQL Loop gets exited from the function. Simple loop block contains a sequence of statements between the start point and end point of the loop.

The below example describes simple loop. [c]SQL> DECLARE 2 x number :=10; 3 BEGIN 4 LOOP 5 dbms_output.put_line(x); 6 x:=x+10; 7 IF x>50 THEN 8 EXIT; 9 END IF; 10 END LOOP; 11 --after exit,control resume here 12 dbms_output.put_line('After Exit x is:'||x); 13 END; 14 / After Exit the value of x=60; PL/SQL procedure successfully completed. [/c] In the above example, simple loop gives the values that are greater than the given value. After the loop gets executed, the value of 'x' will be displayed as 60.

FOR loop is a commonly used looping mechanism that allows to compose loop statements to get executed for a explicit fraction of moments. The syntax is: optional label name for the loop followed by for keyword and loop_counter IN. Reverse, an optional keyword, in the for loop, when specified, enables the loop counter to count in reverse. And, lower_bound and upper_bound have to be specified in the loop_counter separated by two dots. LOOP keyword is used to start the loop followed by a group of statements and ends with the end loop keyword. The loop_counter is declared implicitly as PLS_INTEGER by oracle.  And, oracle also automatically increments and decrements the value after every iteration. Once the last value is reached, the oracle automatically gets exited from the loop.

The below example explains For loop. [c]SQL> DECLARE 2 a number(2); 3 BEGIN 4 FOR a in 10..20 LOOP 5 dbms_output.put_line('value of a:'||a); 6 END LOOP; 7 END; 8 / value of a:10 value of a:11 value of a:12 value of a:13 value of a:14 value of a:15 value of a:16 value of a:17 value of a:18 value of a:19 value of a:20 PL/SQL procedure successfully completed.[/c] The above example describes the For loop statements, and these looping For loop statement will print the values between 10 and 20 as per the code given in the above example.

The while loop is used when a number of iterations are not known, and the number of iterations are dependent on the evaluation of a condition. It starts with the while keyword followed by the condition to be satisfied. If the case is said to be true, the loop starts with a loop keyword. The statements get executed inside the loop and the loop ends with the end loop statement. After compiling the statement and the loop, the condition will be evaluated again to see if the loop should continue or not. If the condition is not satisfied, the loop terminates. A variable is set in the loop and is also a part of evaluation. If the condition is false, the loop does not get executed.

The below example explains the while loop. [c]SQL> DECLARE 2 a number(2):=10; 3 BEGIN 4 WHILE a<20 LOOP 5 dbms_output.put_line('value of a:'||a); 6 a:=a+1; 7 END LOOP; 8 END; 9 / value of a:10 value of a:11 value of a:12 value of a:13 value of a:14 value of a:15 value of a:16 value of a:17 value of a:18 value of a:19 PL/SQL procedure successfully completed.[/c] The above while loop example prints values less than 20. i.e, it executes the while loop statement from the values 10 to 19.

Placing one loop inside the other loop is allowed in PL/SQL. Nesting of loop is used when the outer loop that fetches records from the customer table collects each customer record and performs some operations inside the loop.

The below example illustrates nested loop. [c]SQL> DECLARE 2 i number(3); 3 j number(3); 4 BEGIN 5 i:=2; 6 LOOP 7 j:=2; 8 LOOP 9 exit WHEN((mod(i,j)=0) or(j=i)); 10 j:=j+1; 11 END LOOP; 12 IF(j=i)THEN 13 dbms_output.put_line(i||'is prime'); 14 END IF; 15 i:=i+1; 16 exit WHEN I=50; 17 END LOOP; 18 END; 19 / 2 is prime 3 is prime 5 is prime 7 is prime 11 is prime 13 is prime 17 is prime 19 is prime 23 is prime 29 is prime 31 is prime 37 is prime 41 is prime 43 is prime 47 is prime PL/SQL procedure successfully completed.[/c] In the above nesting loop, it prints the first 50 prime numbers. i.e, from 0 to 50.

The WHILE loop checks the conditional statement at the beginning of the loop and if the case is false, the code in the loop doesn't get executed. REPEAT-UNTIL loop is similar to while loop except that the while loop tests the condition at the beginning of the iteration and repeat until tests the condition at the end of the iteration. REPEAT-UNTIL executes when the condition is false and until the condition becomes true.  REPEAT-UNTIL is not specifically utilized by PL/SQL and in any case it is anything but difficult to actualize it using the perpetual loop.

The below example illustrates the repeat until  loop statement. [c]SQL> declare 2 n_num number:=1; 3 begin 4 loop 5 dbms_output.put(n_num||','); 6 n_num :=n_num +1; 7 exit when n_num>5; 8 end loop; 9 dbms_output.put_line('Final:'||n_num); 10 end; 11 / 1,2,3,4,5,Final:6 PL/SQL procedure successfully completed.[/c] In the above example, the repeat until loop will print the values from 1 to 6 based on the code entered in the loop.