Name_____________________________

Advanced Databases Exam 4: In class programming task 5-1-2014

Your task is to right a program that identifies a high value customer in three steps:

1)      Identify and rank (by date) the three individuals who made a purchase most recently (procedure).

2)      Identify and rank (by total amount) the three individuals who spent the most money (procedure).

3)      Return the high value customer target by following the algorithm described below (function).

Create a package called hivalcus_pkg.

Create a procedure called mostrecent_pp.  It should have three parameters: p_firstplace, p_secondplace, p_thirdplace.

Create a procedure called bigspender_pp.  It should have three parameters: p_firstplace, p_secondplace, p_thirdplace.

Create a function called targetcus_pf.  It should take in six parameters: p_firstinspent, p_secondinspent, p_thirdinspent, p_mostrecent, p_morerecent, p_recent.

Algorithm: use three variables to track total scores for customers in targetcus_pf function.  Set the variable associated with the customer who made the most recent purchase to an initial value of 3; the second most with the value of 2; and the third most with the value of 1.  Add 3 to the customer score who spent the most money.  Return the customer with the highest score.

Use two private cursors to fetch the results for the two procedures (mostrecent_pp, bigspender_pp)

Write an anonymous block of code to:

• call the two procedures and print the results with labels
• call the function and print the results with label
• Finally fetch and print out the customers first and last name