Trust Coding : Programming: PL/SQL Stored Procedure Q31 – Safe Delete Product with Exception Handling

📘 PL/SQL Stored Procedure Lab Exam – 30 Questions (Sample Schema)

Practice these 30 stored procedure questions based on the sample database schema for Product, Customer, Orders, Invoice, and InvoiceDetails.

🔹 Sample Database Schema

-- Product Table
CREATE TABLE Product (
  ProductID NUMBER PRIMARY KEY,
  ProductName VARCHAR2(50),
  CategoryID NUMBER,
  Price NUMBER
);

-- Customer Table
CREATE TABLE Customer (
  CustomerID NUMBER PRIMARY KEY,
  CustomerName VARCHAR2(50),
  CategoryID NUMBER
);

-- Orders Table
CREATE TABLE Orders (
  OrderID NUMBER PRIMARY KEY,
  CustomerID NUMBER REFERENCES Customer(CustomerID),
  OrderDate DATE,
  OrderType VARCHAR2(20),
  Status VARCHAR2(20)
);

-- Invoice Table
CREATE TABLE Invoice (
  InvoiceID NUMBER PRIMARY KEY,
  OrderID NUMBER REFERENCES Orders(OrderID),
  TotalAmount NUMBER,
  PaymentStatus VARCHAR2(20),
  Discount NUMBER
);

-- InvoiceDetails Table
CREATE TABLE InvoiceDetails (
  InvoiceID NUMBER REFERENCES Invoice(InvoiceID),
  ProductID NUMBER REFERENCES Product(ProductID),
  Quantity NUMBER
);

🔹 PL/SQL Stored Procedure Questions (Q1–Q30)

Create a procedure to display all products in the Product table.
Create a procedure with an input parameter to display products of a specific category.
Create a procedure to insert a new customer into the Customer table.
Create a procedure to update product price by a given percentage.
Create a procedure to delete a product based on ProductID.
Create a procedure to display all orders of a specific customer using CustomerID as input.
Create a procedure to calculate total invoice amount for a given customer.
Create a procedure to insert a new order with order details.
Create a procedure to update order status to 'Completed'.
Create a procedure to calculate the total quantity sold for a specific product.
Create a procedure with exception handling for inserting duplicate ProductID.
Create a procedure to display invoices with a discount greater than a given value.
Create a procedure to generate a report of customers with multiple orders.
Create a procedure to display top 5 selling products.
Create a procedure with IN and OUT parameters to return total invoice amount for a customer.
Create a procedure to list all unpaid invoices.
Create a procedure to apply a discount to all invoices of a customer.
Create a procedure to delete orders that are canceled.
Create a procedure to display products in a price range using IN parameters.
Create a procedure to display orders within a date range.
Create a procedure to calculate average invoice amount for a customer.
Create a procedure to update customer category based on total purchases.
Create a procedure to insert multiple products in a loop.
Create a procedure to display orders grouped by order type.
Create a procedure to check if a product exists before inserting a new order.
Create a procedure to display invoice details including product names and quantities.
Create a procedure to calculate grand total of all invoices.
Create a procedure to update payment status of multiple invoices in a loop.
Create a procedure with nested procedure to calculate invoice totals per order.
Create a procedure to generate a summary report of customers, orders, and total invoice amounts.
Create a procedure to handle exceptions when deleting a product that has related orders.

🔹 PL/SQL Stored Procedure Questions with Solutions

-- Q1: Display all products
CREATE OR REPLACE PROCEDURE show_all_products AS
BEGIN
  FOR rec IN (SELECT * FROM Product) LOOP
    DBMS_OUTPUT.PUT_LINE('ID: ' || rec.ProductID || ', Name: ' || rec.ProductName || ', Price: ' || rec.Price);
  END LOOP;
END;
/
-- Explanation: A simple cursor FOR loop fetches all product records and displays them using DBMS_OUTPUT.

-- Q2: Display products of a specific category
CREATE OR REPLACE PROCEDURE show_products_by_category(p_cat NUMBER) AS
BEGIN
  FOR rec IN (SELECT * FROM Product WHERE CategoryID = p_cat) LOOP
    DBMS_OUTPUT.PUT_LINE('ID: ' || rec.ProductID || ', Name: ' || rec.ProductName || ', Price: ' || rec.Price);
  END LOOP;
END;
/
-- Explanation: Uses an input parameter (p_cat) to filter products by category.

-- Q3: Insert a new customer
CREATE OR REPLACE PROCEDURE add_customer(p_name VARCHAR2, p_category NUMBER) AS
BEGIN
  INSERT INTO Customer(CustomerID, CustomerName, CategoryID)
  VALUES(Customer_seq.NEXTVAL, p_name, p_category);
  COMMIT;
END;
/
-- Explanation: Inserts a new customer using parameters and commits the transaction.

-- Q4: Update product price by percentage
CREATE OR REPLACE PROCEDURE update_product_price(p_id NUMBER, p_percent NUMBER) AS
BEGIN
  UPDATE Product SET Price = Price + (Price * p_percent / 100) WHERE ProductID = p_id;
  COMMIT;
END;
/
-- Explanation: Updates a product's price based on a given percentage.

-- Q5: Delete product by ProductID
CREATE OR REPLACE PROCEDURE delete_product(p_id NUMBER) AS
BEGIN
  DELETE FROM Product WHERE ProductID = p_id;
  COMMIT;
END;
/
-- Explanation: Deletes the product with the given ProductID.

-- Q6: Display all orders for a customer
CREATE OR REPLACE PROCEDURE show_orders_by_customer(p_cid NUMBER) AS
BEGIN
  FOR rec IN (SELECT * FROM Orders WHERE CustomerID = p_cid) LOOP
    DBMS_OUTPUT.PUT_LINE('OrderID: ' || rec.OrderID || ', Date: ' || rec.OrderDate || ', Status: ' || rec.Status);
  END LOOP;
END;
/
-- Explanation: Fetches all orders for a specific customer using a parameter.

-- Q7: Calculate total invoice for a customer
CREATE OR REPLACE PROCEDURE total_invoice_customer(p_cid NUMBER, p_total OUT NUMBER) AS
BEGIN
  SELECT SUM(i.TotalAmount) INTO p_total
  FROM Invoice i
  JOIN Orders o ON i.OrderID = o.OrderID
  WHERE o.CustomerID = p_cid;
END;
/
-- Explanation: Uses an OUT parameter to return total invoice amount for a customer.

-- Q8: Insert new order with details
CREATE OR REPLACE PROCEDURE add_order(p_cid NUMBER, p_type VARCHAR2, p_status VARCHAR2, p_productid NUMBER, p_qty NUMBER) AS
  v_orderid NUMBER;
  v_invoiceid NUMBER;
BEGIN
  INSERT INTO Orders(OrderID, CustomerID, OrderDate, OrderType, Status)
  VALUES(Order_seq.NEXTVAL, p_cid, SYSDATE, p_type, p_status)
  RETURNING OrderID INTO v_orderid;

  INSERT INTO Invoice(InvoiceID, OrderID, TotalAmount, PaymentStatus, Discount)
  VALUES(Invoice_seq.NEXTVAL, v_orderid, 0, 'Pending', 0)
  RETURNING InvoiceID INTO v_invoiceid;

  INSERT INTO InvoiceDetails(InvoiceID, ProductID, Quantity)
  VALUES(v_invoiceid, p_productid, p_qty);

  COMMIT;
END;
/
-- Explanation: Inserts an order, creates an invoice, and adds product details.

-- Q9: Update order status to 'Completed'
CREATE OR REPLACE PROCEDURE complete_order(p_oid NUMBER) AS
BEGIN
  UPDATE Orders SET Status = 'Completed' WHERE OrderID = p_oid;
  COMMIT;
END;
/
-- Explanation: Updates the status of a specific order.

-- Q10: Total quantity sold for a product
CREATE OR REPLACE PROCEDURE total_quantity_product(p_pid NUMBER, p_total OUT NUMBER) AS
BEGIN
  SELECT SUM(Quantity) INTO p_total FROM InvoiceDetails WHERE ProductID = p_pid;
END;
/
-- Explanation: Uses an OUT parameter to calculate total quantity sold.

-- Q11: Exception handling for duplicate ProductID
CREATE OR REPLACE PROCEDURE insert_product(p_id NUMBER, p_name VARCHAR2, p_price NUMBER) AS
BEGIN
  INSERT INTO Product(ProductID, ProductName, Price) VALUES(p_id, p_name, p_price);
  COMMIT;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('ProductID already exists.');
END;
/
-- Explanation: Handles duplicate primary key exception during insert.

-- Q12: Display invoices with discount > value
CREATE OR REPLACE PROCEDURE invoices_with_discount(p_discount NUMBER) AS
BEGIN
  FOR rec IN (SELECT * FROM Invoice WHERE Discount > p_discount) LOOP
    DBMS_OUTPUT.PUT_LINE('InvoiceID: ' || rec.InvoiceID || ', Total: ' || rec.TotalAmount || ', Discount: ' || rec.Discount);
  END LOOP;
END;
/
-- Explanation: Fetches invoices exceeding a discount threshold.

-- Q13: Report of customers with multiple orders
CREATE OR REPLACE PROCEDURE customers_multiple_orders AS
BEGIN
  FOR rec IN (SELECT CustomerID, COUNT(*) AS orders_count FROM Orders GROUP BY CustomerID HAVING COUNT(*) > 1) LOOP
    DBMS_OUTPUT.PUT_LINE('CustomerID: ' || rec.CustomerID || ', Orders: ' || rec.orders_count);
  END LOOP;
END;
/
-- Explanation: Uses GROUP BY and HAVING to identify customers with multiple orders.

-- Q14: Top 5 selling products
CREATE OR REPLACE PROCEDURE top_selling_products AS
BEGIN
  FOR rec IN (SELECT ProductID, SUM(Quantity) AS total_sold FROM InvoiceDetails GROUP BY ProductID ORDER BY total_sold DESC FETCH FIRST 5 ROWS ONLY) LOOP
    DBMS_OUTPUT.PUT_LINE('ProductID: ' || rec.ProductID || ', Sold: ' || rec.total_sold);
  END LOOP;
END;
/
-- Explanation: Aggregates sales per product and fetches top 5.

-- Q15: Procedure with IN and OUT parameters for total invoice
CREATE OR REPLACE PROCEDURE invoice_total(p_cid NUMBER, p_total OUT NUMBER) AS
BEGIN
  SELECT SUM(i.TotalAmount) INTO p_total
  FROM Invoice i JOIN Orders o ON i.OrderID = o.OrderID
  WHERE o.CustomerID = p_cid;
END;
/
-- Explanation: Returns total invoice using OUT parameter.

-- Q16: List unpaid invoices
CREATE OR REPLACE PROCEDURE unpaid_invoices AS
BEGIN
  FOR rec IN (SELECT * FROM Invoice WHERE PaymentStatus='Pending') LOOP
    DBMS_OUTPUT.PUT_LINE('InvoiceID: ' || rec.InvoiceID || ', Total: ' || rec.TotalAmount);
  END LOOP;
END;
/
-- Explanation: Displays all invoices with PaymentStatus 'Pending'.

-- Q17: Apply discount to all invoices of a customer
CREATE OR REPLACE PROCEDURE apply_discount(p_cid NUMBER, p_discount NUMBER) AS
BEGIN
  UPDATE Invoice i
  SET Discount = p_discount
  WHERE i.OrderID IN (SELECT OrderID FROM Orders WHERE CustomerID = p_cid);
  COMMIT;
END;
/
-- Explanation: Updates discount for all invoices of a specific customer.

-- Q18: Delete canceled orders
CREATE OR REPLACE PROCEDURE delete_canceled_orders AS
BEGIN
  DELETE FROM Orders WHERE Status='Canceled';
  COMMIT;
END;
/
-- Explanation: Removes all canceled orders from the Orders table.

-- Q19: Display products in price range
CREATE OR REPLACE PROCEDURE products_in_range(p_min NUMBER, p_max NUMBER) AS
BEGIN
  FOR rec IN (SELECT * FROM Product WHERE Price BETWEEN p_min AND p_max) LOOP
    DBMS_OUTPUT.PUT_LINE('ProductID: ' || rec.ProductID || ', Name: ' || rec.ProductName || ', Price: ' || rec.Price);
  END LOOP;
END;
/
-- Explanation: Filters products within the given price range.

-- Q20: Display orders within date range
CREATE OR REPLACE PROCEDURE orders_in_date_range(p_start DATE, p_end DATE) AS
BEGIN
  FOR rec IN (SELECT * FROM Orders WHERE OrderDate BETWEEN p_start AND p_end) LOOP
    DBMS_OUTPUT.PUT_LINE('OrderID: ' || rec.OrderID || ', Date: ' || rec.OrderDate);
  END LOOP;
END;
/
-- Explanation: Filters orders based on date range parameters.

-- Q21: Average invoice amount for customer
CREATE OR REPLACE PROCEDURE avg_invoice_customer(p_cid NUMBER, p_avg OUT NUMBER) AS
BEGIN
  SELECT AVG(i.TotalAmount) INTO p_avg
  FROM Invoice i JOIN Orders o ON i.OrderID = o.OrderID
  WHERE o.CustomerID = p_cid;
END;
/
-- Explanation: Calculates average invoice amount using OUT parameter.

-- Q22: Update customer category based on total purchases
CREATE OR REPLACE PROCEDURE update_customer_category(p_cid NUMBER, p_category NUMBER) AS
BEGIN
  UPDATE Customer SET CategoryID = p_category WHERE CustomerID = p_cid;
  COMMIT;
END;
/
-- Explanation: Updates customer category.

-- Q23: Insert multiple products in loop
CREATE OR REPLACE PROCEDURE insert_multiple_products AS
BEGIN
  FOR i IN 1..5 LOOP
    INSERT INTO Product(ProductID, ProductName, Price) VALUES(Product_seq.NEXTVAL, 'Product'||i, 100 + i*10);
  END LOOP;
  COMMIT;
END;
/
-- Explanation: Demonstrates looping to insert multiple products.

-- Q24: Display orders grouped by order type
CREATE OR REPLACE PROCEDURE orders_grouped_by_type AS
BEGIN
  FOR rec IN (SELECT OrderType, COUNT(*) AS cnt FROM Orders GROUP BY OrderType) LOOP
    DBMS_OUTPUT.PUT_LINE('Type: ' || rec.OrderType || ', Count: ' || rec.cnt);
  END LOOP;
END;
/
-- Explanation: Aggregates orders by type.

-- Q25: Check if product exists before inserting order
CREATE OR REPLACE PROCEDURE check_product_before_order(p_pid NUMBER) AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM Product WHERE ProductID = p_pid;
  IF v_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Product does not exist.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Product exists, proceed.');
  END IF;
END;
/
-- Explanation: Validates product existence using COUNT.

-- Q26: Display invoice details with product names
CREATE OR REPLACE PROCEDURE invoice_details_with_products AS
BEGIN
  FOR rec IN (SELECT i.InvoiceID, p.ProductName, d.Quantity
              FROM Invoice i
              JOIN InvoiceDetails d ON i.InvoiceID = d.InvoiceID
              JOIN Product p ON d.ProductID = p.ProductID) LOOP
    DBMS_OUTPUT.PUT_LINE('InvoiceID: ' || rec.InvoiceID || ', Product: ' || rec.ProductName || ', Qty: ' || rec.Quantity);
  END LOOP;
END;
/
-- Explanation: Joins tables to display detailed invoice info.

-- Q27: Calculate grand total of all invoices
CREATE OR REPLACE PROCEDURE grand_total_invoices(p_total OUT NUMBER) AS
BEGIN
  SELECT SUM(TotalAmount) INTO p_total FROM Invoice;
END;
/
-- Explanation: Aggregates all invoice totals using OUT parameter.

-- Q28: Update payment status of multiple invoices in loop
CREATE OR REPLACE PROCEDURE update_payment_status AS
BEGIN
  FOR rec IN (SELECT InvoiceID FROM Invoice WHERE PaymentStatus='Pending') LOOP
    UPDATE Invoice SET PaymentStatus='Paid' WHERE InvoiceID=rec.InvoiceID;
  END LOOP;
  COMMIT;
END;
/
-- Explanation: Iterates over pending invoices to update status.

-- Q29: Nested procedure to calculate invoice totals per order
CREATE OR REPLACE PROCEDURE nested_invoice_totals AS
  PROCEDURE calc_total(p_oid NUMBER, p_total OUT NUMBER) IS
  BEGIN
    SELECT SUM(Quantity*Price) INTO p_total
    FROM InvoiceDetails d JOIN Product p ON d.ProductID = p.ProductID
    WHERE d.InvoiceID = p_oid;
  END;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Nested procedure defined for invoice totals.');
END;
/
-- Explanation: Demonstrates nested procedure usage.

-- Q30: Summary report of customers, orders, and total invoice
CREATE OR REPLACE PROCEDURE customer_order_summary AS
BEGIN
  FOR rec IN (SELECT c.CustomerID, COUNT(o.OrderID) AS orders_count, SUM(i.TotalAmount) AS total_invoice
              FROM Customer c
              LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
              LEFT JOIN Invoice i ON o.OrderID = i.OrderID
              GROUP BY c.CustomerID) LOOP
    DBMS_OUTPUT.PUT_LINE('CustomerID: ' || rec.CustomerID || ', Orders: ' || rec.orders_count || ', Total: ' || rec.total_invoice);
  END LOOP;
END;
/
-- Explanation: Generates a comprehensive summary report.

🔹 Q31: Delete Product with Exception Handling

CREATE OR REPLACE PROCEDURE delete_product_safe(p_id NUMBER) AS
BEGIN
  DELETE FROM Product WHERE ProductID = p_id;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Product deleted successfully.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Cannot delete product. It may have related orders.');
END;
/
-- Explanation: 
-- Attempts to delete a product. If the product is referenced in Orders or InvoiceDetails, 
-- the foreign key constraint will raise an exception. 
-- This is caught in the EXCEPTION block to prevent procedure failure and display a clear message.

Trust Coding : Programming

PL/SQL Stored Procedure Q31 – Safe Delete Product with Exception Handling

📘 PL/SQL Stored Procedure Lab Exam – 30 Questions (Sample Schema)

🔹 Sample Database Schema

🔹 PL/SQL Stored Procedure Questions (Q1–Q30)

🔹 PL/SQL Stored Procedure Questions with Solutions

🔹 Q31: Delete Product with Exception Handling

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন