SQL database

Question

 Write a script that creates and calls a stored procedure named spUpdateProductDiscount that updates the DiscountPercent column in the Products table. This procedure should have one parameter for the product ID and another for the discount percent. If the value for the DiscountPercent column is a negative number, the stored procedure should raise an error that indicates that the value for this column must be a positive number. Code at least two EXEC statements that test this procedure.

Answer 1

CREATE OR REPLACE PROCEDURE spUpdateProductDiscount (

in_ProductID NUMBER,

in_DiscountPercent NUMBER)

AS

BEGIN

IF in_DiscountPercent < 0 THEN

RAISE_APPLICATION_ERROR (-10000, 'We do not accept negative Discount Percent.');

END IF;

UPDATE PRODUCTS SET DiscountPercent = in_DiscountPercent

WHERE ProductId = in_ProductID;

END;

EXEC spUpdateProductDiscount (1, .20);

EXEC spUpdateProductDiscount (1, -.20);