Oracle Nested Loops
Version 26ai

General Information
Library Note Morgan's Library Page Header
The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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 (2012, 1.2);
INSERT INTO parent VALUES (2013, 1.1);
INSERT INTO parent VALUES (2014, 1.0);

CREATE TABLE child (
hdate  DATE,
oldsal FLOAT,
newsal FLOAT);

INSERT INTO child VALUES (TO_DATE('02-JAN-2017'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('03-FEB-2018'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('04-MAR-2019'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('04-APR-2020'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('05-MAY-2021'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('06-JUN-2022'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('06-JUL-2023'), 100, NULL);
INSERT INTO child VALUES (TO_DATE('07-AUG-2024'), 100, NULL);
COMMIT;

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;
/
WHERE TO_NUMBER(TO_CHAR(hdate,'YYYY')) = prec.year_hired;
*
ERROR at line 9:
ORA-06550: line 9, column 43:
PL/SQL: ORA-00904: "PREC"."YEAR_HIRED": invalid identifier
ORA-06550: line 7, column 2:
PL/SQL: SQL Statement ignored

-- 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;
/

PL/SQL procedure successfully completed.

SELECT * FROM child;

HDATE                 OLDSAL  NEWSAL
-------------------- ------- --------
02-JAN-2017 00:00:00     100
03-FEB-2018 00:00:00     100
04-MAR-2019 00:00:00     100
04-APR-2020 00:00:00     100
05-MAY-2021 00:00:00     100
06-JUN-2022 00:00:00     100
06-JUL-2023 00:00:00     100
07-AUG-2024 00:00:00     100
 
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 26ai

Morgan's Library Page Footer
This site is maintained by Daniel Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2026 Daniel A. Morgan All Rights Reserved