Скачать или смотреть III SEM BCA SEP - DBMS Lab - B5 : Create a package to handle transactions involving stocks.

Given the following tables: ITEM_MASTER(itemno, name, stock, unit_price) [Apply the Primary key and check constraint for stock and price as greater than 0] [Insert 5 records] ITEM_TRANS(itemno, quantity and trans_date)

Create a package PCK_ITEM includes a function CHK_ITEM and a procedure PROC_ITEM.

Function CHK_ITEM gets one argument itemno and is used to check whether the parameter itemno exists in ITEM_MASTER and should return 1 if exist. Otherwise 0 and displays proper message.

Procedure PROC_ITEM gets two arguments itemno and quantity, and is used to perform the following if item exists. If required quantity is not available, give appropriate message. If available , insert a record of this transaction to ITEM_TRANS and modify the stock in ITEM_MASTER.

Write a PL/SQL program to accept ITEM_NO and Quantity needed of required item. Use Package to do the transaction process(Transaction date can be current date). OUTPUT the details in proper format.