Trust Coding : Programming: PL/SQL Stored Procedure Example: Get Order Details

PL/SQL Stored Procedure: Get Order Details by Order_ID

📌 Question

Consider a table Orders(order_id, customer_id, order_date, total_amount).

Write a stored procedure named GetOrderDetails that:

Accepts an order_id as input.
Retrieves and displays the customer_id, order_date, and total_amount for that order.
If the order_id does not exist, display the message: "Order Not Found".

✅ Answer

Stored Procedure Code

CREATE OR REPLACE PROCEDURE GetOrderDetails(p_order_id IN NUMBER) IS
   v_customer_id   Orders.customer_id%TYPE;
   v_order_date    Orders.order_date%TYPE;
   v_total_amount  Orders.total_amount%TYPE;
BEGIN
   -- Try to fetch the order details
   SELECT customer_id, order_date, total_amount
   INTO v_customer_id, v_order_date, v_total_amount
   FROM Orders
   WHERE order_id = p_order_id;

   -- Display the result
   DBMS_OUTPUT.PUT_LINE('Order ID: ' || p_order_id ||
                        ', Customer ID: ' || v_customer_id ||
                        ', Order Date: ' || v_order_date ||
                        ', Total Amount: ' || v_total_amount);

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Order Not Found');
END;
/

📖 Explanation

Input Parameter: p_order_id is provided when calling the procedure.
SELECT INTO: Retrieves order details into local variables.
DBMS_OUTPUT.PUT_LINE: Prints details if found.
EXCEPTION (NO_DATA_FOUND): Handles cases where the order does not exist and prints "Order Not Found".

📌 Example Execution

SET SERVEROUTPUT ON;
EXEC GetOrderDetails(101);

Output Example (if order exists):

Order ID: 101, Customer ID: C05, Order Date: 12-JAN-25, Total Amount: 15000

Output Example (if order not found):

Order Not Found

📌 Final Summary

The stored procedure GetOrderDetails accepts an order_id as input.
It retrieves and displays the order’s customer_id, order_date, and total_amount.
If the order_id does not exist, it prints "Order Not Found".
This approach ensures clean error handling and user-friendly messages in PL/SQL applications.

Trust Coding : Programming

PL/SQL Stored Procedure Example: Get Order Details