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.static/image/hrline/1.gif
Expert Solution
arrow_forward
Step 1
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.
arrow_forward
Step 2
CREATE OR REPLACE FUNCTION totalSpending
RETURN number IS
finalspending number(2) := 0;
DECLARE
id integer := 0;
shipping_amount integer := 0;
tax_amount integer := 0;
total_spending integer:=0;
year_var integer := 0;
BEGIN
ACCEPT x number prompt "Enter shopper ID: ";
id := &x;
ACCEPT y number prompt "Enter year: ";
year_var := &y;
SELECT SUM(shipping_amount) INTO shipping_amount
FROM table_name
WHERE shopper_id = id AND year = year_var;
SELECT SUM(tax_amount) INTO tax_amount
FROM table_name
WHERE shopper_id = id AND year = year_var;
SELECT SUM(spending) INTO total_spending
FROM table_name
WHERE shopper_id = id AND year = year_var;
finalSpending := total_spending - (shipping_amount + tax_amount);
return finalSpending;
EXCEPTION:
WHEN no_data_found then:
dbms_output.put_line("No such shopper")
WHEN others then:
dbms_output.put_line("Error")
END;
BEGIN
finalSpending := totalSpending();
dbms_output.put_line(finalSpending);
END;
/
页:
[1]