Using PL/SQL, create the exercises below Below is a function that will calculate

Question

Using PL/SQL, create the exercises below

Below is a function that will calculate the total discount based on an order ID. Using the code as a starting template, modify it so that it will return the actual percentage of savings by also calculating the TotalCost and then dividing TotalDiscount by TotalCost to get the percent saved. ?You must calculate TotalCost in the same manner that TotalDiscount is being calculated below, e.g. use the Cursor and variables in the loop.

CREATE OR REPLACE FUNCTION getTotalDiscount

(o_id IN NUMBER)

return NUMBER

IS

TotalDiscount NUMBER := 0;

CURSOR getOrderItems IS

SELECT DISCOUNT_AMOUNT FROM order_items WHERE ORDER_ID = o_id;

BEGIN
FOR items IN getOrderItems LOOP

TotalDiscount := TotalDiscount + items.DISCOUNT_AMOUNT;

END LOOP;
RETURN TotalDiscount;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('Something went wrong...');

END

Explanation / Answer

Hi,

CREATE OR REPLACE FUNCTION getTotalDiscount

(o_id IN NUMBER)

return NUMBER

IS

TotalDiscount NUMBER := 0;

TotalCost NUMBER := 0;

CURSOR getOrderItems IS

SELECT DISCOUNT_AMOUNT FROM order_items WHERE ORDER_ID = o_id;

BEGIN
FOR items IN getOrderItems LOOP

TotalDiscount := TotalDiscount + items.DISCOUNT_AMOUNT;

TotalCost := TotalCost + items.TOTAL_COST;

END LOOP;
RETURN TotalDiscount/TotalCost;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('Something went wrong...');

END

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.