Using Searched CASE Statements

The Brewbeans application needs a block to determine whether a customer is rated HIGH, MID, or LOW based on his or her total purchases. The block needs to select the total amount of orders for a specified customer, determine the rating, and then display the results onscreen. The code rates the customer HIGH if total purchases are greater than $200, MID if greater than $100, and LOW if $100 or lower. Use an initialized variable to provide the shopper ID.

2. Modify the code used in Assignment 3-3 to use a searched CASE statement to check the shoppers total purchase amount and determine the correct rating.

Assignment 3-3 code:

DECLARE lv_total_num NUMBER(6,2); lv_rating_txt VARCHAR2(4); lv_shop_num bb_basket.idshopper%TYPE := 23; BEGIN SELECT SUM(total) INTO lv_total_num FROM bb_basket WHERE idShopper = lv_shop_num AND orderplaced = 1 GROUP BY idshopper; IF lv_total_num > 200 THEN DBMS_OUTPUT.PUT_LINE ('Shopper ' || lv_shop_num || ' is rated HIGH.'); ELSIF lv_total_num>100 THEN DBMS_OUTPUT.PUT_LINE ('Shopper ' || lv_shop_num || ' is rated MID.'); ELSE DBMS_OUTPUT.PUT_LINE ('Shopper ' || lv_shop_num || ' is rated LOW.'); END IF; END;

3. Run the block, and verify the results.