Create a function to calculate a shopper’s total spending

Create a function to calculate a shopper’s total spending, excluding shipping and tax amount, with Brewbean’s site in a particular year. Exception handling is needed. Use an anonymous block to call the function and output the result.

1. Expert Solution
   
2. arrow_forward
   
3. Step 1
   
4. Below is the function totalSpending to calculate a shopper’s total spending, excluding shipping and tax amount, with Brewbean’s site in a particular year.
   
5. 
   
6. arrow_forward
   
7. Step 2
   
8. CREATE OR REPLACE FUNCTION totalSpending
   
9. 
   
10. RETURN number IS
    
11. 
    
12. finalspending number(2) := 0;
    
13. 
    
14. DECLARE
    
15. 
    
16. id integer := 0;
    
17. 
    
18. shipping_amount integer := 0;
    
19. 
    
20. tax_amount integer := 0;
    
21. 
    
22. total_spending integer  :=0;
    
23. 
    
24. year_var integer := 0;
    
25. 
    
26. BEGIN
    
27. 
    
28. ACCEPT x number prompt "Enter shopper ID: ";
    
29. 
    
30. id := &x;
    
31. 
    
32. ACCEPT y number prompt "Enter year: ";
    
33. 
    
34. year_var := &y;
    
35. 
    
36. SELECT SUM(shipping_amount) INTO shipping_amount
    
37. 
    
38. FROM table_name
    
39. 
    
40. WHERE shopper_id = id AND year = year_var;
    
41. 
    
42. SELECT SUM(tax_amount) INTO tax_amount
    
43. 
    
44. FROM table_name
    
45. 
    
46. WHERE shopper_id = id AND year = year_var;
    
47. 
    
48. SELECT SUM(spending) INTO total_spending
    
49. 
    
50. FROM table_name
    
51. 
    
52. WHERE shopper_id = id AND year = year_var;
    
53. 
    
54. finalSpending := total_spending - (shipping_amount + tax_amount);
    
55. 
    
56. return finalSpending;
    
57. 
    
58. EXCEPTION:
    
59. 
    
60. WHEN no_data_found then:
    
61. 
    
62. dbms_output.put_line("No such shopper")
    
63. 
    
64. WHEN others then:
    
65. 
    
66. dbms_output.put_line("Error")
    
67. 
    
68. END;
    
69. 
    
70. BEGIN
    
71. 
    
72. finalSpending := totalSpending();
    
73. 
    
74. dbms_output.put_line(finalSpending);
    
75. 
    
76. END;
    
77. 
    
78. /
    
79. 
    

复制代码