Question

Oracle -

1. Create a procedure that accepts product ID as a parameter and returns the name of the product from ProductTable table. Add exception handling to catch if product ID is not in the table.

Table to use:

CREATE TABLE ProductTable(

    ProductID INTEGER NOT NULL primary key,

    ProductName VARCHAR(50) NOT NULL,

    ListPrice NUMBER(10,2),

    Category INTEGER NOT NULL

);

/

INSERT INTO ProductTable VALUES(299,'Chest',99.99,10);

INSERT INTO ProductTable VALUES(300,'Wave Cruiser',49.99,11);

INSERT INTO ProductTable VALUES(301,'Megaland Play Tent',59.99,11);

INSERT INTO ProductTable VALUES(302,'Wind-Up Water Swimmers',2.00,11);

INSERT INTO ProductTable VALUES(303,'Garmin Pocket or Vehicle GPS Navigator',609.99,12);

Answer 1

Database Used :Oracle Live SQL.

Stored Procedure :

CREATE OR REPLACE PROCEDURE getProduct(
prodID IN ProductTable.ProductID%type,
ProdName OUT ProductTable.ProductName%type)
AS
BEGIN
SELECT ProductName INTO ProdName FROM ProductTable WHERE ProductID=prodID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('Enter valid product ID');
END;
/

Executing stored procedure :

declare
ProductName varchar(50);
begin
getProduct(303, ProductName);
dbms_output.put_line(ProductName);
end;
/

Screen in Oracle Live SQL when product id is valid  :

Screen in Oracle Live SQL when product id is invalid  :