E-commerce Order Processing with PL/SQL Procedure

intermediate

E-commerce platforms process orders with multiple steps: validate inventory, calculate discount, apply tax, create order record, update inventory. Doing this with separate SQL statements requires 5+ round trips. A PL/SQL procedure does everything in one call (5x faster).

-- Order processing procedure

CREATE OR REPLACE PROCEDURE process_order(

p_customer_id IN NUMBER,

p_product_id IN NUMBER,

p_quantity IN NUMBER,

p_order_id OUT NUMBER,

p_final_amount OUT NUMBER,

p_status OUT VARCHAR2

) AS

v_product_price NUMBER;

v_available_qty NUMBER;

v_customer_tier VARCHAR2(20);

v_subtotal NUMBER;

v_discount NUMBER;

v_tax NUMBER;

insufficient_inventory EXCEPTION;

invalid_product EXCEPTION;

BEGIN

-- Step 1: Validate product and get price

BEGIN

SELECT price, stock_quantity

INTO v_product_price, v_available_qty

FROM products

WHERE product_id = p_product_id

FOR UPDATE; -- Lock row

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE invalid_product;

END;

-- Step 2: Check inventory

IF v_available_qty < p_quantity THEN

RAISE insufficient_inventory;

END IF;

-- Step 3: Get customer tier for discount

SELECT tier INTO v_customer_tier

FROM customers

WHERE customer_id = p_customer_id;