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

MySQL Stored Procedures: Tạo và sử dụng

Trong thế giới cơ sở dữ liệu, hiệu năng và khả năng bảo trì là hai yếu tố then chốt quyết định sự thành công của một ứng dụng. MySQL Stored Procedures (thủ tục lưu trữ) ra đời nhằm giải quyết cả hai vấn đề này. Bài viết này sẽ dẫn bạn khám phá khái niệm, cách tạo và ứng dụng thực tế của Stored Procedures trong MySQL.

Stored Procedures là gì?

Stored Procedures là khối mã SQL được lưu trữ trực tiếp trong cơ sở dữ liệu, có khả năng chấp nhận tham số đầu vào, xử lý logic và trả về kết quả. Khác với các câu lệnh SQL thông thường, Stored Procedures được biên dịch một lần và lưu trong server, giúp giảm overhead khi thực thi nhiều lần.

Ưu điểm nổi bật: - Tăng hiệu năng: Tránh việc gửi nhiều câu lệnh SQL qua lại giữa ứng dụng và server. - Bảo mật tốt hơn: Người dùng có thể được cấp quyền thực thi Stored Procedures mà không cần truy cập trực tiếp vào bảng dữ liệu. - Dễ bảo trì: Logic xử lý tập trung tại database, giảm sự phân tán code.

Cú pháp cơ bản

Tạo Stored Procedure

DELIMITER $$

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


DELIMITER ;

Lưu ý: DELIMITER được thay đổi để MySQL phân biệt được nơi kết thúc procedure.

Gọi Stored Procedure

CALL sp_GetCustomers();

Sử dụng tham số

Stored Procedures hỗ trợ cả tham số đầu vào (IN), đầu ra (OUT) và vừa vào/vừa ra (INOUT).

Ví dụ với tham số IN

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.