Oracle Loop Statements
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;