|
|
|
Search the Reference Library pages: |
|
|
|
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 <[email protected]>
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;
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;
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;
/ |
|
|
|
|
-----
|