Oracle Control Structures
Version 11.2.0.3

IF Statements
Basic IF Statement IF <condition> THEN
  <code here>
END IF;
set serveroutput on

DECLARE
 x NUMBER(3) := 9;
BEGIN
  IF x < 10 THEN
    dbms_output.put_line('X is less than 10');
  END IF;
END;
/
IF - ELSE Statement IF <condition> THEN
  <code here>
ELSE
  <code here>
END IF;
set serveroutput on

DECLARE
 x NUMBER(3) := 10;
BEGIN
  IF x < 10 THEN
    dbms_output.put_line('X is less than 10');
  ELSE
    dbms_output.put_line('X is not less than 10');
  END IF;
END;
/
IF - ELSIF - ELSE Statement IF <condition> THEN
  <code here>
ELSIF <condition> THEN
  <code here>
ELSIF <condition> THEN
  <code here>
ELSE
  <code here>
END IF;
set serveroutput on

DECLARE
 x NUMBER(3) := 47;
BEGIN
  IF x < 10 THEN
    dbms_output.put_line('X is less than 10');
  ELSIF x = 10 THEN
    dbms_output.put_line('X is equal to 10');
  ELSIF x < 100 THEN
    dbms_output.put_line('X is between 11 and 99');
  ELSE
    dbms_output.put_line('X is greater than 99');
  END IF;
END;
/
 
Basic Loop
Table for LOOP and CURSOR LOOP Demos CREATE TABLE loop_test (test_col VARCHAR2(5));
The BASIC loop just uses LOOP and END LOOP and depends on the programmer to create an explicit exit from the loop. LOOP
  <code here>
END LOOP;
DECLARE
 i PLS_INTEGER := 1;
BEGIN
  LOOP
    i := i + 1;
    INSERT INTO loop_test VALUES (i);

    IF i > 99 THEN
      EXIT;
    END IF;
  END LOOP;
  COMMIT;
END;
/

SELECT * FROM loop_test;
 
While Loop
The WHILE loop loops until a condition, explicitly stated at the beginning of the loop is met WHILE <some_condition_is_met>
LOOP
  <code here>
END LOOP;
DECLARE
 i PLS_INTEGER := 999;
BEGIN
  WHILE i < 1100 LOOP
    i := i + 1;
    INSERT INTO loop_test VALUES (i);
  END LOOP;
  COMMIT;
END;
/

SELECT * FROM loop_test;
 
FOR Loop
The FOR loop loops for a specific number of times defined by FOR statement

The variable used as the counter for the FOR loop does not need to be declared in the declaration section of the code

This is an example of an INCREMENTING FOR LOOP ... the counter increases in value		 FOR <variable> IN <start_number> .. <end_number> LOOP
  <code here>
END LOOP;
BEGIN
  FOR i IN 2000 .. 2100 LOOP
    INSERT INTO loop_test VALUES (i);
  END LOOP;
  COMMIT;
END;
/

SELECT * FROM loop_test;
Decrementing FOR LOOP ... the counter decreases in value FOR <variable> IN REVERSE <start_number> .. <end_number> LOOP
  <code here>
END LOOP;
BEGIN
  FOR i IN REVERSE 3000 .. 3100 LOOP
    INSERT INTO loop_test VALUES (i);
  END LOOP;
  COMMIT;
END;
/

SELECT * FROM loop_test;
 
Continue Statement
Continues processing at the top of the loop LOOP
  <code here>
  CONTINUE;
  <code here>
END LOOP;
set serveroutput on

CREATE OR REPLACE PROCEDURE cont_test (val IN POSITIVEN) AUTHID DEFINER IS
BEGIN
  FOR i IN 1 .. 3 LOOP
    dbms_output.put_line('i=' || TO_CHAR(i));

    IF val = 2 THEN
      CONTINUE;
    END IF;

    dbms_output.put_line('Did not jump to the top of the loop');
  END LOOP;
END cont_test;
/

exec cont_test(3);
exec cont_test(2);
Continues processing at the top of the loop when the specified condition is met LOOP
  <code here>
  CONTINUE WHEN <code here that determines whether the condition has been met>;
END LOOP;
set serveroutput on

CREATE OR REPLACE PROCEDURE contwhen_test (val IN POSITIVEN) AUTHID CURRENT_USER IS
BEGIN
  FOR i IN 1 .. 3 LOOP
    dbms_output.put_line('i=' || TO_CHAR(i));

    CONTINUE WHEN (i+1) = val;

    dbms_output.put_line('Did not jump to the top of the loop');
  END LOOP;
END contwhen_test;
/

exec contwhen_test(1);
exec contwhen_test(2);
exec contwhen_test(3);
 
Cursor Loop
Explicitly declared cursor and record CREATE OR REPLACE PROCEDURE <procedure_name> IS
 CURSOR <cursor_name> IS
 <SQL statement>

 <record_name> <cursor_name>%ROWTYPE;
BEGIN
  OPEN <cursor_name>
  LOOP
    FETCH <cursor_name> INTO <record_name>;
    EXIT WHEN <cursor_name>%NOTFOUND;

    <other code>
  END LOOP;
  CLOSE <cursor_name>;
END <procedure_name>;
/
TRUNCATE TABLE loop_test;

DECLARE
 CURSOR ao_cur IS
 SELECT SUBSTR(object_name,1,5) FIRSTFIVE
 FROM all_objects;

 ao_rec ao_cur%ROWTYPE;
BEGIN
  OPEN ao_cur;
  LOOP
    FETCH ao_cur INTO ao_rec;
    EXIT WHEN ao_cur%NOTFOUND;

    INSERT INTO loop_test VALUES (ao_rec.firstfive);
  END LOOP;
  COMMIT;
  CLOSE ao_cur;
END;
/

SELECT COUNT(*) FROM loop_test;
Fetch Demo -- demo courtesy of Andy Hassall <andy@andyh.co.uk>

CREATE TABLE t1 (tcol NUMBER);

CREATE TABLE t2 (c NUMBER);

BEGIN
  FOR i IN 1..500 LOOP
    INSERT INTO t1 VALUES (i);
  END LOOP;
END;
/

SELECT COUNT(*) FROM t1;

COMMIT;

CREATE OR REPLACE FUNCTION p (c_in NUMBER) RETURN NUMBER AUTHID DEFINER IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  -- Every time this is called, write a row to table t2
  INSERT INTO t2 VALUES (c_in);
  COMMIT;
  RETURN c_in;
END p;
/

DECLARE
  BEGIN
  /* Open up a cursor for loop, also selecting
  * the "p" function which will write rows to
  * t2 for every row fetched from t1. */

  FOR crec IN (SELECT tcol, p(tcol) FROM t1) LOOP
    -- Break out of the loop immediately
    EXIT;
  END LOOP;
END;
/

SELECT COUNT(*) FROM t2;
 
Cursor FOR Loop
Explicitly declared cursor and implicit record declared by the FOR loop CREATE OR REPLACE PROCEDURE <procedure_name> IS
 CURSOR <cursor_name> IS
 <SQL statement>
BEGIN
  FOR <record_name> IN <cursor_name> LOOP
    <other code>
  END LOOP;
END <procedure_name>;
/
TRUNCATE TABLE loop_test;

DECLARE
 CURSOR ao_cur IS
 SELECT SUBSTR(object_name,1,5) FIRSTFIVE
 FROM all_objects;
BEGIN
  FOR ao_rec IN ao_cur LOOP
    INSERT INTO loop_test VALUES (ao_rec.firstfive);
  END LOOP;
  COMMIT;
END;
/

SELECT COUNT(*) FROM loop_test;
A FOR loop with an intrinsic cursor. A record is implicitly declared by the variable in the FOR statement. BEGIN
  FOR <record_name> IN <SQL_statement>
  LOOP
    <other code>
  END LOOP;
END <procedure_name>;
/
TRUNCATE TABLE loop_test;

BEGIN
  FOR ao_rec IN (SELECT SUBSTR(object_name,1,5) FIRSTFIVE FROM all_objects) LOOP
    INSERT INTO loop_test VALUES (ao_rec.firstfive);
  END LOOP;
  COMMIT;
END;
/

SELECT COUNT(*) FROM loop_test;
Cursor Loop With WHERE CURRENT OF Clause CREATE OR REPLACE PROCEDURE <procedure_name> IS
 <cursor definition>
BEGIN
  FOR <record_name> IN (<cursor_name>) LOOP
    <other code>
    UPDATE <table_name>
    SET <column_name> = <value>
    WHERE CURRENT OF <cursor_name>
  END LOOP;
END <procedure_name>;
/
CREATE TABLE test (
pid  NUMBER(3),
cash NUMBER(10,2));

INSERT INTO test VALUES (100, 10000.73);
INSERT INTO test VALUES (200  25000.26);
INSERT INTO test VALUES (300, 30000.11);
INSERT INTO test VALUES (400, 45000.99);
INSERT INTO test VALUES (500, 50000.08);
COMMIT;

CREATE OR REPLACE PROCEDURE wco AUTHID CURRENT_USER IS
 CURSOR x_cur IS
 SELECT pid, cash
 FROM test
 WHERE cash < 35000
 FOR UPDATE;
BEGIN
  FOR x_rec IN x_cur LOOP
    UPDATE test
    SET cash = FLOOR(cash)
    WHERE CURRENT OF x_cur;
  END LOOP;
  COMMIT;
END wco;
/

exec wco;

SELECT * FROM test;
 
Nested Cursor Loops
Demo Tables CREATE TABLE airplanes (
program_id  VARCHAR2(3),
line_number NUMBER(10),
customer_id VARCHAR2(4),
order_date  DATE,
delivered_date DATE)
PCTFREE 0;

CREATE INDEX programid_idx
ON airplanes (program_id)
PCTFREE 0;

CREATE TABLE parts (
program_id VARCHAR2(3),
line_type VARCHAR2(4),
part_type VARCHAR2(10),
quantity NUMBER(3));

CREATE TABLE ap_parts AS
SELECT a.customer_id, p.part_type, p.quantity
FROM airplanes a, parts p
WHERE a.program_id = p.program_id
AND 1=2;
Load the Airplanes demo table DECLARE
 progid  airplanes.program_id%TYPE;
 lineno  airplanes.line_number%TYPE;
 custid  airplanes.customer_id%TYPE := 'AAL';
 orddate airplanes.order_date%TYPE;
 deldate airplanes.delivered_date%TYPE;
BEGIN
  FOR i IN 1 .. 5 LOOP
    SELECT DECODE(i, 1, '737', 2, '747', 3, '757', 4, '767', 5, '777')
    INTO progid
    FROM dual;

    FOR lineno IN 1..250 LOOP
      SELECT DECODE(custid, 'AAL','DAL','DAL','SAL','SAL','ILC',
     'ILC','SWA', 'SWA','NWO','NWO','AAL')
      INTO custid
      FROM dual;

      IF progid = '737' THEN
       OrdDate := SYSDATE + lineno;
        DelDate := OrdDate + lineno + 100;
      ELSIF progid = '747' THEN
        OrdDate := SYSDATE + lineno+17;
        DelDate := OrdDate + lineno + 302;
      ELSIF progid = '757' THEN
       OrdDate := SYSDATE + lineno+22;
       DelDate := OrdDate + lineno + 202;
      ELSIF progid = '767' THEN
        OrdDate := SYSDATE + lineno+43;
        DelDate := OrdDate + lineno + 189;
      ELSIF progid = '777' THEN
        OrdDate := SYSDATE + lineno-69;
        DelDate := OrdDate + lineno + 299;
      END IF;

      INSERT INTO airplanes
      (program_id, line_number, customer_id, order_date,
       delivered_date)
      VALUES
      (progid, lineno, custid, orddate, deldate);
    END LOOP;
  END LOOP;
  COMMIT;
END load_airplanes;
/
Load the Airplane Parts Demo Table BEGIN
  INSERT INTO parts VALUES ('737', 'Even', 'Wing', 2);
  INSERT INTO parts VALUES ('747', 'Even', 'Wing', 2);
  INSERT INTO parts VALUES ('757', 'Even', 'Wing', 2);
  INSERT INTO parts VALUES ('767', 'EVen', 'Wing', 2);
  INSERT INTO parts VALUES ('777', 'even', 'Wing', 2);
  INSERT INTO parts VALUES ('737', 'ODD', 'Wing', 2);
  INSERT INTO parts VALUES ('747', 'odd', 'Wing', 2);
  INSERT INTO parts VALUES ('757', 'Odd', 'Wing', 2);
  INSERT INTO parts VALUES ('767', 'Odd', 'Wing', 2);
  INSERT INTO parts VALUES ('777', 'Odd', 'Wing', 2);
  INSERT INTO parts VALUES ('737', 'Even', 'Galley', 1);
  INSERT INTO parts VALUES ('747', 'EVen', 'Galley', 3);
  INSERT INTO parts VALUES ('757', 'EVEN', 'Galley', 3);
  INSERT INTO parts VALUES ('767', 'EVeN', 'Galley', 2);
  INSERT INTO parts VALUES ('777', 'even', 'Galley', 3);
  INSERT INTO parts VALUES ('737', 'ODD', 'Galley', 2);
  INSERT INTO parts VALUES ('747', 'odd', 'Galley', 4);
  INSERT INTO parts VALUES ('757', 'Odd', 'Galley', 3);
  INSERT INTO parts VALUES ('767', 'ODd', 'Galley', 4);
  INSERT INTO parts VALUES ('777', 'odD', 'Galley', 4);
  INSERT INTO parts VALUES ('737', 'Even', 'Tire', 10);
  INSERT INTO parts VALUES ('747', 'Even', 'Tire', 18);
  INSERT INTO parts VALUES ('757', 'Even', 'Tire', 12);
  INSERT INTO parts VALUES ('767', 'Even', 'Tire', 14);
  INSERT INTO parts VALUES ('777', 'EveN', 'Tire', 16);
  INSERT INTO parts VALUES ('737', 'ODD', 'Tire', 14);
  INSERT INTO parts VALUES ('747', 'Odd', 'Tire', 20);
  INSERT INTO parts VALUES ('757', 'Odd', 'Tire', 14);
  INSERT INTO parts VALUES ('767', 'Odd', 'Tire', 16);
  INSERT INTO parts VALUES ('777', 'Odd', 'Tire', 18);
  INSERT INTO parts VALUES ('737', 'Even', 'Seats', 105);
  INSERT INTO parts VALUES ('747', 'Even', 'Seats', 255);
  INSERT INTO parts VALUES ('757', 'Even', 'Seats', 140);
  INSERT INTO parts VALUES ('767', 'Even', 'Seats', 200);
  INSERT INTO parts VALUES ('777', 'EveN', 'Seats', 210);
  INSERT INTO parts VALUES ('737', 'ODD', 'Seats', 137);
  INSERT INTO parts VALUES ('747', 'Odd', 'Seats', 20);
  INSERT INTO parts VALUES ('757', 'Odd', 'Seats', 166);
  INSERT INTO parts VALUES ('767', 'Odd', 'Seats', 345);
  INSERT INTO parts VALUES ('777', 'Odd', 'Seats', 267);
  COMMIT;
END;
/
Nested Loops With Static Cursors CREATE OR REPLACE PROCEDURE nested_loop AUTHID DEFINER IS
 CURSOR a_cur IS
 SELECT program_id, line_number, customer_id
 FROM airplanes;

 a_rec a_cur%ROWTYPE;

 CURSOR p_cur IS
 SELECT part_type, quantity
 FROM parts
 WHERE program_id = a_rec.program_id
 AND UPPER(line_type)=DECODE(MOD(a_rec.line_number,2),0,'EVEN','ODD');

 p_rec p_cur%ROWTYPE;
BEGIN
  OPEN a_cur;
  LOOP
    FETCH a_cur INTO a_rec;
    EXIT WHEN a_cur%NOTFOUND;

    OPEN p_cur;
    LOOP
      FETCH p_cur INTO p_rec;
      EXIT WHEN p_cur%NOTFOUND;
      INSERT INTO ap_parts
      (customer_id, part_type, quantity)
      VALUES
      (a_rec.customer_id, p_rec.part_type, p_rec.quantity);
    END LOOP;
    CLOSE p_cur;

  END LOOP;
  COMMIT;
  CLOSE a_cur;
END nested_loop;
/
 
Nested Loops with Bulk Collect
Bulk Collect Nested Loops The purpose of this demo is to show how to duplicate nested cursor loops using BULK COLLECTION
CREATE OR REPLACE PROCEDURE bulk_nested AUTHID DEFINER IS
CURSOR a_cur IS
SELECT program_id, line_number, customer_id
FROM airplanes;

TYPE ap_array IS TABLE OF airplanes.program_id%TYPE
INDEX BY BINARY_INTEGER;
ap_rec ap_array;

TYPE al_array IS TABLE OF airplanes.line_number%TYPE
INDEX BY BINARY_INTEGER;
al_rec al_array;

TYPE ac_array IS TABLE OF airplanes.customer_id%TYPE
INDEX BY BINARY_INTEGER;
ac_rec ac_array;

TYPE pp_array IS TABLE OF parts.part_type%TYPE
INDEX BY BINARY_INTEGER;
pp_rec pp_array;

TYPE pq_array IS TABLE OF parts.quantity%TYPE
INDEX BY BINARY_INTEGER;
pq_rec pq_array;
BEGIN
  OPEN a_cur;
  LOOP
    FETCH a_cur BULK COLLECT INTO ap_rec, al_rec, ac_rec LIMIT 500;
     FOR i IN 1 .. ap_rec.COUNT LOOP
        DECLARE
         CURSOR p_cur IS
         SELECT part_type, quantity
         FROM parts
         WHERE program_id = ap_rec(i)
         AND UPPER(line_type)=DECODE(MOD(al_rec(i),2),0,'EVEN','ODD');
        BEGIN
          OPEN p_cur;
          LOOP
            FETCH p_cur BULK COLLECT INTO pp_rec, pq_rec;

           FORALL j IN pp_rec.FIRST .. pp_rec.LAST
            INSERT INTO ap_parts
            (customer_id, part_type, quantity)
            VALUES
            (ap_rec(i), pp_rec(j), pq_rec(j));

            EXIT WHEN p_cur%NOTFOUND;
         END LOOP;
          CLOSE p_cur;
        END;
      END LOOP;
    EXIT WHEN a_cur%NOTFOUND;
  END LOOP;
  COMMIT;
  CLOSE a_cur;
END bulk_nested;
/
 
Bidirectional Cursors
Bidirectional Cursor Demo This example demonstrates how to implement bidirectional cursors using BULK COLLECT into a collection
CREATE TABLE jokes (
joke_id INTEGER,
title   VARCHAR2(100),
text    VARCHAR2(4000));

INSERT INTO jokes VALUES (1, 'AAA', 'aaaaaaaaaaa');
INSERT INTO jokes VALUES (1, 'BBB', 'bbbbbbbbbbb');
INSERT INTO jokes VALUES (1, 'CCC', 'ccccccccccc');
INSERT INTO jokes VALUES (1, 'DDD', 'ddddddddddd');
INSERT INTO jokes VALUES (1, 'EEE', 'eeeeeeeeeee');
INSERT INTO jokes VALUES (1, 'FFF', 'fffffffffff');
COMMIT;

CREATE OR REPLACE PROCEDURE bidir AUTHID DEFINER IS
 TYPE joke_tt IS TABLE OF jokes%ROWTYPE
 INDEX BY PLS_INTEGER;

 joke_cache joke_tt;
 l_row PLS_INTEGER;
BEGIN
  SELECT *
  BULK COLLECT INTO joke_cache
  FROM jokes;

  dbms_output.put_line('From first to last...');
  l_row := joke_cache.FIRST;

  WHILE (l_row IS NOT NULL) LOOP
    dbms_output.put_line (' ' || joke_cache (l_row).title);
    l_row := joke_cache.NEXT (l_row);
  END LOOP;

  dbms_output.put_line('From last to first...');
  l_row := joke_cache.LAST;

  WHILE (l_row IS NOT NULL) LOOP
    dbms_output.put_line (' ' || joke_cache (l_row).title);
    l_row := joke_cache.PRIOR (l_row);
  END LOOP;

  dbms_output.put_line('Compare 2nd row to 5th row...');

  IF LENGTH(joke_cache(2).title) > LENGTH(joke_cache(5).title) THEN
    dbms_output.put_line(' 2nd row title longer than 5th.');
  ELSE
    dbms_output.put_line(' 2nd row title is not longer than 5th.');
  END IF;

  joke_cache.DELETE;
END bidir;
/
 
Using a Label for Referencing Variables Outside a Loop
Label Demo <<label_name>>
set serveroutput on

<<main>>
DECLARE
 i NUMBER := 5;
BEGIN
  FOR i IN 1..3 LOOP -- assign the values 1,2,3 to i
    dbms_output.put_line( 'local: ' || TO_CHAR(i) || ' global: ' || TO_CHAR(main.i));
  END LOOP;
END main;
/

BEGIN
  <<outer_loop>>
  FOR i IN 1..3 LOOP -- assign the values 1,2,3 to i
    <<inner_loop>>
    FOR i IN 1..3 LOOP
      IF outer_loop.i = 2 THEN
        dbms_output.put_line('outer: ' || TO_CHAR(outer_loop.i) || ' inner: ' || TO_CHAR(inner_loop.i));
      END IF;
    END LOOP inner_loop;
  END LOOP outer_loop;
END;
/
Using GOTO with Labels CREATE OR REPLACE PROCEDURE goto_demo(pInVal IN INTEGER) AUTHID DEFINER IS
BEGIN
  IF mod(pInVal, 2) = 0 THEN
    GOTO process_even;
  ELSE
    GOTO process_odd;
  END IF;
dbms_output.put_line('xxx');
  <<process_even>>
  dbms_output.put_line('Even');
  GOTO process_end; -- try dropping this to understand the behaviour

  <<process_odd>>
  dbms_output.put_line('Odd');

  <<process_end>>
  RETURN;
END goto_demo;
/

set serveroutput on

exec goto_demo(43);

exec goto_demo(42);

Related Topics
Array Processing and Bulk Binding
CASE Function
DECODE Function
Nested Loops
Procedures

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved