zerfz

CREATE OR REPLACE PROCEDURE P4_6_AANPASSING_VOORRAAD  AS 
 
TYPE TYPE_COLL_AMOUNT
  IS TABLE OF S_INVENTORY.AMOUNT_IN_STOCK%TYPE;
TYPE type_coll_productId
  IS TABLE OF S_INVENTORY.PRODUCT_ID%TYPE;
TYPE type_coll_warehouseId
  IS TABLE OF S_INVENTORY.WAREHOUSE_ID%TYPE;
 
  T_AMOUNTS  TYPE_COLL_AMOUNT;
  T_PRODUCTIDS  TYPE_COLL_PRODUCTID;
  t_warehouseIds  type_coll_warehouseId;
 
E_BULK_ERRORS  EXCEPTION;
PRAGMA EXCEPTION_INIT(E_BULK_ERRORS,-24381);
 
BEGIN
  SELECT amount_reordered BULK COLLECT INTO t_amounts
  FROM s_reordered
  WHERE DATE_IN IS NOT NULL;
 
  SELECT product_id BULK COLLECT INTO t_productIds
  FROM s_reordered
  WHERE DATE_IN IS NOT NULL;
 
  SELECT warehouse_id BULK COLLECT INTO t_warehouseIds
  FROM s_reordered
  WHERE DATE_IN IS NOT NULL;
 
  FORALL i IN t_amounts.FIRST ..t_amounts.LAST
    SAVE EXCEPTIONS
    UPDATE S_INVENTORY  
    SET  amount_in_stock = amount_in_stock + T_AMOUNTS(i)
    WHERE PRODUCT_ID = T_PRODUCTIDS(I) AND WAREHOUSE_ID = T_WAREHOUSEIDS(I);
      -- Moeten de records uit de reordered table ook gewist worden?
    COMMIT;
EXCEPTION
   WHEN e_bulk_errors
   THEN 
     FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT
        LOOP
          DBMS_OUTPUT.PUT_LINE('Fout bij element met indexnummer : ' || SQL%BULK_EXCEPTIONS(I).ERROR_INDEX);
          DBMS_OUTPUT.PUT_LINE('Opgetreden oracle-error : ' || SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
          DBMS_OUTPUT.PUT_LINE('Bijhorende fouttekst: ' || SQLERRM(-1 * SQL%BULK_EXCEPTIONS(I).ERROR_CODE));
          DBMS_OUTPUT.PUT_LINE('******************************************** ');
         END LOOP;
 
END P4_6_AANPASSING_VOORRAAD  ;