-- add_stock2.sql

CREATE OR REPLACE PROCEDURE add_stock (
  snum inventory.store_number%TYPE,
  pid inventory.product_id%TYPE,
  new_stock inventory.quantity_on_hand%TYPE )
AS
  qtyoh inventory.quantity_on_hand%TYPE := 0;
  no_parent_record EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_parent_record, -2291);
BEGIN

  BEGIN
    SELECT quantity_on_hand INTO qtyoh
      FROM inventory
     WHERE store_number = snum AND product_id = pid;
  EXCEPTION
    WHEN no_data_found THEN
      BEGIN
        INSERT INTO inventory (store_number, product_id)
               VALUES (snum, pid);
      EXCEPTION
        WHEN no_parent_record THEN
            raise_application_error(-20005,
              'Invalid store or product ID.', false);
      END;
  END;

  qtyoh := qtyoh + new_stock;

  UPDATE inventory SET quantity_on_hand = qtyoh
   WHERE store_number = snum AND product_id = pid;

END;
/
