--%>
Assignment Help - homework help

Updating the Status of an Order in SQL

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

E

Expert

Verified

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;

   Related Questions in Programming Languages

  • Q : Write a function Write a function that

    Write a function that takes an integer value and returns the number with its digits reversed. For example, given 7631, the function should return 1367.

  • Q : Define the need of process relative

    Define the need of process relative handles.

    		•
  • Q : Problem on Gaussian Matlab function

    Solve the system Ax = b, with using the given Matlab function gauss (it cal

    		•
  • Q : Definition of Role Normal 0 false false

    Normal 0 false false

  • Q : Explain Polymorphism Polymorphism : It

    Polymorphism: It is the ability of an object reference to be employed as if it referred to an object with various forms. The polymorphism in Java outcomes from both class inheritance and interface inheritance. The actually different forms frequently o

    		•
  • Q : Define Unconditional Jumps

    Unconditional Jumps: Jumps which are not strictly upwards in the block hierarchy can require extensive control-flow manipulation, including creation of redundant code, and should be avoided if possible. Such jumps add considerable modeling overhead fo

    		•
  • Q : Explain Redundant value Redundant value

    Redundant value: The value of a data type which has no use or meaning in a particular context. For example, negative values would be redundant a class employing integer attributes to the model assignment marks. In several applications, redundant patte

    		•
  • Q : State Default label Default label : The

    Default label: The destination for all values employed in a switch statement expression which do not contain explicit case labels. The default label is not obligatory.

    		•
  • Q : Explain the Automated Software testing

    Explain the Automated Software testing life cycle.

    		•
  • Q : Explain Static variable Static variable

    Static variable: It is a static variable defined within a class body. Such a variable fits in to the class as a whole, and is, thus, shared by all objects of the class. The class variable may be employed to define the default value of an instance vari

    		•