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')
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
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')
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
