Question 1. Create a procedure named STATUS_SHIP_SP that allows an employee in the Brewbeans' Shipping Department to update an order status to add shipping information. The BB_BASKETSTATUS table lists events for each order so that a shopper can see the status, date, and comments as each stage of the order process is finished. The IDSTAGE column of the BB_BASKETSTATUS table identifies each stage; the value 3 in this column indicates that an order has been shipped.
The procedure should allow adding a row with an IDSTAGE of 3, date shipped, tracking number and shipper. The BB_STATUS_SEQ sequence is used to provide a value for the primary key column. Test the procedure with the following information:
o Basket # = 3
o Date shipped = 20-FEB-12
o Shipper = UPS
o Tracking # = ZW2384YXK4957
create or replace PROCEDURE STATUS_SHIP_SP
(p_id IN OUT BB_BASKETSTATUS.IDSTATUS%TYPE)
IS
BEGIN
p_id := BB_STATUS_SEQ.NEXTVAL;
INSERT INTO BB_BASKETSTATUS (IDSTATUS, IDBASKET, IDSTAGE, DTSTAGE, NOTES, SHIPPER, SHIPPINGNUM)
VALUES (p_id, '3', '3', '20-FEB-12', NULL, 'UPS', 'ZW2384YXK4957');
COMMIT;
END;
Question 2. Create a procedure that returns the most recent order status information for a specified basket. This procedure should determine the most recent ordering-stage entry in the BB_BASKETSTATUS table and return the data. Use an IF OR CASE clause to return a stage description instead of an IDSTAGE number, which means little to shoppers. The IDSTAGE column of the BB_BASKETSTATUS table identifies each stage as follows:
o 1-Submitted and received
o 2-Confirmed, processed, sent to shipping
o 3-Shipped
o 4-Cancelled
o 5-Back-ordered
The procedure should accept a basket ID number and return the most recent status description and date the status was recorded. If no status is available for the specified basket ID, return a message stating that no status is available. Name the procedure STATUS_SP. Test the procedure twice with the basket ID 4 and then 6.
CREATE OR REPLACE PROCEDURE STATUS_SP
(
P_RECENT_ORDER IN NUMBER,
p_stat_desc OUT LONG,
p_date OUT DATE
) AS
BEGIN
SELECT BB_BASKETSTATUS.IDBASKET,
BB_BASKETSTATUS.IDSTAGE,
BB_BASKETSTATUS.DTSTAGE
FROM BB_BASKETSTATUS
WHERE BB_BASKETSTATUS.IDBASKET = &P_RECENT_ORDER AND
BB_BASKETSTATUS.DTSTAGE = (SELECT MAX(DISTINCT BB_BASKETSTATUS.DTSTAGE)
FROM BB_BASKETSTATUS
WHERE BB_BASKETSTATUS.IDBASKET = P_RECENT_ORDER);
BEGIN
p_date := BB_BASKETSTATUS.DTSTATUS;
CASE
WHEN BB_BASKETSTATUS.IDSTAGE = 1 THEN p_stat_desc := 'Submitted and recieved';
WHEN BB_BASKETSTATUS.IDSTAGE = 2 THEN p_stat_desc := 'Confirmed, processed, and shipping';
WHEN BB_BASKETSTATUS.IDSTAGE = 3 THEN p_stat_desc := 'Shipped';
WHEN BB_BASKETSTATUS.IDSTAGE = 4 THEN p_stat_desc := 'Cancelled';
WHEN BB_BASKETSTATUS.IDSTAGE = 5 THEN p_stat_desc := 'Back-ordered';
END CASE;
DBMS_OUTPUT.PUT_LINE('Your most recent is: ' || p_stat_desc
|| ' , updated on:' || p_date);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No status is available');
END STATUS_SP;
Question 3. As a shopper selects products on the Brewbeans' site, a procedure is needed to add a newly selected item to the current shopper's basket. Create a procedure named BASKET_ADD_SP that accepts a product ID, basket ID, price, quantity, size code option (1 or 2), and form code option (3 or 4) and uses this information to add a new item to the BB_BASKETITEM table. The table's PRIMARY KEY column is generated by BB_IDBASKETITEM_SEQ. Run the procedure with the following values:
o Basket ID-14
o Product ID-8az
o Price-10.80
o Quantity-1
o Size code-2
o Form code-4
Question 4. The home page of the Brewbeans' Web site has an option for members to log on with their IDs and passwords. Develop a procedure named MEMBER_CK_SP that accepts the ID and password as inputs, checks whether they make up a valid logon and returns the member name and cookie value. The name should be returned as a single text string containing the first and last name.
The head developer wants the number of parameters minimized so that the same parameter is used to accept the password and return the name value. In addition, if the user does not enter a valid username and password, return the value INVALID in a parameter named p_check. Test the procedure using a valid logon first, with the username rat55 and password kile. Then try it with an invalid logon by changing the username to rat.