Assignment: Updating Columns in a Table
After a shopper completes an order, a procedure is called to update the following columns in the BASKET table: ORDERPLACED, SUBTOTAL, SHIPPING, TAX, and TOTAL. The value 1 entered in the ORDERPLACED column indicates that the shopper has completed an order. Inputs to the procedure are the basket ID and amounts for the subtotal, shipping, tax, and total.
1. In SQL Developer, create a procedure named BASKET_CONFIRM_SP that accepts the input values specified in the preceding description. Keep in mind that you're modifying an existing row of the BB_BASKET table in this procedure.
2. Enter the following statements to create a new basket containing two items: INSERT INTO BB_BASKET (IDBASKET, QUANTITY, IDSHOPPER, ORDERPLACED, SUBTOTAL, TOTAL, SHIPPING, TAX, DTCREATED, PROMO)
VALUES (17, 2, 22, 0, 0, 0, 0, 0, '28-FEB-12', 0);
INSERT INTO BB_BASKETITEM (IDBASKETITEM, IDPRODUCT, PRICE, QUANTITY, IDBASKET, OPTION1, OPTION2)
VALUES (44, 7, 10.8, 3, 17, 2, 3);
INSERT INTO BB_BASKETITEM (IDBASKETITEM, IDPRODUCT, PRICE, QUANTITY, IDBASKET, OPTION1, OPTION2)
VALUES (45, 8, 10.8, 3, 17, 2, 3);
3. Type and run COMMIT; to save the data from these statements.
4. Call the procedure with the following parameter values: 17, 64.80, 8.00, 1.94, 74.74. As mentioned, these values represent the basket ID and the amounts for the subtotal, shipping, tax, and total.
5. Query the BB_BASKET table to confirm that the procedure was successful: SELECT subtotal, shipping, tax, total, orderplaced FROM bb_basket WHERE idbasket = 17; .
6. 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: -1-Submitted and received -2-Confirmed, processed, sent to shipping -3-Shipped -4-Cancelled -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.
7. Brewbean's wants to offer an incentive of free shipping to customers who haven't returned to the site since a specified date. Create a procedure named PROMO_SHIP_SP that determines who these customers are and then updates the BB_PROMOLIST table accordingly. The procedure uses the following information:
-Date cutoff-Any customers who haven't shopped on the site since this date should be included as incentive participants. Use the basket creation date to reflect shopper activity dates.
-Month-A three-character month (such as APR) should be added to the promotion table to indicate which month free shipping is effective.
-Year-A four-digit year indicates the year the promotion is effective. -promo_flag-1 represents free shipping.
The BB_PROMOLIST table also has a USED column, which contains the default value N and is updated to Y when the shopper uses the promotion. Test the procedure with the cutoff date 15-FEB-12. Assign free shipping for the month APR and the year 2012.
8. As a shopper selects products on the Brewbean's 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:
-Basket ID-14
-Product ID-8
-Price-10.80
-Quantity-1
-Size code-2
-Form code-4