Create a procedure named STATUS_SHIP_SP that allows an employee in the Shipping.
QuestionTask2 : Updating Order StatusCreate 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_BASKETSTATUStable lists events for each order so that a shopper can see the current status, date, andcomments 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 anorder has been shipped.
The procedure should allow adding a row with an IDSTAGE of 3, date shipped, trackingnumber, and shipper. The BB_STATUS_SEQ sequence is used to provide a value for the primarykey column.
HINT: In your Procedure main part (BEGIN _END)
BEGININSERT 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.NEXTVALis auto number generatorbb_status_seq.NEXTVAL is created in the database already. Every time you call this sequence nextval it will generate automatic/an unique /non repeatednumber for Primary key for idstatus
Test in SQL Developer
Test the procedure with the following information:Basket # = 3Date shipped = 20-FEB-12Shipper = UPSTracking # = ZW2384YXK4957
Test the result with below command
SELECT *FROM bb_basketstatus;
Test new entry with below statement and place screen shot here
GUI WorkAccept 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 UpdateOrderStatusand when user press this button it should call this procedure and does its magic behind the schene. Executestatus_ship_sp(3,'20-FEB-12','UPS','ZW2384YXK4957')
Expert Solution
arrow_forwardStep 1
The Complete Explanation is given below.
arrow_forwardStep 2
make table customers(email varchar(30), secret word varchar(20), address varchar(30), contact number);make table cellphone(ID varchar(10), mark varchar(20), demonstrate varchar(15), value number, os varchar(15), scr_size number, cam_reso varchar(20), organize varchar(3), ram_size varchar(15), storage_size varchar(10), battery varchar(10), shading varchar(10), stock number);make table order(ord_date date, ID varchar(10), qty number, ship_mtd varchar(20), ship_date date, ship_stat varchar(10), tot_due number, pay_method varchar(10));PL/SQL Block for highlight 1announceemail customers.email%Type;pass customers.password%Type;include customers.address%Type;con customers.contact%Type;startembed into clients values(email, pass, include, con);end;/
Rate this solution
页:
[1]