MySQL Stored Procedures: Tạo và Sử dụng Hiệu Quả

DELIMITER $$

CREATE PROCEDURE sp_GetCustomers()
BEGIN
    SELECT * FROM customers;
END$$

DELIMITER ;

CALL sp_GetCustomers();

DELIMITER $$

CREATE PROCEDURE sp_GetCustomerById(IN cust_id INT)
BEGIN
    SELECT * FROM customers WHERE id = cust_id;
END$$

DELIMITER ;

Gọi:

CALL sp_GetCustomerById(5);

Ví dụ với tham số OUT

DELIMITER $$

CREATE PROCEDURE sp_CountCustomers(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM customers;
END$$

DELIMITER ;

Gọi:

CALL sp_CountCustomers(@total);
SELECT @total;

Ví dụ với INOUT

DELIMITER $$

CREATE PROCEDURE sp_UpdateAndReturnPrice(
    INOUT prod_price DECIMAL(10,2),
    IN discount_rate DECIMAL(10,2)
)
BEGIN
    SET prod_price = prod_price * (1 - discount_rate);
END$$

DELIMITER ;

Xử lý lỗi và điều khiển luồng

MySQL cung cấp các lệnh điều khiển luồng như IF, CASE, LOOP và DECLARE ... HANDLER để xử lý lỗi.

Ví dụ IF

DELIMITER $$

CREATE PROCEDURE sp_CheckStock(IN prod_id INT, OUT status VARCHAR(20))
BEGIN
    DECLARE qty INT;
    SELECT stock INTO qty FROM products WHERE id = prod_id;

IF qty > 0 THEN
        SET status = 'Còn hàng';
    ELSE
        SET status = 'Hết hàng';
    END IF;
END$$

DELIMITER ;

Ví dụ HANDLER

DELIMITER $$

CREATE PROCEDURE sp_SafeDelete(IN cust_id INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Xóa thất bại, dữ liệu có thể bị ràng buộc.';
    END;

START TRANSACTION;
    DELETE FROM customers WHERE id = cust_id;
    COMMIT;
END$$

DELIMITER ;

Một số lưu ý khi sử dụng

– Phân quyền: Cần cấp quyền EXECUTE cho người dùng muốn gọi Stored Procedures.
– Debugging: Sử dụng SELECT để in thông tin debug trong quá trình phát triển.
– Biên dịch lại: Nếu cấu trúc bảng thay đổi, cần xem xét cập nhật Stored Procedures tương ứng.

Kết luận

Stored Procedures là công cụ mạnh mẽ giúp tối ưu hiệu năng và bảo mật cho ứng dụng MySQL. Tuy nhiên, cần cân nhắc việc sử dụng hợp lý: với các tác vụ phức tạp, lặp lại nhiều lần, Stored Procedures tỏ ra vượt trội; còn với logic thay đổi thường xuyên, việc maintain ở tầng ứng dụng có thể linh hoạt hơn. Hãy bắt đầu từ những thủ tục đơn giản và nâng cấp dần theo nhu cầu thực tế của dự án.