Oracle Control Structures LOOPS FOR WHILE CURSOR IF THEN ELSE ELSIF


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

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