www.oradev.com
  Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
Pl/sql loop

Database/Sql

Oracle Regular Expressions
Timestamp
SQL Date format
String concatenation
Loop in pl/sql
SQL IN-clause
Regular Expressions Examples
Flashback query
Grant/revoke privileges
Sequence
Rename tables, columns
Insert into Oracle
Database name
Table with sequenced numbers
Oracle connect by
Add columns to table


  OraDev.com

Oracle pl/sql loop structure

A loop in pl/sql is a repeating control structure that repeats a piece of code. There are different kinds of loops:

for loop

A for loop repeats a code block a specified number of times. The statement begins with ‘for’..’loop’ and ends with ‘end loop .

Syntax:
for counter in [reverse] lower_bound..higher_bound loop
   Code…;
End loop;

With lower_bound and higher_bound integer values.

Normal loop in Oracle

A normal loop starts with ‘loop’ and ends with ‘end loop’. The loop will be stopped when the EXIT statement is reached. You can also use the RETURN statement to complete pl/sql block before its normal end is reached.

Syntax:
loop
    code..;
 
    EXIT when …;
end loop;
 
loop
    code..;
    if (condition) then
      exit;
    end if;
end loop;
<<outer>>
loop
   ..
   loop
      …
      EXIT outer WHEN condition;
   end loop;
   ..
end loop outer;

while loop

The while loop statement repeats a code block until a condition is reached. The statement starts with ‘while [condition] loop’ and ends with ‘end loop’.

Syntax:
while (condition) loop
  code..;
end loop;