Anonymous

SQL> @toystore
SQL> CREATE OR REPLACE PACKAGE toystore AS
  2  --      INVALID_ITEM_ID EXCEPTION
  3  --      INVALID_ORDER_ID EXCEPTION
  4  --      NOT_ENOUGH_STOCK EXCEPTION
  5
  6          PROCEDURE addStock (item_Id IN ITEM.itemId%TYPE, addQty IN NUMBER);
  7  --      PROCEDURE documentStock(item_Id IN ITEM.itemId%TYPE, subQty IN NUMB
ER);
  8  --      PROCEDURE displayItem(item_Id IN ITEM.itemID%TYPE;
  9  --      PROCEDURE displayItemsCategory(item_Id IN catId VARCHAR);
 10  --      PROCEDURE createOrderItem(orderId IN NUMBER, item_Id IN ITEM.itemId
%TYPE, qty IN NUMBER);
 11  --      PROCEDURE displayOrder(IN orderId NUMBER);
 12  --
 13  --      FUNCTION getStockAmount(item_Id IN ITEM.itemId%TYPE) RETURN NUMBER;
 14  --      FUNCTION getPrice(item_Id IN ITEM.itemId%TYPE) RETURN NUMBER;
 15  --      FUNCTION createOrder(custId IN VARCHAR2) RETURN NUMBER;
 16
 17  END toystore;
 18  /

Package created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY toystore AS
  2
  3          PROCEDURE addStock (item_Id IN ITEM.itemId%TYPE, addQty IN NUMBER)
IS
  4          v_old_qty Inventory.Qty%TYPE;
  5          v_new_qty Inventory.Qty%TYPE;
  6          BEGIN
  7                  SELECT qty INTO v_old_qty
  8                  FROM inventory
  9                  WHERE inventory.itemId = item_Id;
 10
 11                  v_new_qty := v_old_qty + addQty;
 12
 13                  UPDATE Inventory SET qty = v_new_qty WHERE inventory.itemId
 = item_Id;
 14          END addStock;
 15  END toystore;
 16  /

Package body created.

SQL>
SQL> show errors
No errors.
SQL>