-- add_stock.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;
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 OTHERS THEN
          IF SQLCODE = -2291 THEN
            raise_application_error(-20005,
              'Invalid store or product ID.', false);
          ELSE
            RAISE;
          END IF;
      END;
  END;

  qtyoh := qtyoh + new_stock;

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

END;
/
