--%>
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 : Compute Days Between Ordering and

    An analyst in the quality assurance office reviews the time lapse between receiving an order and shipping an order. Any orders which have not been shipped in a day of the order being positioned are investigated. Build a function named ORD_SHIP_SF which computes the nu

    		•
  • Q : Define the term Stack trace Define the

    Define the term Stack trace: It is a display of the runtime stack.

    		•
  • Q : Function prototypes Function prototypes

    Function prototypes: Function declaration which specifies the function name, return type and parameter list of the function. Syntax: return_type function_name(type var1, type var2,…

    		•
  • Q : Explain Out-of-bounds value

    Out-of-bounds value: It is a redundant value employed to point out that a different action from the norm is needed at some point. The read technique of InputStream returns -1 to point out that the end of a stream has been reached, for illustration, ra

    		•
  • Q : What are the tools require writing

    What are the tools require writing XHTML documents?

    		•
  • Q : What is Binary operator Binary operator

    Binary operator: It is an operator taking two operands. Java has numerous binary operators, like the arithmetic operators +, -, *, / and %, and the Boolean operators &&, || and ^, among others.

    		•
  • Q : BAT files and shell scripts Using the

    Using the Web as a resource for your investigation, find out and explain what BAT files and shell scripts contain, and what they are used for. In the light of your findings, discuss why an operating system would provide both a graphical user interface

    		•
  • Q : Explain Right shift operator Right

    Right shift operator: Right shift operator (>>) is the bit manipulation operator. It shifts the bits in its left operand zero (0) or many places to the right, according to the value of its accurate operand. The most important bit from before the

    		•
  • Q : Define Thread starvation Thread

    Thread starvation: It is a condition which applies to a thread which is prevented from running by other threads which do not yield or turn into blocked.

    		•
  • Q : Define the term XSL-FO Define the term

    Define the term XSL-FO. Answr: A sub-element of XSL used for explaining font sizes, how information flows from one page to other and page layouts.

    		•