Updating the Status of an Order in SQL

Question

Build a procedure named STATUS_SHIP_SP which permits a company to employee in the Shipping Department to update the status of an order to add up shipping information. The BB_BASKETSTATUS table maintains a list of events for each order and hence a shopper can see the current status, date, and comments as each phase of the order process is finished. The IDSTAGE column of the BB_BASKETSTATUS table recognizes each stage and an IDSTAGE of 3 points out the order has been shipped.
 
The procedure must permit the addition of a row pointing an IDSTAGE of 3, date shipped, tracking number, and shipper. The series BB_STATUS_SEQ is employed to give a value for the primary key column. Test the procedure with the below information:
 
 Basket # = 3
 Date Shipped = 20-FEB-03
 Shipper = UPS
 Tracking # = ZW2384YXK4957

Answer 1

create or replace procedure "STATUS_SHIP_SP"
(basket IN NUMBER,
tidstage IN NUMBER,
date_shipped IN DATE,
tshipper IN VARCHAR2,
tracking IN VARCHAR2)
is
begin
INSERT INTO BB_BASKETSTATUS (IDSTATUS,IDBASKET,IDSTAGE,DTSTAGE,SHIPPER,SHIPPINGNUM)  VALUES (BB_STATUS_SEQ.NEXTVAL, BASKET,TIDSTAGE,DATE_SHIPPED,TSHIPPER,TRACKING);
 
COMMIT;
 
EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end;