PL/SQL - SPLessons

PL/SQL Loop

Chapter 13

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Loop

PL/SQL Loop

shape Description

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.

Simple PLSQL Loop

shape Description

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.

shape Syntax

DECLARE
I_counter NUMBER:=0;
I_counter NUMBER:=0;
BEGIN
LOOP
I_sum:=I_sum+I_counter;
I_counter:=I_counter+1;
EXIT WHEN I_counter>3;
END LOOP;
END;

I_counter NUMBER => Is a variable

Loop => Sequence of instructions

shape Examples

The below example describes simple loop.

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.

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

shape Description

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.

shape Syntax

[label]FOR loop_counter IN [REVERSE]lower_bound..upper_bound loop
group_of_statements
END LOOP[label];

Reverse => Executes the loop from the bottom

End loop => End of the looping statement.

shape Examples

The below example explains For loop.

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.

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.

While loop

shape Description

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.

shape Syntax

WHILE<case>LOOP
group_of_proclamations
END LOOP;

Condition =>The condition to execute the while loop

Group of statements => Variables or statements

shape Examples

The below example explains the while loop.

SQL&gt; DECLARE
  2     a number(2):=10;
  3  BEGIN
  4   WHILE a&lt;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.

The above while loop example prints values less than 20. i.e, it executes the while loop statement from the values 10 to 19.

Nesting of loop

shape Description

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.

shape Syntax

LOOP
Arrangement of proclamation
LOOP
Arrangement of proclamation
END LOOP;
END LOOP;

shape Examples

The below example illustrates nested loop.

SQL&gt; 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.

In the above nesting loop, it prints the first 50 prime numbers. i.e, from 0 to 50.

Repeat until loop

shape Description

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.

shape Syntax

LOOP
{…Proclamation…}
EXIT [WHEN boolean_case];
END LOOP;
Proclamation => Code to execute the loop.
Boolean_case => Condition that terminate the loop.

shape Examples

The below example illustrates the repeat until  loop statement.

SQL&gt; 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&gt;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.

In the above example, the repeat until loop will print the values from 1 to 6 based on the code entered in the loop.

Exit statement

shape Description

The EXIT statement exits from the loop and forwards the lead control to the end of the loop. There are 2 types of EXIT statements, such as

Conditional statement

When the  statement EXIT is executed inside the loop, it gets terminated immediately and the program control resumes at the following statement of the loop.

Unconditional statement

While using the nested loops, the statement EXIT will halt the execution of the deepest loop and begins executing the following line of code.

shape Syntax

EXIT [WHEN boolean_condition];
Boolean_condition => Condition to terminate the loop

shape Examples

The below example illustrates the Exit statement.

SQL&gt; declare
  2  a number(2):=10;
  3  begin
  4    --while loop execution
  5  WHILE a&lt;20 LOOP 6 dbms_output.put_line('value of a:'||a); 7 a:=a+1; 8 IF a&gt;15 then
  9    --terminate the loop using the exit statement
 10  EXIT;
 11  END IF;
 12  END LOOP;
 13  END;
 14  /

value of a:10
value of a:11
value of a:12
value of a:13
value of a:14
value of a:15
PL/SQL procedure successfully completed.

In the above example, the Exit statement will display the values that are encapsulated based on the given code i.e, it will print the values from 10 and less than or equal to 15.

Summary

shape Key Points

  • PLSQL Loop – Sequence of instructions that are continuously executed.
  • Simple loop – Arranging the statements between the beginning and ending of the loop.
  • For loop –  Control structure repetition is executed a number of times.
  • While loop – Repeatedly executes statements to be true.
  • Nested loop – Placement of one loop inside the other loop.
  • Repeat Until loop – The condition is tested at the end of the iteration and the process is repeated when the condition is false and until the condition becomes true .
  • Exit statement – Exits from the PLSQL Loop and transfers the control to the end of the loop.