-- add_stock_warn.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;

  IF (FALSE) THEN   -- generates warning for code that will never run
    qtyoh := qtyoh + new_stock;
  END IF;

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

END;
/
ALTER PROCEDURE add_stock COMPILE PLSQL_WARNINGS = 'ENABLE:ALL'
/
SHOW ERRORS
