zerfz

CREATE OR REPLACE PROCEDURE P4_3_VOEG_ORDERLIJN_TOE(
  P_ORD_ID IN S_ITEM.ORD_ID%TYPE  
, P_PRODUCT_ID IN S_ITEM.PRODUCT_ID%TYPE   
, P_PRICE IN S_ITEM.PRICE%TYPE   
, P_QUANTITY IN S_ITEM.QUANTITY%TYPE 
) AS 
  V_ITEM_ID   S_ITEM.ITEM_ID%TYPE;
 
  E_NOPARENT EXCEPTION;
  PRAGMA EXCEPTION_INIT(E_NOPARENT,-2291);
 
BEGIN
  SELECT MAX(ITEM_ID)+1
  INTO V_ITEM_ID
  FROM S_ITEM
  WHERE ORD_ID = P_ORD_ID;
 
  IF V_ITEM_ID IS NULL THEN
    V_ITEM_ID := 1;
  END IF;
 
  INSERT INTO S_ITEM(ORD_ID,ITEM_ID,PRODUCT_ID,PRICE,QUANTITY)
  VALUES(P_ORD_ID, V_ITEM_ID, P_PRODUCT_ID, P_PRICE, P_QUANTITY);
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Orderlijn toegevoegd!');
 
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
  DBMS_OUTPUT.PUT_LINE('Dit ID is al in gebruik');
  WHEN E_NOPARENT THEN
  DBMS_OUTPUT.PUT_LINE('FK Not correct');
  WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR( -20000, 'Onbekende fout opgetreden');
END P4_3_VOEG_ORDERLIJN_TOE;