Procedures và Functions trong MySQL: Hướng dẫn toàn diện về stored routines

-- ✅ Tạo
DELIMITER //
CREATE PROCEDURE proc_name(IN p_id INT, OUT p_total INT)
BEGIN
    SELECT COUNT(*) INTO p_total FROM orders WHERE customer_id = p_id;
END //
DELIMITER ;

-- ✅ Gọi
CALL proc_name(1, @total);
SELECT @total;

-- ✅ Liệt kê
SHOW PROCEDURE STATUS WHERE Db = 'your_database';

-- ✅ Xem định nghĩa
SHOW CREATE PROCEDURE proc_name;

-- ✅ Xoá
DROP PROCEDURE IF EXISTS proc_name;

-- ✅ Tạo
DELIMITER //
CREATE FUNCTION func_name(p_price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN p_price * 1.1;
END //
DELIMITER ;

-- ✅ Gọi
SELECT func_name(100);
SELECT id, func_name(price) FROM products;

-- ✅ Liệt kê
SHOW FUNCTION STATUS WHERE Db = 'your_database';

-- ✅ Xem định nghĩa
SHOW CREATE FUNCTION func_name;

-- ✅ Xoá
DROP FUNCTION IF EXISTS func_name;

-- ✅ Liệt kê tất cả routines qua INFORMATION_SCHEMA
SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database';

-- ✅ Sửa: MySQL không hỗ trợ sửa body → DROP rồi CREATE lại
-- (ALTER chỉ đổi được thuộc tính như COMMENT, SQL SECURITY)
ALTER PROCEDURE proc_name COMMENT 'Mô tả mới';

-- ✅ Cấp / thu hồi quyền thực thi
GRANT EXECUTE ON PROCEDURE your_database.proc_name TO 'app_user'@'%';
REVOKE EXECUTE ON PROCEDURE your_database.proc_name FROM 'app_user'@'%';

-- ✅ Cho phép tạo function khi bật binary log (lỗi 1418)
SET GLOBAL log_bin_trust_function_creators = 1;

-- ❌ Lỗi: client gửi lên server tới "... FROM customers;" rồi dừng
CREATE PROCEDURE GetCustomerCount()
BEGIN
    SELECT COUNT(*) FROM customers;
END;

-- ✅ Đổi delimiter tạm thời sang // → client chỉ gửi khi gặp //
DELIMITER //
CREATE PROCEDURE GetCustomerCount()
BEGIN
    SELECT COUNT(*) FROM customers;
END //
DELIMITER ;   -- Trả delimiter về lại ;

-- Cú pháp cơ bản
DELIMITER //
CREATE PROCEDURE procedure_name(
    [IN | OUT | INOUT] parameter_name datatype,
    ...
)
BEGIN
    -- SQL statements
END //
DELIMITER ;

-- Ví dụ đơn giản
DELIMITER //
CREATE PROCEDURE GetCustomerCount()
BEGIN
    SELECT COUNT(*) as customer_count FROM customers;
END //
DELIMITER ;

-- Gọi procedure
CALL GetCustomerCount();

-- IN Parameters (input only)
DELIMITER //
CREATE PROCEDURE GetCustomerById(
    IN customer_id INT
)
BEGIN
    SELECT * FROM customers WHERE id = customer_id;
END //
DELIMITER ;

-- OUT Parameters (output only)
DELIMITER //
CREATE PROCEDURE GetCustomerCountByCity(
    IN city_name VARCHAR(50),
    OUT customer_count INT
)
BEGIN
    SELECT COUNT(*) INTO customer_count 
    FROM customers 
    WHERE city = city_name;
END //
DELIMITER ;

-- INOUT Parameters (both input and output)
DELIMITER //
CREATE PROCEDURE CalculateTax(
    INOUT amount DECIMAL(10,2),
    IN tax_rate DECIMAL(5,4)
)
BEGIN
    SET amount = amount * (1 + tax_rate);
END //
DELIMITER ;

-- Sử dụng các procedures
CALL GetCustomerById(123);

CALL GetCustomerCountByCity('New York', @count);
SELECT @count;

SET @price = 1000;
CALL CalculateTax(@price, 0.08);
SELECT @price; -- Kết quả: 1080.00

-- IF Statement
DELIMITER //
CREATE PROCEDURE ClassifyCustomer(
    IN customer_id INT,
    OUT customer_class VARCHAR(20)
)
BEGIN
    DECLARE total_orders INT DEFAULT 0;
    DECLARE total_amount DECIMAL(12,2) DEFAULT 0;
    
    SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
    INTO total_orders, total_amount
    FROM orders 
    WHERE customer_id = customer_id;
    
    IF total_amount > 50000 THEN
        SET customer_class = 'VIP';
    ELSEIF total_amount > 20000 THEN
        SET customer_class = 'Premium';
    ELSEIF total_amount > 5000 THEN
        SET customer_class = 'Regular';
    ELSE
        SET customer_class = 'New';
    END IF;
END //
DELIMITER ;

-- CASE Statement
DELIMITER //
CREATE PROCEDURE GetDiscountRate(
    IN customer_type VARCHAR(20),
    OUT discount_rate DECIMAL(5,4)
)
BEGIN
    CASE customer_type
        WHEN 'VIP' THEN SET discount_rate = 0.15;
        WHEN 'Premium' THEN SET discount_rate = 0.10;
        WHEN 'Regular' THEN SET discount_rate = 0.05;
        ELSE SET discount_rate = 0.00;
    END CASE;
END //
DELIMITER ;

-- WHILE Loop
DELIMITER //
CREATE PROCEDURE GenerateSequence(
    IN max_num INT
)
BEGIN
    DECLARE counter INT DEFAULT 1;
    
    DROP TEMPORARY TABLE IF EXISTS temp_sequence;
    CREATE TEMPORARY TABLE temp_sequence (
        id INT,
        value INT
    );
    
    WHILE counter <= max_num DO
        INSERT INTO temp_sequence VALUES (counter, counter * counter);
        SET counter = counter + 1;
    END WHILE;
    
    SELECT * FROM temp_sequence;
END //
DELIMITER ;

-- FOR Loop (MySQL 8.0+)
DELIMITER //
CREATE PROCEDURE ProcessMonthlyReports()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE month_num INT;
    
    FOR month_num IN 1..12 DO
        -- Process each month
        INSERT INTO monthly_reports (month, processed_date)
        VALUES (month_num, NOW());
    END FOR;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE SafeTransferMoney(
    IN from_account VARCHAR(20),
    IN to_account VARCHAR(20),
    IN transfer_amount DECIMAL(10,2),
    OUT result_message VARCHAR(255)
)
BEGIN
    DECLARE v_from_balance DECIMAL(10,2);
    DECLARE v_error_count INT DEFAULT 0;
    
    -- Error handlers
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        GET DIAGNOSTICS CONDITION 1
            @sqlstate = RETURNED_SQLSTATE,
            @errno = MYSQL_ERRNO,
            @text = MESSAGE_TEXT;
        SET result_message = CONCAT('Error: ', @errno, ' - ', @text);
    END;
    
    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
        ROLLBACK;
        SET result_message = 'Warning occurred, transaction rolled back';
    END;
    
    START TRANSACTION;
    
    -- Check source account balance
    SELECT balance INTO v_from_balance 
    FROM accounts 
    WHERE account_number = from_account;
    
    IF v_from_balance IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Source account not found';
    END IF;
    
    IF v_from_balance < transfer_amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
    
    -- Perform transfer
    UPDATE accounts 
    SET balance = balance - transfer_amount 
    WHERE account_number = from_account;
    
    UPDATE accounts 
    SET balance = balance + transfer_amount 
    WHERE account_number = to_account;
    
    -- Check if destination account exists
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Destination account not found';
    END IF;
    
    COMMIT;
    SET result_message = 'Transfer completed successfully';
END //
DELIMITER ;

-- Test error handling
CALL SafeTransferMoney('ACC001', 'ACC002', 500.00, @msg);
SELECT @msg;

-- Scalar Function
DELIMITER //
CREATE FUNCTION CalculateAge(birth_date DATE)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
END //
DELIMITER ;

-- Sử dụng function
SELECT 
    customer_id,
    first_name,
    last_name,
    birth_date,
    CalculateAge(birth_date) as age
FROM customers;

-- String processing function
DELIMITER //
CREATE FUNCTION FormatPhoneNumber(phone VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE formatted_phone VARCHAR(20);
    
    -- Remove all non-digit characters
    SET formatted_phone = REGEXP_REPLACE(phone, '[^0-9]', '');
    
    -- Format as (XXX) XXX-XXXX
    IF LENGTH(formatted_phone) = 10 THEN
        SET formatted_phone = CONCAT(
            '(', SUBSTRING(formatted_phone, 1, 3), ') ',
            SUBSTRING(formatted_phone, 4, 3), '-',
            SUBSTRING(formatted_phone, 7, 4)
        );
    END IF;
    
    RETURN formatted_phone;
END //
DELIMITER ;

-- Test function
SELECT FormatPhoneNumber('1234567890'); -- Returns: (123) 456-7890

-- Calculate customer loyalty points
DELIMITER //
CREATE FUNCTION CalculateLoyaltyPoints(
    customer_id INT,
    order_amount DECIMAL(10,2)
)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE customer_tier VARCHAR(20);
    DECLARE points_multiplier DECIMAL(3,2);
    DECLARE base_points INT;
    
    -- Determine customer tier
    SELECT 
        CASE 
            WHEN total_spent > 50000 THEN 'VIP'
            WHEN total_spent > 20000 THEN 'Premium'
            WHEN total_spent > 5000 THEN 'Regular'
            ELSE 'New'
        END INTO customer_tier
    FROM (
        SELECT COALESCE(SUM(total_amount), 0) as total_spent
        FROM orders
        WHERE customer_id = customer_id
    ) customer_summary;
    
    -- Set points multiplier based on tier
    CASE customer_tier
        WHEN 'VIP' THEN SET points_multiplier = 3.0;
        WHEN 'Premium' THEN SET points_multiplier = 2.0;
        WHEN 'Regular' THEN SET points_multiplier = 1.5;
        ELSE SET points_multiplier = 1.0;
    END CASE;
    
    -- Calculate base points (1 point per dollar)
    SET base_points = FLOOR(order_amount);
    
    RETURN FLOOR(base_points * points_multiplier);
END //
DELIMITER ;

-- Validate credit card number (Luhn algorithm)
DELIMITER //
CREATE FUNCTION ValidateCreditCard(card_number VARCHAR(20))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE card_length INT;
    DECLARE digit_sum INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    DECLARE digit INT;
    DECLARE doubled_digit INT;
    
    -- Remove spaces and dashes
    SET card_number = REPLACE(REPLACE(card_number, ' ', ''), '-', '');
    SET card_length = LENGTH(card_number);
    
    -- Check if all characters are digits
    IF card_number REGEXP '[^0-9]' THEN
        RETURN FALSE;
    END IF;
    
    -- Check length (13-19 digits for most cards)
    IF card_length < 13 OR card_length > 19 THEN
        RETURN FALSE;
    END IF;
    
    -- Luhn algorithm
    WHILE i <= card_length DO
        SET digit = CAST(SUBSTRING(card_number, card_length - i + 1, 1) AS UNSIGNED);
        
        IF i % 2 = 0 THEN  -- Every second digit from right
            SET doubled_digit = digit * 2;
            IF doubled_digit > 9 THEN
                SET doubled_digit = doubled_digit - 9;
            END IF;
            SET digit_sum = digit_sum + doubled_digit;
        ELSE
            SET digit_sum = digit_sum + digit;
        END IF;
        
        SET i = i + 1;
    END WHILE;
    
    RETURN (digit_sum % 10 = 0);
END //
DELIMITER ;

-- Test credit card validation
SELECT ValidateCreditCard('4532-1234-5678-9012'); -- Test with a valid format

-- Complete order processing procedure
DELIMITER //
CREATE PROCEDURE ProcessCompleteOrder(
    IN p_customer_id INT,
    IN p_product_list JSON, -- [{"product_id": 1, "quantity": 2}, ...]
    IN p_shipping_address JSON,
    IN p_payment_method VARCHAR(50),
    OUT p_order_id INT,
    OUT p_total_amount DECIMAL(12,2),
    OUT p_result_message VARCHAR(255)
)
BEGIN
    DECLARE v_product_count INT DEFAULT 0;
    DECLARE v_i INT DEFAULT 0;
    DECLARE v_product_id INT;
    DECLARE v_quantity INT;
    DECLARE v_unit_price DECIMAL(10,2);
    DECLARE v_stock_quantity INT;
    DECLARE v_subtotal DECIMAL(12,2) DEFAULT 0;
    DECLARE v_tax_amount DECIMAL(12,2);
    DECLARE v_shipping_cost DECIMAL(10,2) DEFAULT 10.00;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result_message = 'Order processing failed';
        SET p_order_id = 0;
    END;
    
    START TRANSACTION;
    
    -- Validate customer
    IF NOT EXISTS (SELECT 1 FROM customers WHERE id = p_customer_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid customer ID';
    END IF;
    
    -- Get product count from JSON
    SET v_product_count = JSON_LENGTH(p_product_list);
    
    -- Create order
    INSERT INTO orders (
        customer_id, 
        order_date, 
        status, 
        shipping_address,
        payment_method
    ) VALUES (
        p_customer_id, 
        NOW(), 
        'pending',
        p_shipping_address,
        p_payment_method
    );
    
    SET p_order_id = LAST_INSERT_ID();
    
    -- Process each product
    WHILE v_i < v_product_count DO
        SET v_product_id = JSON_UNQUOTE(JSON_EXTRACT(p_product_list, CONCAT('$[', v_i, '].product_id')));
        SET v_quantity = JSON_UNQUOTE(JSON_EXTRACT(p_product_list, CONCAT('$[', v_i, '].quantity')));
        
        -- Get product info and check stock
        SELECT price, stock_quantity 
        INTO v_unit_price, v_stock_quantity
        FROM products 
        WHERE id = v_product_id AND is_active = TRUE;
        
        IF v_unit_price IS NULL THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT('Product not found: ', v_product_id);
        END IF;
        
        IF v_stock_quantity < v_quantity THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT('Insufficient stock for product: ', v_product_id);
        END IF;
        
        -- Add order item
        INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price)
        VALUES (p_order_id, v_product_id, v_quantity, v_unit_price, v_quantity * v_unit_price);
        
        -- Update stock
        UPDATE products 
        SET stock_quantity = stock_quantity - v_quantity 
        WHERE id = v_product_id;
        
        -- Add to subtotal
        SET v_subtotal = v_subtotal + (v_quantity * v_unit_price);
        
        SET v_i = v_i + 1;
    END WHILE;
    
    -- Calculate tax (8%)
    SET v_tax_amount = v_subtotal * 0.08;
    
    -- Calculate total
    SET p_total_amount = v_subtotal + v_tax_amount + v_shipping_cost;
    
    -- Update order with totals
    UPDATE orders 
    SET 
        subtotal = v_subtotal,
        tax_amount = v_tax_amount,
        shipping_amount = v_shipping_cost,
        total_amount = p_total_amount
    WHERE id = p_order_id;
    
    COMMIT;
    SET p_result_message = 'Order processed successfully';
END //
DELIMITER ;

-- Test the procedure
SET @products = '[{"product_id": 1, "quantity": 2}, {"product_id": 2, "quantity": 1}]';
SET @address = '{"street": "123 Main St", "city": "New York", "state": "NY", "zip": "10001"}';

CALL ProcessCompleteOrder(
    1, 
    @products, 
    @address, 
    'credit_card',
    @order_id, 
    @total, 
    @message
);

SELECT @order_id, @total, @message;

-- Procedure để restock inventory với notifications
DELIMITER //
CREATE PROCEDURE RestockInventory(
    IN p_product_id INT,
    IN p_restock_quantity INT,
    IN p_supplier_id INT,
    OUT p_result VARCHAR(255)
)
BEGIN
    DECLARE v_current_stock INT;
    DECLARE v_min_stock_level INT;
    DECLARE v_product_name VARCHAR(200);
    DECLARE v_reorder_point INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = 'Restock failed due to error';
    END;
    
    START TRANSACTION;
    
    -- Get current product info
    SELECT 
        stock_quantity, 
        min_stock_level, 
        name,
        reorder_point
    INTO 
        v_current_stock, 
        v_min_stock_level, 
        v_product_name,
        v_reorder_point
    FROM products 
    WHERE id = p_product_id;
    
    IF v_current_stock IS NULL THEN
        SET p_result = 'Product not found';
        ROLLBACK;
    ELSE
        -- Update stock
        UPDATE products 
        SET 
            stock_quantity = stock_quantity + p_restock_quantity,
            last_restock_date = NOW(),
            updated_at = NOW()
        WHERE id = p_product_id;
        
        -- Log inventory movement
        INSERT INTO inventory_movements (
            product_id,
            movement_type,
            quantity_change,
            old_quantity,
            new_quantity,
            supplier_id,
            created_at
        ) VALUES (
            p_product_id,
            'restock',
            p_restock_quantity,
            v_current_stock,
            v_current_stock + p_restock_quantity,
            p_supplier_id,
            NOW()
        );
        
        -- Create notification if still below minimum
        IF (v_current_stock + p_restock_quantity) < v_min_stock_level THEN
            INSERT INTO notifications (
                type,
                title,
                message,
                created_at
            ) VALUES (
                'low_stock',
                'Low Stock Warning',
                CONCAT('Product "', v_product_name, '" is still below minimum stock level after restock'),
                NOW()
            );
        END IF;
        
        COMMIT;
        SET p_result = CONCAT('Successfully restocked ', p_restock_quantity, ' units');
    END IF;
END //
DELIMITER ;

-- Function để check products cần reorder
DELIMITER //
CREATE FUNCTION GetLowStockProducts()
RETURNS JSON
READS SQL DATA
BEGIN
    DECLARE result_json JSON;
    
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'product_id', id,
            'name', name,
            'current_stock', stock_quantity,
            'min_level', min_stock_level,
            'suggested_order', reorder_quantity
        )
    ) INTO result_json
    FROM products
    WHERE stock_quantity <= reorder_point
    AND is_active = TRUE;
    
    RETURN COALESCE(result_json, JSON_ARRAY());
END //
DELIMITER ;

-- Sử dụng function
SELECT GetLowStockProducts() as low_stock_report;

-- Procedure tạo monthly sales report
DELIMITER //
CREATE PROCEDURE GenerateMonthlySalesReport(
    IN p_year INT,
    IN p_month INT
)
BEGIN
    DECLARE v_start_date DATE;
    DECLARE v_end_date DATE;
    
    SET v_start_date = DATE(CONCAT(p_year, '-', LPAD(p_month, 2, '0'), '-01'));
    SET v_end_date = LAST_DAY(v_start_date);
    
    -- Create temporary report table
    DROP TEMPORARY TABLE IF EXISTS temp_monthly_report;
    CREATE TEMPORARY TABLE temp_monthly_report (
        category VARCHAR(100),
        total_orders INT,
        total_revenue DECIMAL(15,2),
        avg_order_value DECIMAL(10,2),
        unique_customers INT,
        top_product VARCHAR(200),
        growth_rate DECIMAL(5,2)
    );
    
    -- Insert category-wise data
    INSERT INTO temp_monthly_report (category, total_orders, total_revenue, avg_order_value, unique_customers)
    SELECT 
        c.name as category,
        COUNT(DISTINCT o.id) as total_orders,
        SUM(oi.quantity * oi.unit_price) as total_revenue,
        AVG(o.total_amount) as avg_order_value,
        COUNT(DISTINCT o.customer_id) as unique_customers
    FROM orders o
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id
    INNER JOIN categories c ON p.category_id = c.id
    WHERE o.order_date BETWEEN v_start_date AND v_end_date
    AND o.status = 'completed'
    GROUP BY c.id, c.name;
    
    -- Calculate growth rates and top products
    UPDATE temp_monthly_report tmr
    SET 
        growth_rate = (
            SELECT 
                CASE 
                    WHEN prev_revenue > 0 THEN 
                        ((tmr.total_revenue - prev_revenue) / prev_revenue) * 100
                    ELSE 0
                END
            FROM (
                SELECT 
                    c.name,
                    SUM(oi.quantity * oi.unit_price) as prev_revenue
                FROM orders o
                INNER JOIN order_items oi ON o.id = oi.order_id
                INNER JOIN products p ON oi.product_id = p.id
                INNER JOIN categories c ON p.category_id = c.id
                WHERE o.order_date BETWEEN 
                    DATE_SUB(v_start_date, INTERVAL 1 MONTH) AND 
                    DATE_SUB(v_end_date, INTERVAL 1 MONTH)
                AND o.status = 'completed'
                AND c.name = tmr.category
                GROUP BY c.name
            ) prev_month
        ),
        top_product = (
            SELECT p.name
            FROM orders o
            INNER JOIN order_items oi ON o.id = oi.order_id
            INNER JOIN products p ON oi.product_id = p.id
            INNER JOIN categories c ON p.category_id = c.id
            WHERE o.order_date BETWEEN v_start_date AND v_end_date
            AND o.status = 'completed'
            AND c.name = tmr.category
            GROUP BY p.id, p.name
            ORDER BY SUM(oi.quantity) DESC
            LIMIT 1
        );
    
    -- Return the report
    SELECT 
        category,
        total_orders,
        total_revenue,
        avg_order_value,
        unique_customers,
        top_product,
        ROUND(growth_rate, 2) as growth_rate_percent
    FROM temp_monthly_report
    ORDER BY total_revenue DESC;
    
    -- Summary totals
    SELECT 
        'TOTAL' as summary,
        SUM(total_orders) as total_orders,
        SUM(total_revenue) as total_revenue,
        AVG(avg_order_value) as overall_avg_order,
        SUM(unique_customers) as total_unique_customers
    FROM temp_monthly_report;
END //
DELIMITER ;

-- Run monthly report
CALL GenerateMonthlySalesReport(2024, 12);

-- Optimized procedure với caching
DELIMITER //
CREATE PROCEDURE GetCustomerSummaryOptimized(
    IN p_customer_id INT
)
BEGIN
    -- Check if summary exists in cache table
    IF EXISTS (
        SELECT 1 FROM customer_summary_cache 
        WHERE customer_id = p_customer_id 
        AND updated_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
    ) THEN
        -- Return cached data
        SELECT * FROM customer_summary_cache 
        WHERE customer_id = p_customer_id;
    ELSE
        -- Calculate and cache new data
        REPLACE INTO customer_summary_cache (
            customer_id,
            total_orders,
            total_spent,
            avg_order_value,
            last_order_date,
            customer_tier,
            updated_at
        )
        SELECT 
            c.id,
            COUNT(o.id),
            COALESCE(SUM(o.total_amount), 0),
            COALESCE(AVG(o.total_amount), 0),
            MAX(o.order_date),
            CASE 
                WHEN COALESCE(SUM(o.total_amount), 0) > 50000 THEN 'VIP'
                WHEN COALESCE(SUM(o.total_amount), 0) > 20000 THEN 'Premium'
                WHEN COALESCE(SUM(o.total_amount), 0) > 5000 THEN 'Regular'
                ELSE 'New'
            END,
            NOW()
        FROM customers c
        LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'completed'
        WHERE c.id = p_customer_id
        GROUP BY c.id;
        
        -- Return calculated data
        SELECT * FROM customer_summary_cache 
        WHERE customer_id = p_customer_id;
    END IF;
END //
DELIMITER ;

-- Batch update procedure
DELIMITER //
CREATE PROCEDURE UpdateCustomerTiersBatch(
    IN p_batch_size INT DEFAULT 1000
)
BEGIN
    DECLARE v_offset INT DEFAULT 0;
    DECLARE v_rows_processed INT;
    
    process_loop: LOOP
        -- Process batch
        UPDATE customers c
        INNER JOIN (
            SELECT 
                customer_id,
                CASE 
                    WHEN total_spent > 50000 THEN 'VIP'
                    WHEN total_spent > 20000 THEN 'Premium'
                    WHEN total_spent > 5000 THEN 'Regular'
                    ELSE 'New'
                END as new_tier
            FROM (
                SELECT 
                    o.customer_id,
                    SUM(o.total_amount) as total_spent
                FROM orders o
                WHERE o.status = 'completed'
                GROUP BY o.customer_id
                LIMIT p_batch_size OFFSET v_offset
            ) customer_totals
        ) tiers ON c.id = tiers.customer_id
        SET c.customer_tier = tiers.new_tier,
            c.updated_at = NOW();
        
        SET v_rows_processed = ROW_COUNT();
        SET v_offset = v_offset + p_batch_size;
        
        IF v_rows_processed < p_batch_size THEN
            LEAVE process_loop;
        END IF;
        
        -- Small delay to avoid overwhelming the server
        DO SLEEP(0.1);
    END LOOP;
    
    SELECT CONCAT('Processed customer tiers in batches of ', p_batch_size) as result;
END //
DELIMITER ;

-- Input validation function
DELIMITER //
CREATE FUNCTION ValidateEmail(email VARCHAR(255))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    RETURN email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
END //
DELIMITER ;

-- Secure procedure với validation
DELIMITER //
CREATE PROCEDURE CreateCustomerSecure(
    IN p_first_name VARCHAR(50),
    IN p_last_name VARCHAR(50),
    IN p_email VARCHAR(100),
    IN p_phone VARCHAR(20),
    OUT p_customer_id INT,
    OUT p_result VARCHAR(255)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = 'Failed to create customer';
        SET p_customer_id = 0;
    END;
    
    -- Input validation
    IF p_first_name IS NULL OR TRIM(p_first_name) = '' THEN
        SET p_result = 'First name is required';
        SET p_customer_id = 0;
        LEAVE main_proc;
    END IF;
    
    IF NOT ValidateEmail(p_email) THEN
        SET p_result = 'Invalid email format';
        SET p_customer_id = 0;
        LEAVE main_proc;
    END IF;
    
    -- Check for duplicate email
    IF EXISTS (SELECT 1 FROM customers WHERE email = p_email) THEN
        SET p_result = 'Email already exists';
        SET p_customer_id = 0;
        LEAVE main_proc;
    END IF;
    
    START TRANSACTION;
    
    INSERT INTO customers (first_name, last_name, email, phone, created_at)
    VALUES (p_first_name, p_last_name, p_email, p_phone, NOW());
    
    SET p_customer_id = LAST_INSERT_ID();
    
    COMMIT;
    SET p_result = 'Customer created successfully';
    
    main_proc: BEGIN END; -- Label for LEAVE statement
END //
DELIMITER ;

-- Logging procedure
DELIMITER //
CREATE PROCEDURE LogProcedureExecution(
    IN p_procedure_name VARCHAR(100),
    IN p_parameters JSON,
    IN p_execution_time_ms INT,
    IN p_status VARCHAR(20),
    IN p_error_message TEXT
)
BEGIN
    INSERT INTO procedure_execution_log (
        procedure_name,
        parameters,
        execution_time_ms,
        status,
        error_message,
        executed_at
    ) VALUES (
        p_procedure_name,
        p_parameters,
        p_execution_time_ms,
        p_status,
        p_error_message,
        NOW()
    );
END //
DELIMITER ;

-- Wrapper procedure với logging
DELIMITER //
CREATE PROCEDURE ProcessOrderWithLogging(
    IN p_customer_id INT,
    IN p_product_list JSON,
    OUT p_result VARCHAR(255)
)
BEGIN
    DECLARE v_start_time BIGINT;
    DECLARE v_end_time BIGINT;
    DECLARE v_execution_time INT;
    
    SET v_start_time = UNIX_TIMESTAMP(NOW(6)) * 1000000 + MICROSECOND(NOW(6));
    
    -- Call actual procedure
    CALL ProcessCompleteOrder(p_customer_id, p_product_list, @order_id, @total, p_result);
    
    SET v_end_time = UNIX_TIMESTAMP(NOW(6)) * 1000000 + MICROSECOND(NOW(6));
    SET v_execution_time = (v_end_time - v_start_time) / 1000; -- Convert to milliseconds
    
    -- Log execution
    CALL LogProcedureExecution(
        'ProcessCompleteOrder',
        JSON_OBJECT('customer_id', p_customer_id, 'product_count', JSON_LENGTH(p_product_list)),
        v_execution_time,
        IF(p_result LIKE '%successfully%', 'SUCCESS', 'ERROR'),
        IF(p_result LIKE '%successfully%', NULL, p_result)
    );
END //
DELIMITER ;

-- List all procedures và functions
SELECT 
    ROUTINE_TYPE,
    ROUTINE_NAME,
    ROUTINE_SCHEMA,
    CREATED,
    LAST_ALTERED,
    ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database'
ORDER BY ROUTINE_TYPE, ROUTINE_NAME;

-- Show procedure definition
SHOW CREATE PROCEDURE GetCustomerById;
SHOW CREATE FUNCTION CalculateAge;

-- Drop procedures và functions
DROP PROCEDURE IF EXISTS GetCustomerById;
DROP FUNCTION IF EXISTS CalculateAge;

-- Rename procedure (recreate with new name)
-- MySQL không hỗ trợ RENAME cho procedures

-- Grant execute permissions
GRANT EXECUTE ON PROCEDURE GetCustomerById TO 'app_user'@'%';
GRANT EXECUTE ON FUNCTION CalculateAge TO 'report_user'@'%';

-- Create role-based access
CREATE ROLE 'procedure_executor';
GRANT EXECUTE ON *.* TO 'procedure_executor';
GRANT 'procedure_executor' TO 'app_user'@'%';

-- Revoke permissions
REVOKE EXECUTE ON PROCEDURE GetCustomerById FROM 'app_user'@'%';

CREATE DEFINER=`root`@`localhost` FUNCTION `func_AddShipAndPaymentHist`(i_ShipmentNo BIGINT) RETURNS bigint(20)
    DETERMINISTIC
BEGIN
    DECLARE i_LiqSeq BIGINT;

    SET i_LiqSeq = func_AddShipHist(i_ShipmentNo);
    SET i_LiqSeq = func_AddPaymentHist(i_ShipmentNo, i_LiqSeq);

    RETURN i_LiqSeq;
END$$

-- Hai cách viết tương đương khi đang đăng nhập bằng root@localhost
CREATE FUNCTION func_name() RETURNS INT DETERMINISTIC RETURN 1;
CREATE DEFINER = CURRENT_USER FUNCTION func_name() RETURNS INT DETERMINISTIC RETURN 1;

ERROR 1449 (HY000): The user specified as a definer ('root'@'localhost') does not exist

-- ✅ Không ghi DEFINER trong migration files → tự lấy user đang chạy migration
CREATE FUNCTION func_AddShipAndPaymentHist(i_ShipmentNo BIGINT) RETURNS BIGINT
    DETERMINISTIC
BEGIN
    ...
END;

-- ✅ Nếu cần DEFINER, dùng account riêng cho ứng dụng thay vì root
CREATE DEFINER = 'app_owner'@'%' PROCEDURE ...

-- ✅ Dùng SQL SECURITY INVOKER khi muốn áp dụng quyền của người gọi
CREATE PROCEDURE GetReport()
SQL SECURITY INVOKER
BEGIN
    SELECT * FROM orders;
END;

-- ✅ Kiểm tra DEFINER và SQL SECURITY của các routines hiện có
SELECT ROUTINE_NAME, ROUTINE_TYPE, DEFINER, SECURITY_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database';

# ✅ Loại bỏ DEFINER khỏi file dump trước khi import sang môi trường khác
sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' dump.sql > dump_clean.sql

-- Debug procedure với detailed logging
DELIMITER //
CREATE PROCEDURE DebugProcedureTemplate(
    IN p_input_param INT
)
BEGIN
    DECLARE v_debug_mode BOOLEAN DEFAULT TRUE;
    DECLARE v_step VARCHAR(100);
    
    IF v_debug_mode THEN
        INSERT INTO debug_log (message, created_at) 
        VALUES (CONCAT('Starting procedure with param: ', p_input_param), NOW());
    END IF;
    
    SET v_step = 'Validation';
    IF v_debug_mode THEN
        INSERT INTO debug_log (message, created_at) 
        VALUES (CONCAT('Step: ', v_step), NOW());
    END IF;
    
    -- Your procedure logic here
    
    IF v_debug_mode THEN
        INSERT INTO debug_log (message, created_at) 
        VALUES ('Procedure completed successfully', NOW());
    END IF;
END //
DELIMITER ;

-- Performance monitoring query
SELECT 
    ROUTINE_SCHEMA,
    ROUTINE_NAME,
    ROUTINE_TYPE,
    COUNT(*) as execution_count,
    AVG(execution_time_ms) as avg_execution_time,
    MAX(execution_time_ms) as max_execution_time,
    SUM(CASE WHEN status = 'ERROR' THEN 1 ELSE 0 END) as error_count
FROM procedure_execution_log
WHERE executed_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
ORDER BY avg_execution_time DESC;