Task2 : Updating Order Status
Create a procedure named STATUS_SHIP_SP that allows an employee in the Shipping.
Department to update an order status to add shipping information. The BB_BASKETSTATUS
table lists events for each order so that a shopper can see the current status, date, and
comments as each stage of the order process are finished.
The IDSTAGE column of the BB_BASKETSTATUS table identifies each stage; the value 3 in this column indicates that an
order has been shipped.
The procedure should allow adding a row with an IDSTAGE of 3, date shipped, tracking
number, and shipper. The BB_STATUS_SEQ sequence is used to provide a value for the primary
key column.
HINT: In your Procedure main part (BEGIN _END)
BEGIN
INSERT INTO bb_basketstatus (idstatus, idbasket, idstage, dtstage,
shipper, shippingnum)
VALUES (bb_status_seq.NEXTVAL, p_basketid, 3, p_date, p_shipper,
p_shipnum);
COMMIT;
END;
bb_status_seq.NEXTVAL is auto number generator bb_status_seq.NEXTVAL is created in the database already. Every time you call this sequence nextval it will generate automatic/an unique /non repeated number for Primary key for idstatus
Test in SQL Developer
Test the procedure with the following information:
Basket # = 3
Date shipped = 20-FEB-12
Shipper = UPS
Tracking # = ZW2384YXK4957
Test the result with below command
SELECT *
FROM bb_basketstatus;
Test new entry with below statement and place screen shot here
GUI Work
Accept entry on your GUI for 4 different edit boxes and collect them and use them to call stored procedure in your code . There must be button which has name UpdateOrderStatus and when user press this button it should call this procedure and does its magic behind the schene.
Execute status_ship_sp(3,'20-FEB-12','UPS','ZW2384YXK4957')