CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Control Structures
Version 11.1
 
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
Loop Demo Table 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.
-- incrementing
FOR <variable> IN <start_number> .. <end_number>
LOOP
  <code here>
END LOOP;

-- decrementing
FOR <variable> IN REVERSE <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;

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) 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>
END LOOP;
set serveroutput on

CREATE OR REPLACE PROCEDURE contwhen_test (val IN POSITIVEN) 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_objs
  WHERE SUBSTR(object_name,1,5) BETWEEN 'A' AND 'M';

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 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_objs
   WHERE SUBSTR(object_name,1,5) BETWEEN 'N' AND 'W';
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_objs
    WHERE SUBSTR(object_name,1,5) BETWEEN 'N' AND 'Z')

  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 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 Airplanes
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 Airplane Parts
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 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 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 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;
/
 
Related Topics
Bulk Binding
Nested Loops
Procedures
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [145 users online]    © 2010 psoug.org