Oracle 11g PL/SQL Programming When a product is placed on sale, Brewbean’s recor

Question

Oracle 11g PL/SQL Programming

When a product is placed on sale, Brewbean’s records the sale’s start and end dates in columns of the BB_PRODUCT table. A function is needed to provide sales information when a shopper selects an item. If a product is on sale, the function should return the value ON SALE!. However, if it isn’t on sale, the function should return the value Great Deal!. These values are used on the product display page. Create a function named CK_SALE_SF that accepts a date and product ID as arguments, checks whether the date falls within the product’s sale period, and returns the corresponding string value. Test the function with the product ID 6 and two dates: 10-JUN-12 and 19-JUN-12. Verify your results by reviewing the product sales information.

Explanation / Answer

CREATE FUNCTION CK_SALE_SF

(PID IN INT,

S_DATE IN DATE,

RESULT OUT VARCHAR)

RETURN VARCHAR IS

BEGIN

IF

SELECT RESULT = ‘ON SALES’ FROM BB_PRODUCT WHERE START_DATE >= S_DATE AND END_DATE<= S_DATE AND ID = PID;

RETURN RESULT;

ELSE

RETURN 'GREAT DEAL’;

END IF

END;

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

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