Introduction To Loops & Simple Loops In PL/SQL

With the previous tutorial we finished our Conditional Control Statement Series and learnt the concept of different types of IF conditions in Oracle PL/SQL. That was a very important topic from the examination’s perspective. Another topic which is again very important for your exam and could help you in getting good percentage is Iterative Statements.

Iterative statements famously known as Loops in Programming language. It executes block of statements or a part of a program several times.

Types of Loops in Oracle PL/SQL

There are 4 types of Loops in Oracle PL/SQL

1. Simple Loop
2. While Loop
3. Numeric For Loop and
4. Cursor For loop

In this series we will focus on the first 3 types of loops. The last type which is “Cursor For Loop” will be discussed with Cursor in the future Tutorial. Having said that let’s start today’s tutorial with Simple Loop.

Simple Loop

Simple loop is the most basic loop in Oracle PL/SQL

Syntax

LOOP
Statement 1;
Statement 2;
…
Statement 3;
END LOOP;

Here keyword LOOP marks the beginning and phrase END LOOP marks the ending of the loop. In between we have a sequence of executable statements.

As you can see in this syntax that unlike conventional loops here we do not have update statements or for that matter exist conditions which will terminate the loop. May be that is why we call this a simple loop.

Example 1

DECLARE
  v_counter   NUMBER :=0;
  v_result  NUMBER;
BEGIN
  LOOP
  v_counter := v_counter+1;
  v_result := 19*v_counter;
  DBMS_OUTPUT.PUT_LINE(’19’||’ x ‘||v_counter||’ = ‘|| v_result);
  END LOOP;
END;

Here in this example as you can see we do not have any exit statement to terminate the loop. This means that if we execute this program then the execution will keep on printing till we halt it manually.

In this case Oracle PL/SQL gives us two clauses to terminate the loop

1. Exit
2. Exit When

Exit clause will terminate the loop when Exit condition is evaluated to be true. The exit condition is evaluated with the help of Simple IF THEN condition which we discussed in PL/SQL Tutorial 8. So let’s see how you can use this exit statement in this example.

Example 2 Terminate Loop with EXIT

DECLARE
  v_counter   NUMBER :=0;
  v_result  NUMBER;
BEGIN
  LOOP
  v_counter := v_counter+1;
  v_result := 19*v_counter;
  DBMS_OUTPUT.PUT_LINE(’19’||’ x ‘||v_counter||’ = ‘|| v_result);
IF v_counter >=10 THEN
  EXIT;
END IF;
  END LOOP;
END;

You simply have to add this IF THEN block either right above the phrase END LOOP or immediately below the keyword loop. What this IF THEN block will do? This block will keep an eye on your counter and will tell the control to exit the loop when counter either becomes greater than or equal to 10. Which means loop will execute for 10 times.

Example 3 Terminate the Loop with EXIT WHEN Clause

Second way of terminating the loop is by using EXIT WHEN clause. Using this clause you can replace this whole IF THEN block with a simple single statement.

DECLARE
  v_counter   NUMBER :=0;
  v_result  NUMBER;
BEGIN
  LOOP
  v_counter := v_counter+1;
  v_result := 19*v_counter;
  DBMS_OUTPUT.PUT_LINE(’19’||’ x ‘||v_counter||’ = ‘|| v_result);
EXIT WHEN i_counter>=10;
  END LOOP;
END;

That’s all about Simple Loop. I hope you found this article helpful. Kindly please share it with your friends and help me reach out to more people. Thanks & have a great day.