Posted By

wh0emPah on 04/02/10


Tagged


Versions (?)

zerfz


 / Published in: PL/SQL
 

  1. CREATE OR REPLACE PROCEDURE P4_3_VOEG_ORDERLIJN_TOE(
  2. P_ORD_ID IN S_ITEM.ORD_ID%TYPE
  3. , P_PRODUCT_ID IN S_ITEM.PRODUCT_ID%TYPE
  4. , P_PRICE IN S_ITEM.PRICE%TYPE
  5. , P_QUANTITY IN S_ITEM.QUANTITY%TYPE
  6. ) AS
  7. V_ITEM_ID S_ITEM.ITEM_ID%TYPE;
  8.  
  9. E_NOPARENT EXCEPTION;
  10. PRAGMA EXCEPTION_INIT(E_NOPARENT,-2291);
  11.  
  12. SELECT MAX(ITEM_ID)+1
  13. INTO V_ITEM_ID
  14. FROM S_ITEM
  15. WHERE ORD_ID = P_ORD_ID;
  16.  
  17. IF V_ITEM_ID IS NULL THEN
  18. V_ITEM_ID := 1;
  19.  
  20. INSERT INTO S_ITEM(ORD_ID,ITEM_ID,PRODUCT_ID,PRICE,QUANTITY)
  21. VALUES(P_ORD_ID, V_ITEM_ID, P_PRODUCT_ID, P_PRICE, P_QUANTITY);
  22. DBMS_OUTPUT.PUT_LINE('Orderlijn toegevoegd!');
  23.  
  24. DBMS_OUTPUT.PUT_LINE('Dit ID is al in gebruik');
  25. WHEN E_NOPARENT THEN
  26. DBMS_OUTPUT.PUT_LINE('FK Not correct');
  27. RAISE_APPLICATION_ERROR( -20000, 'Onbekende fout opgetreden');
  28. END P4_3_VOEG_ORDERLIJN_TOE;

Report this snippet  

You need to login to post a comment.