Stored Procedures

-- Create a stored procedure (MySQL)
DELIMITER //
CREATE PROCEDURE GetCustomerOrders(
    IN p_customer_id INT,
    IN p_start_date DATE,
    OUT p_total DECIMAL(10,2)
)
BEGIN
    -- Return order details
    SELECT o.id, o.total, o.status, o.created_at
    FROM orders o
    WHERE o.customer_id = p_customer_id
      AND o.created_at >= p_start_date
    ORDER BY o.created_at DESC;

    -- Calculate total
    SELECT SUM(total) INTO p_total
    FROM orders
    WHERE customer_id = p_customer_id
      AND created_at >= p_start_date;
END //
DELIMITER ;

-- Call the procedure
CALL GetCustomerOrders(42, '2025-01-01', @total);
SELECT @total AS customer_total;

-- User-defined function (returns a value)
CREATE FUNCTION CalculateDiscount(
    price DECIMAL(10,2),
    quantity INT
) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE discount DECIMAL(10,2) DEFAULT 0;
    IF quantity >= 100 THEN SET discount = 0.20;
    ELSEIF quantity >= 50 THEN SET discount = 0.10;
    ELSEIF quantity >= 10 THEN SET discount = 0.05;
    END IF;
    RETURN price * quantity * (1 - discount);
END;

-- Use in queries
SELECT name, price, CalculateDiscount(price, 25) AS total FROM products;

-- Trigger: Automatically runs on INSERT/UPDATE/DELETE
CREATE TRIGGER update_customer_order_count
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE customers
    SET order_count = order_count + 1
    WHERE id = NEW.customer_id;
END;