Create a procedure named STATUS_SHIP_SP that allows an employee in the Shipping.

Question

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')

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 1

announce

email customers.email%Type;

pass customers.password%Type;

include customers.address%Type;

con customers.contact%Type;

start

embed into clients values(email, pass, include, con);

end;

/

Rate this solution