Oracle Nested Loops
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Basic Nested Loops
Demo tables and data conn uwclass/uwclass@pdbdev

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-2012'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('03-FEB-2013'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('04-MAR-2014'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('04-APR-2015'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('05-MAY-2016'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('06-JUN-2017'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('06-JUL-2018'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('07-AUG-2019'), 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;
 
Static Cursor
Demo tables conn uwclass/uwclass@pdbdev

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;
AP_ORDERS Demo Data 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 AUTHID DEFINER 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;
 
Bulk Collection
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 CURRENT_USER 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;

Related Topics
Bulk Binding
Control Structures
Exception Handling
Procedures
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx