Oracle Loop Statements

A LOOP statement executes a sequence of statements multiple times.

The Oracle PL/SQL LOOP statements are:

Basic loop – A loop that executes an unlimited number of times. An EXIT, GOTO, RAISE statement, or a raised exception ends the loop.

WHILE loop – The While Loop is executed if the expression is TRUE.

FOR loop – Numeric FOR Loop loops iterate over a specified range of integers.

Cursor FOR loop – The Cursor FOR Loop issues a SQL query and loops through the rows in the result set.

Basic Loop

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

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

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

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