ProductenPerKlant

CREATE OR REPLACE
PROCEDURE ProductenPerKlant AS
--First cursor: loop through all customers
  CURSOR customcursor IS
    SELECT customer_id, cust_first_name, cust_last_name FROM oe.customers;
--Second cursor: loop through all customer's orders
  CURSOR ordercursor (customer customcursor%ROWTYPE) IS
    SELECT o.order_id FROM oe.orders o
    WHERE customer.customer_id = o.customer_id;
--Third cursor: loop through all customer's order's items
  CURSOR productcursor (item ordercursor%ROWTYPE) IS
    SELECT i.product_id, pi.product_name, i.unit_price, i.quantity FROM oe.order_items i
    INNER JOIN oe.product_information pi ON i.product_id = pi.product_id
    WHERE i.order_id = item.order_id;
BEGIN
  FOR customer IN customcursor LOOP
    DBMS_OUTPUT.put_line('Customer '||customer.customer_id||', '
        ||customer.cust_first_name||' '||customer.cust_last_name);
    FOR ordern IN ordercursor(customer) LOOP
      FOR product IN productcursor(ordern) LOOP
        DBMS_OUTPUT.put_line(product.product_id || ' ' || product.product_name
                || ' ' || product.unit_price || ' ' || product.quantity);
      END LOOP;
    END LOOP;
  END LOOP;
END;
/
/*---- Testapplication ---- */
BEGIN
productenperklant();
END;