SQL vs. PL/SQL

-- Example of a simplified PL/SQL Stored Procedure
CREATE OR REPLACE PROCEDURE PLACE_ORDER (
    p_customer_id    IN NUMBER,
    p_cart_items     IN SYS.ODCINUMBERLIST, -- An array/list of product IDs
    p_quantities     IN SYS.ODCINUMBERLIST, -- An array/list of quantities
    p_payment_method IN VARCHAR2,
    x_order_id       OUT NUMBER,
    x_status_message OUT VARCHAR2
)
AS
    v_total_amount    DECIMAL(10, 2) := 0;
    v_product_price   DECIMAL(10, 2);
    v_current_stock   NUMBER;
    v_new_order_id    NUMBER;
    v_item_index      NUMBER;
    
    -- Custom exception for insufficient stock
    INSUFFICIENT_STOCK EXCEPTION;
    PRAGMA EXCEPTION_INIT(INSUFFICIENT_STOCK, -20001); -- Assign a custom error number
    
BEGIN
    -- Start a transaction for atomicity
    SAVEPOINT StartOrderPlacement;

    -- 1. Insert new order record
    SELECT NVL(MAX(OrderID), 0) + 1 INTO v_new_order_id FROM Orders;
    INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount, PaymentMethod)
    VALUES (v_new_order_id, p_customer_id, SYSDATE, 0, p_payment_method); -- TotalAmount will be updated later

    -- 2. Loop through cart items to add to OrderDetails and update stock
    v_item_index := p_cart_items.FIRST;
    WHILE v_item_index IS NOT NULL LOOP
        SELECT UnitPrice, CurrentStock INTO v_product_price, v_current_stock FROM Products
        WHERE ProductID = p_cart_items(v_item_index) FOR UPDATE; -- Lock row for update

        IF v_current_stock < p_quantities(v_item_index) THEN
            RAISE INSUFFICIENT_STOCK; -- Raise our custom exception
        END IF;

        -- Insert into OrderDetails
        INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
        VALUES (v_new_order_id, p_cart_items(v_item_index), p_quantities(v_item_index), v_product_price);

        -- Update product stock
        UPDATE Products
        SET CurrentStock = CurrentStock - p_quantities(v_item_index)
        WHERE ProductID = p_cart_items(v_item_index);

        v_total_amount := v_total_amount + (v_product_price * p_quantities(v_item_index));
        
        v_item_index := p_cart_items.NEXT(v_item_index);
    END LOOP;

    -- 3. Update the total amount for the new order
    UPDATE Orders
    SET TotalAmount = v_total_amount
    WHERE OrderID = v_new_order_id;
    
    -- 4. Commit the transaction if all successful
    COMMIT;
    
    x_order_id := v_new_order_id;
    x_status_message := 'Order placed successfully!';

EXCEPTION
    WHEN INSUFFICIENT_STOCK THEN
        ROLLBACK TO StartOrderPlacement; -- Rollback only this transaction
        x_order_id := NULL;
        x_status_message := 'Error: Insufficient stock for one or more items. Order cancelled.';
    WHEN OTHERS THEN
        ROLLBACK; -- Rollback the entire transaction
        x_order_id := NULL;
        x_status_message := 'Error: An unexpected error occurred during order placement: ' || SQLERRM;
        -- Log the error for debugging purposes
        INSERT INTO ErrorLog (ErrorDate, ErrorMessage) VALUES (SYSDATE, SQLERRM);
        COMMIT; -- Commit the error log entry (if desired, depends on logging strategy)

END PLACE_ORDER;
/

-- Assuming `Products` table has a `LastOrderDate` column
ALTER TABLE Products ADD LastOrderDate DATE;

CREATE OR REPLACE TRIGGER TRG_UPDATE_PRODUCT_LAST_ORDER_DATE
AFTER INSERT ON OrderDetails
FOR EACH ROW
BEGIN
    -- Update the Products table with the most recent order date for the product
    UPDATE Products
    SET LastOrderDate = SYSDATE -- Or join with Orders to get the actual OrderDate of the new order
    WHERE ProductID = :NEW.ProductID;
END;
/

CREATE OR REPLACE FUNCTION GET_CUSTOMER_TOTAL_SPEND (
    p_customer_id IN NUMBER
)
RETURN DECIMAL
IS
    v_total_spend DECIMAL(10, 2);
BEGIN
    SELECT NVL(SUM(TotalAmount), 0)
    INTO v_total_spend
    FROM Orders
    WHERE CustomerID = p_customer_id;

    RETURN v_total_spend;
END GET_CUSTOMER_TOTAL_SPEND;
/

CREATE OR REPLACE PACKAGE INVENTORY_MGMT_PKG AS
    -- Procedure to replenish stock for a product
    PROCEDURE REPLENISH_STOCK (
        p_product_id IN NUMBER,
        p_quantity   IN NUMBER,
        x_status     OUT VARCHAR2
    );

    -- Function to check current stock level
    FUNCTION GET_STOCK_LEVEL (
        p_product_id IN NUMBER
    ) RETURN NUMBER;

END INVENTORY_MGMT_PKG;
/

CREATE OR REPLACE PACKAGE BODY INVENTORY_MGMT_PKG AS

    PROCEDURE REPLENISH_STOCK (
        p_product_id IN NUMBER,
        p_quantity   IN NUMBER,
        x_status     OUT VARCHAR2
    )
    IS
    BEGIN
        UPDATE Products
        SET CurrentStock = CurrentStock + p_quantity
        WHERE ProductID = p_product_id;
        
        IF SQL%ROWCOUNT > 0 THEN
            x_status := 'Stock replenished successfully.';
            COMMIT;
        ELSE
            x_status := 'Product ID not found.';
            ROLLBACK;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            x_status := 'Error replenishing stock: ' || SQLERRM;
            ROLLBACK;
    END REPLENISH_STOCK;

    FUNCTION GET_STOCK_LEVEL (
        p_product_id IN NUMBER
    ) RETURN NUMBER
    IS
        v_stock NUMBER;
    BEGIN
        SELECT CurrentStock INTO v_stock
        FROM Products
        WHERE ProductID = p_product_id;
        
        RETURN v_stock;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN NULL; -- Or raise an application-specific error
    END GET_STOCK_LEVEL;

END INVENTORY_MGMT_PKG;
/