Oracle Loop Statements

Oracle PL/SQL provides loop statements that allow you to execute a block of code repeatedly. Loop statements are useful when you need to perform a specific task multiple times or iterate through a collection of data. There are several types of loop statements in PL/SQL, including the basic loop, while loop, and for loop.

Basic Loop

The basic loop is the simplest type of loop statement in PL/SQL. It repeats a block of code indefinitely until an exit condition is met. The loop continues until explicitly terminated using the EXIT statement. The basic syntax of the basic loop is as follows:

LOOP
   -- Code block to be executed
   -- Exit condition
   EXIT WHEN ;
END LOOP;

Here, represents the expression that determines whether the loop should be terminated.

While Loop

The while loop allows you to repeatedly execute a block of code as long as a specific condition is true. The condition is evaluated before each iteration, and if it becomes false, the loop terminates. The syntax of the while loop is as follows:

WHILE  LOOP
   -- Code block to be executed
END LOOP;

In this case, represents the expression that determines whether the loop should continue or terminate.

For Loop

The for loop is useful when you want to iterate a specific number of times. It provides a convenient way to loop through a range of values or iterate over a collection. The syntax of the for loop is as follows:

FOR  IN [REVERSE] ..[UPPER_BOUND] LOOP
   -- Code block to be executed
END LOOP;

In this syntax, is a variable that holds the current iteration value. and specify the range of values for the loop. The REVERSE keyword is optional and allows you to iterate in reverse order.

Within loop statements, you can use control statements like CONTINUE to skip the current iteration and move to the next one, or EXIT to terminate the loop prematurely. These statements provide flexibility in controlling the flow of the loop.

Basic Loop example

declare
  v_name varchar2(255);
  j number;
  i number:=0;
begin
select count(*) into j from CUSTOMERS;
DBMS_OUTPUT.put_line('Total customers: '||j);
  loop
    i:=i+1;
    select name into v_name from CUSTOMERS where id=i;
    DBMS_OUTPUT.put_line('Customer name: '||v_name);
    if i=j then
      exit;
    end if;
  end loop;
end;

While Loop example

declare
  v_name varchar2(255);
  j number:=3;
  i number:=0;
  v_condition boolean:=TRUE; 
begin
  while v_condition 
  loop
    i:=i+1;
    select name into v_name from CUSTOMERS where id=i;
    DBMS_OUTPUT.put_line('Customer name: '||v_name);
    if i=j then
      exit;
    end if;    
  end loop;
end;

For Loop example

declare
  v_name varchar2(255);
  j number:=2;
  i number:=0;
begin
  for i in 1.. j 
  loop
    select name into v_name from CUSTOMERS where id=i;
    DBMS_OUTPUT.put_line('Customer name: '||v_name);
  end loop;
end;

Cursor FOR Loop example

declare
begin
  for c1 in (select * from CUSTOMERS) 
  loop
    DBMS_OUTPUT.put_line('Customer name: '||c1.name);   
  end loop;
end;