--%>

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 : Explain Virtual memory Virtual memory :

    Virtual memory: It is computer will comprise a limited amount of real memory accessible to it. Programs frequently need more memory than the quantity of real memory. Moreover, in a multiprogramming system, various processes will be competing for simil

  • Q : Questions on java class Array 1.

    1. Suppose we have a class A which has a constructor that takes a single integer. (a) After the following statements have been executed, how many A objects will exist

  • Q : Explain different types of variable iv

    The kind of value that a variable can own is known as data type. When we state a variable we require specifying the type of value it will own with the n

  • Q : Simple Sorting Program in C Shell

    Assignment: Sorting You will write a simple sorting program. This program should be invoked as follows: shell% ./fastsort -i inputfile -o outputfile

  • Q : Assembly program that reads in five

     I need to write assembly program that reads in five numbers from the user. The user can then be prompted for one of the following pieces of information to be computed and returned: . the mean of the five numbers; . the largest number in the set; . the smallest number in the set; . t

  • Q : What is Memory leak Memory leak : It is

    Memory leak: It is a situation in which memory which is no longer being employed has not been returned to the pool of free memory. The garbage collector is designed to return unreferenced objects to the free memory pool in order to shun memory leaks.<

  • Q : Define Arithmetic operator Arithmetic

    Arithmetic operator: The Operators, like +, -, *, / and %, which generate a numerical outcome, as a part of an arithmetic expression.

  • Q : State Space Reduction in Java PathFinder

    State Space Reduction: JPF is a so-called explicit-state model checker, as it enumerates all visited states, and therefore suffers from the state explosion problem inherent in analyzing large programs. It also contains garbage collection, because a ty

  • Q : Problem on COBOL source code errors

    There are many errors in the following COBOL source code. Identify the errors and rewrite the program so that it contains no errors:IDENTIFICATION DIVISION   (Full stop required)PROGRAM ID. ERRORS-EXAMPLE.DATA DIVISION    (Fu

  • Q : Special set of tags in PHP Normal 0

    Normal 0 false false