--%>

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 : OCIW32 dll error Normal 0 false false

    Normal 0 false false

  • Q : What is Shallow copy Shallow copy : It

    Shallow copy: It is a copy of an object in which copies of each and every object's sub-components are not as well made. For example, a shallow copy of an array of objects would outcome in two separate array objects, each having references to similar s

  • Q : What is Random Access Memory Random

    Random Access Memory: Random access memory, or RAM, is the memory whose contents are simply accessible to the processing components of a computer. In specific, the time it takes to read and write to a particular portion of the memory does not based on

  • Q : What is pipe What is meant by the term

    What is meant by the term pipe?

  • Q : Write a program and estimate pi using

    Consider a dartboard of radius 1. Since the area of the board will be  π r2 = π * 1 *1, it's clear the area of the dartboard is exactly π. The area of a square surrounding the board (circumscribing it) would be 2*2 = 4, sin

  • Q : Problem on Vectors Create a vector

    Create a vector representing x coordinates of a measurement with 20 points between 0 and 10. Create another vector y representing fake measurements which are related to the above x values as y = 2.3 x – 1.2. Next add random (normal, Gaussian) noise to the vector

  • Q : Learning File system with operations

    Program 1. Purpose of this assignment is to learn to work with files.Assignment: The file, data.txt, is provided on the class website. Each line of the file consists of a binary operator followed by two operands. Your program wi

  • Q : Types of layout managers Describe the

    Describe the layout manager and what are the various types of layout managers accessible?

  • Q : Define Passing by value Passing by

    Passing by value: In this process separate memory builds for formal arguments and when any modifications done on formal variables, it will not influence the real variables. Therefore actual variables are preserved in this situation.

  • Q : Differentiate between MFC and Win32

    Differentiate between MFC and Win32?