SQL database

Question

 Write a script that creates and calls a stored procedure named spInsertProduct that inserts a row into the Products table. This stored procedure should accept five parameters. One parameter for each of these columns: CategoryID, ProductCode, ProductName, ListPrice, and DiscountPercent. If the value for the ListPrice column is a negative number, the stored procedure should raise an error that indicates that this column doesn’t accept negative numbers. Similarly, the procedure should raise an error if the value for the DiscountPercent column is a negative number. When inserting data into the Products table, set the Description column to an empty string and set the DateAdded column to the current date. Code at least two EXEC statements that test this procedure (one successful, one failure)

Answer 1

CREATE OR REPLACE PROCEDURE spInsertProduct (

in_CategoryID NUMBER,

in_ProductCode VARCHAR,

in_ProductName VARCHAR,

in_ListPrice NUMBER,

in_DiscountPercent NUMBER)

AS

BEGIN

IF in_ListPrice < 0 THEN

RAISE_APPLICATION_ERROR (-10000, 'We do not accept negative List Price.');

END IF;

IF in_DiscountPercent < 0 THEN

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

END IF;

INSERT INTO PRODUCTS (CategoryID, ProductCode, ProductName, ListPrice, DiscountPercent, Description, DateAdded)

VALUES (in_CategoryID, in_ProductCode, in_ProductName, in_ListPrice, in_DiscountPercent, NULL, sysdate);

END;

Script:

EXEC spInsertProduct (1, 'A', 'Nike Air Jordans', 100, .20);

EXEC spInsertProduct (1, 'A', 'Nike Air Jordans', -100, .20);

EXEC spInsertProduct (1, 'A', 'Nike Air Jordans', 100, -.20);