Oracle PL/SQL - PL SQL Statement Simple loops

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. Basic LOOP Statement

Introduction

The syntax of a typical simple loop looks like the following: 

loop  
      ...<<set of statements>>... 
     exit when <<conditionA>>; 
      ...<<set of statements>>... 
end loop;

or 

loop  
     ...<<set of statements>>... 
     if <<conditionB>> then 
          ...<<set of statements>>... 
          exit; 
     end if; 
      ...<<set of statements>>... 
end loop;

The set of commands enclosed in the LOOP/END LOOP is repeated until the EXIT command is fired.

The preceding example shows both ways for exiting a loop: EXIT WHEN and EXIT.

When using the EXIT command, as soon as Oracle encounters it, the loop is immediately terminated.

You will place the EXIT command inside an IF...THEN statement.

In EXIT WHEN statement, you place the condition along with the EXIT command.

Demo

SQL>
SQL> declare-- from  w ww.  j ava 2  s.c  o m
  2        v_start_dt date:=to_date('01-01-2016','MM-DD-YYYY');
  3        v_end_dt   date:=to_date('02-01-2016','MON-DD-YYYY');
  4  begin
  5        v_start_dt:=v_start_dt + (7-to_number(TO_CHAR(v_start_dt,'d')));
  6        -- detect first Saturday
  7        loop
  8             DBMS_OUTPUT.put_line(to_char(v_start_dt,'MM-DD-YYYY'));
  9             v_start_dt := v_start_dt + 7;
 10             exit when v_start_dt >= v_end_dt;
 11        end loop;
 12  end;
 13  /
SQL>

Related Topic