Posted By

wh0emPah on 04/02/10


Tagged


Versions (?)

zerfz


 / Published in: PL/SQL
 

  1. CREATE OR REPLACE PROCEDURE P4_6_AANPASSING_VOORRAAD AS
  2.  
  3. TYPE TYPE_COLL_AMOUNT
  4. IS TABLE OF S_INVENTORY.AMOUNT_IN_STOCK%TYPE;
  5. TYPE type_coll_productId
  6. IS TABLE OF S_INVENTORY.PRODUCT_ID%TYPE;
  7. TYPE type_coll_warehouseId
  8. IS TABLE OF S_INVENTORY.WAREHOUSE_ID%TYPE;
  9.  
  10. T_AMOUNTS TYPE_COLL_AMOUNT;
  11. T_PRODUCTIDS TYPE_COLL_PRODUCTID;
  12. t_warehouseIds type_coll_warehouseId;
  13.  
  14. E_BULK_ERRORS EXCEPTION;
  15. PRAGMA EXCEPTION_INIT(E_BULK_ERRORS,-24381);
  16.  
  17. SELECT amount_reordered BULK COLLECT INTO t_amounts
  18. FROM s_reordered
  19. WHERE DATE_IN IS NOT NULL;
  20.  
  21. SELECT product_id BULK COLLECT INTO t_productIds
  22. FROM s_reordered
  23. WHERE DATE_IN IS NOT NULL;
  24.  
  25. SELECT warehouse_id BULK COLLECT INTO t_warehouseIds
  26. FROM s_reordered
  27. WHERE DATE_IN IS NOT NULL;
  28.  
  29. FORALL i IN t_amounts.FIRST ..t_amounts.LAST
  30. SAVE EXCEPTIONS
  31. UPDATE S_INVENTORY
  32. SET amount_in_stock = amount_in_stock + T_AMOUNTS(i)
  33. WHERE PRODUCT_ID = T_PRODUCTIDS(I) AND WAREHOUSE_ID = T_WAREHOUSEIDS(I);
  34. -- Moeten de records uit de reordered table ook gewist worden?
  35. WHEN e_bulk_errors
  36. FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT
  37. DBMS_OUTPUT.PUT_LINE('Fout bij element met indexnummer : ' || SQL%BULK_EXCEPTIONS(I).ERROR_INDEX);
  38. DBMS_OUTPUT.PUT_LINE('Opgetreden oracle-error : ' || SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
  39. DBMS_OUTPUT.PUT_LINE('Bijhorende fouttekst: ' || SQLERRM(-1 * SQL%BULK_EXCEPTIONS(I).ERROR_CODE));
  40. DBMS_OUTPUT.PUT_LINE('******************************************** ');
  41.  
  42. END P4_6_AANPASSING_VOORRAAD ;

Report this snippet  

You need to login to post a comment.