Assignment #3 – Cursors, Procedures, Functions, Triggers
Due Date:
As per dropbox
Purpose:
The purpose of this assignment is to help you:
·
Become familiar with Cursors, Exceptions, Procedures, Functions and Triggers
Instructions
: Be sure to read the following general instructions carefully:
This assignment should be completed individually by all the students. Submit your solution
through dropbox.
Your submission should include PL/SQL code and the screenshot of the code execution result, the submission must be named according to the following rule:
studentID(yourlastname)_Assignment#number.doc. e.g., 300123456(smith)_Assignment#3.doc
Questions [8 marks]
Answer Questions 1-4 related to
Chapters 5,6,9 of Oracle 11g: PL/SQL Programming.
Your files must be in SQL script format so that they can be run in SQL Developer and have DBMS_OUTPUT.PUT_LINE for presenting results. Must have separate testing codes for all programming units.
Submit code in.txt file.
Marks are and will be entirely based only on the correct result of the code submitted having testing part
.
Q1:
Calculating a Shopper’s Total Number of Orders Another commonly used statistic in reports is the total number of orders a shopper has placed. Follow these steps to create a function named NUM_PURCH_SF that accepts a shopper ID and returns a shopper’s total number of orders. Use the function in a SELECT statement to display the number of orders for shopper 23.
1. Develop and run a CREATE FUNCTION statement to create the NUM_PURCH_SF function. The function code needs to tally the number of orders (using an Oracle built-in function) by shopper. Keep in mind that the ORDERPLACED column contains a 1 if an order has been placed.
2. Create a SELECT query by using the NUM_PURCH_SF function on the IDSHOPPER column of the BB_SHOPPER table. Be sure to select only shopper 23.
Q2:
Identifying the Weekday for an Order Date The day of the week that baskets are created is often analyzed to determine consumershopping patterns. Create a function named DAY_ORD_SF that accepts an order date and returns the weekday. Use the function in a SELECT statement to display each basket ID and the weekday the order was created. Write a second SELECT statement, using this function to display the total number of orders for each weekday. (Hint: Call the TO_CHAR function to retrieve the weekday from a date.)
1. Develop and run a CREATE FUNCTION statement to create the DAY_ORD_SF function. Use the DTCREATED column of the BB_BASKET table as the date the basket is created. Call the TO_CHAR function with the DAY option to retrieve the weekday for a date value.
2. Create a SELECT statement that lists the basket ID and weekday for every basket.
3. Create a SELECT statement, using a GROUP BY clause to list the total number of baskets per weekday. Based on the results, what’s the most popular shopping day?
Q3:
Calculating Days Between Ordering and Shipping An analyst in the quality assurance office reviews the time elapsed between receiving an order and shipping the order. Any orders that haven’t been shipped within a day of the order being placed are investigated. Create a function named ORD_SHIP_SF that calculates the number of days between the basket’s creation date and the shipping date. The function should return a character string that states OK if the order was shipped within a day or CHECK if it wasn’t. If the order hasn’t shipped, return the string Not shipped. The IDSTAGE column of the BB_BASKETSTATUS table indicates a shipped item with the value 5, and the DTSTAGE column is the shipping date. The DTORDERED column of the BB_BASKET table is the order date. Review data in the BB_BASKETSTATUS table and create an anonymous block to test all three outcomes the function should handle.
Q4:
Updating Stock Levels When an Order Is Canceled At times, customers make mistakes in submitting orders and call to cancel an order. Brewbean wants to create a trigger that automatically updates the stock level of all products associated with a cancelled order and updates the ORDERPLACED column of the BB_BASKET table to zero, reflecting that the order wasn’t completed. Create a trigger named BB_ORDCANCEL_TRG to perform this task, taking into account the following points:
• The trigger needs to fire when a new status record is added to the BB_BASKETSTATUS table and when the IDSTAGE column is set to 4, which indicates an order has been cancelled.
• Each basket can contain multiple items in the BB_BASKETITEM table, so a CURSOR FOR loop might be a suitable mechanism for updating each item’s stock level.
• Keep in mind that coffee can be ordered in half or whole pounds.
• Use basket 6, which contains two items, for testing.
1. Run this INSERT statement to test the trigger:
INSERT INTO bb_basketstatus (idStatus, idBasket, idStage, dtStage) VALUES (bb_status_seq.NEXTVAL, 6, 4, SYSDATE);
2. Issue queries to confirm that the trigger has modified the basket’s order status and product stock levels correctly. 3. Be sure to run the following statement to disable this trigger so that it doesn’t affect other assignments:
ALTER TRIGGER bb_ordcancel_trg DISABLE;
Q5:
Processing Discounts Brewbean’s is offering a new discount for return shoppers: Every fifth completed order gets a 10% discount. The count of orders for a shopper is placed in a packaged variable named pv_disc_num during the ordering process. This count needs to be tested at checkout to determine whether a discount should be applied. Create a trigger named BB_DISCOUNT_TRG so that when an order is confirmed (the ORDERPLACED value is changed from 0 to 1), the pv_disc_num packaged variable is checked. If it’s equal to 5, set a second variable named pv_disc_txt to Y. This variable is used in calculating the order summary so that a discount is applied, if necessary. Create a package specification named DISC_PKG containing the necessary packaged variables. Use an anonymous block to initialize the packaged variables to use for testing the trigger. Test the trigger with the following UPDATE statement:
UPDATE bb_basket SET orderplaced = 1 WHERE idBasket = 13;
If you need to test the trigger multiple times, simply reset the ORDERPLACED column to 0 for basket 13 and then run the UPDATE again. Also, disable this trigger when you’re finished so that it doesn’t affect other assignments.