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_REORDERED.AMOUNT_REORDERED%TYPE;
  5. TYPE type_coll_productId
  6. IS TABLE OF S_REORDERED.PRODUCT_ID%TYPE;
  7. TYPE type_coll_warehouseId
  8. IS TABLE OF S_REORDERED.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. FORALL i IN t_amounts.FIRST ..t_amounts.LAST
  36. DELETE FROM s_reordered
  37. WHERE product_id = T_PRODUCTIDS(i) AND warehouse_id = T_WAREHOUSEIDS(i);
  38.  
  39.  
  40. WHEN e_bulk_errors
  41. FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT
  42. DBMS_OUTPUT.PUT_LINE('Fout bij element met indexnummer : ' || SQL%BULK_EXCEPTIONS(I).ERROR_INDEX);
  43. DBMS_OUTPUT.PUT_LINE('Opgetreden oracle-error : ' || SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
  44. DBMS_OUTPUT.PUT_LINE('Bijhorende fouttekst: ' || SQLERRM(-1 * SQL%BULK_EXCEPTIONS(I).ERROR_CODE));
  45. DBMS_OUTPUT.PUT_LINE('******************************************** ');
  46. END P4_6_AANPASSING_VOORRAAD ;

Report this snippet  

You need to login to post a comment.