Create Stored Procedures SQL

Question 1

find_product (productId IN NUMBER,

price OUT products.list_price%TYPE,

productName OUT products.product_name%TYPE);

This procedure has an input parameter to receive the product ID and an output parameter named price.

This procedure looks for the given product ID in the database. If the product exists, it stores the product's product_name in the variable productName and the product's list_price in the variable price. If the product does not exist, the productName is set to null and the price variable is set to 0.

In November and December, the company gives 10% off on all products in categories 2 and 5. Before storing the list price of products that belong to categories 2 and 5 into the variable price, calculate the new list price if the current month is November or December and send back the new list price to the caller as price.

EXCEPTION

WHEN no_data_found THEN

price := 0;

To check if your query in the find_product()procedure returns multiple rows, you need to check the too_many_rows exception in the EXCEPTION block and display a proper message.

To catch any other errors, check the OTHERS exception in the EXCEPTION block. and display a proper message.

Question 2

generate_order_id ()

This is an Oracle function with no parameters.It finds the maximum order ID in the orders table and increase it by 1 as new order ID. The function returns the new order ID to the caller.

Question 3

add_order_item (orderId IN order_items.order_id%type, itemId IN order_items.item_id%type, productId IN order_items.product_id%type, quantity IN order_items.quantity%type, price IN order_items.unit_price%type)

This procedure has five IN parameters. It stores the values of these parameters to the table order_items.