| Nested Cursor
Loops: Demo 1 |
Demo Tables |
CREATE TABLE parent (
year_hired NUMBER(4),
factor NUMBER(2,1));
INSERT INTO parent VALUES (2007, 1.2);
INSERT INTO parent VALUES (2008, 1.1);
INSERT INTO parent VALUES (2009, 1.0);
CREATE TABLE child (
hdate DATE,
oldsal FLOAT,
newsal FLOAT);
INSERT INTO child VALUES (TO_DATE('02-JAN-2006'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('03-FEB-2007'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('04-MAR-2007'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('04-APR-2007'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('05-MAY-2008'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('06-JUN-2008'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('06-JUL-2009'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('07-AUG-2010'), 100, NULL);
SELECT * FROM parent;
SELECT * FROM child;
set serveroutput on
DECLARE
CURSOR pcur IS
SELECT year_hired, factor
FROM parent;
BEGIN
FOR prec IN pcur LOOP
dbms_output.put_line(TO_CHAR(prec.year_hired) || '-' ||
TO_CHAR(prec.factor));
END LOOP;
END;
/
-- prec is only valid within the cursor FOR loop
DECLARE
CURSOR pcur IS
SELECT year_hired, factor
FROM parent;
CURSOR ccur IS
SELECT hdate, oldsal
FROM child
WHERE TO_NUMBER(TO_CHAR(hdate,'YYYY')) = prec.year_hired;
BEGIN
FOR prec IN pcur LOOP
dbms_output.put_line(TO_CHAR(prec.year_hired) || '-' ||
TO_CHAR(prec.factor));
END LOOP;
END;
/
-- prec declared in the correct scope
DECLARE
CURSOR pcur IS
SELECT year_hired, factor
FROM parent;
prec pcur%ROWTYPE;
CURSOR ccur IS
SELECT hdate, oldsal
FROM child
WHERE TO_NUMBER(TO_CHAR(hdate,'YYYY')) = prec.year_hired;
BEGIN
OPEN pcur;
LOOP
FETCH pcur INTO prec;
EXIT WHEN pcur%NOTFOUND;
dbms_output.put_line(TO_CHAR(prec.year_hired) || '-' ||
TO_CHAR(prec.factor));
END LOOP;
CLOSE pcur;
END;
/
-- with dependent nested loop
DECLARE
CURSOR pcur IS
SELECT year_hired, factor
FROM parent;
prec pcur%ROWTYPE;
CURSOR ccur IS
SELECT hdate, oldsal
FROM child
WHERE TO_NUMBER(TO_CHAR(hdate,'YYYY')) = prec.year_hired;
BEGIN
OPEN pcur;
LOOP
FETCH pcur INTO prec;
EXIT WHEN pcur%NOTFOUND;
dbms_output.put_line(TO_CHAR(prec.year_hired) || '-' ||
TO_CHAR(prec.factor));
FOR crec IN ccur
LOOP
dbms_output.put_line(TO_CHAR(crec.hdate) ||
'-' ||
TO_NUMBER(crec.oldsal));
END LOOP;
END LOOP;
CLOSE pcur;
END;
/
DECLARE
CURSOR pcur IS
SELECT year_hired, factor
FROM parent;
prec pcur%ROWTYPE;
CURSOR ccur IS
SELECT hdate, oldsal
FROM child
WHERE TO_NUMBER(TO_CHAR(hdate,'YYYY')) = prec.year_hired
FOR UPDATE;
BEGIN
OPEN pcur;
LOOP
FETCH pcur INTO prec;
EXIT WHEN pcur%NOTFOUND;
FOR crec IN ccur LOOP
UPDATE child
SET newsal = prec.factor * crec.oldsal
WHERE CURRENT OF ccur;
END LOOP;
END LOOP;
COMMIT;
CLOSE pcur;
END;
/
SELECT * FROM child; |
| |
| Nested Cursor
Loops: Demo 2 |
Demo Tables |
CREATE TABLE ap_orders (
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 ap_orders (program_id)
PCTFREE 0;
CREATE TABLE parts (
program_id VARCHAR2(3),
line_type VARCHAR2(4),
part_type VARCHAR2(10),
quantity NUMBER(3));
CREATE INDEX fbi_parts_line_type
ON parts (UPPER(line_type))
PCTFREE 0;
CREATE TABLE ap_parts AS
SELECT a.customer_id, p.part_type, p.quantity
FROM ap_orders a, parts p
WHERE a.program_id = p.program_id
AND 1=2; |
Load AP_ORDERS |
DECLARE
progid ap_orders.program_id%TYPE;
lineno ap_orders.line_number%TYPE;
custid ap_orders.customer_id%TYPE := 'AAL';
orddate ap_orders.order_date%TYPE;
deldate ap_orders.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+23;
DelDate := OrdDate + lineno + 202;
ELSIF progid = '767' THEN
OrdDate := SYSDATE + lineno+42;
DelDate := OrdDate + lineno + 189;
ELSIF progid = '777' THEN
OrdDate := SYSDATE + lineno-69;
DelDate := OrdDate + lineno + 299;
END IF;
INSERT INTO ap_orders
(program_id, line_number, customer_id, order_date,
delivered_date)
VALUES
(progid, lineno, custid, orddate, deldate);
END LOOP;
END LOOP;
COMMIT;
END load_ap_orders;
/ |
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
cursor_nested IS
CURSOR a_cur IS
SELECT program_id, line_number, customer_id
FROM ap_orders;
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 cursor_nested;
/
set timing on
exec cursor_nested
set timing off
SELECT * FROM ap_parts; |
| |
| Nested Bulk Collection
Loops |
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 ap_orders;
TYPE ap_array IS TABLE OF ap_orders.program_id%TYPE
INDEX BY BINARY_INTEGER;
ap_rec ap_array;
TYPE al_array IS TABLE OF ap_orders.line_number%TYPE
INDEX BY BINARY_INTEGER;
al_rec al_array;
TYPE ac_array IS TABLE OF ap_orders.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;
/
set timing on
exec bulk_nested
set timing off
SELECT * FROM ap_parts; |
|
