zerze

CREATE OR REPLACE PROCEDURE P2_1_OVERZICHT_PROD_WAREHOUSE AS
 
  TYPE TYPE_REC_PRODUCTS IS RECORD(
    PID     S_PRODUCT.ID%TYPE,
    PNAME   S_PRODUCT.NAME%TYPE,
    WHID    S_INVENTORY.WAREHOUSE_ID%TYPE,
    WHCITY  S_WAREHOUSE.CITY%TYPE
  );
 
  TYPE TYPE_COLL_PRODUCTS
  IS TABLE OF TYPE_REC_PRODUCTS;
  T_PRODUCTS TYPE_COLL_PRODUCTS;
  V_OLDWHID S_WAREHOUSE.ID%TYPE DEFAULT -1;
 
BEGIN
  SELECT PRODUCT_ID, 
    (SELECT NAME FROM S_PRODUCT WHERE ID= PRODUCT_ID) NAME, WAREHOUSE_ID, 
    (SELECT CITY FROM S_WAREHOUSE WHERE ID=WAREHOUSE_ID)
  BULK COLLECT INTO T_PRODUCTS
  FROM S_INVENTORY
  ORDER BY WAREHOUSE_ID, NAME;
 
  FOR i IN 1..T_PRODUCTS.COUNT
    LOOP
      IF V_OLDWHID != T_PRODUCTS(I).WHID THEN
        DBMS_OUTPUT.PUT_LINE(' ');
        DBMS_OUTPUT.PUT_LINE(T_PRODUCTS(I).WHID || ' ' || T_PRODUCTS(I).WHCITY);
        DBMS_OUTPUT.PUT_LINE('-----------');
      END IF;
      DBMS_OUTPUT.PUT_LINE(T_PRODUCTS(I).PID || ' ' || T_PRODUCTS(I).PNAME);
      V_OLDWHID := T_PRODUCTS(I).WHID;      
    END LOOP;
END P2_1_OVERZICHT_PROD_WAREHOUSE;