1. Giới thiệu

Stored Procedures trong MySQL Server là một tập hợp các câu lệnh SQL được lưu trữ trong cơ sở dữ liệu và có thể được thực thi nhiều lần. Stored Procedures giúp bạn quản lý các tác vụ phức tạp như thực hiện truy vấn, xử lý dữ liệu, hoặc tương tác với nhiều bảng. Sử dụng Stored Procedures giúp cải thiện hiệu suất, bảo mật và khả năng tái sử dụng của ứng dụng cơ sở dữ liệu.

2. Lợi ích của Stored Procedures

  • Hiệu suất tốt hơn: Stored Procedures được biên dịch trước và lưu trữ sẵn trong cơ sở dữ liệu, giúp tăng tốc độ thực thi.
  • Bảo mật tốt hơn: Bạn có thể kiểm soát quyền truy cập đến dữ liệu bằng cách chỉ cho phép người dùng gọi Stored Procedures thay vì truy vấn trực tiếp vào bảng.
  • Dễ bảo trì: Code SQL được lưu trữ ở một nơi duy nhất, dễ dàng bảo trì và cập nhật.
  • Giảm độ phức tạp: Stored Procedures cho phép bạn tổ chức code SQL phức tạp thành các đoạn mã nhỏ, dễ hiểu.

3. Cú pháp của Stored Procedures

Cú pháp chung để tạo Stored Procedure trong MySQL Server là:

shell
CREATE PROCEDURE procedure_name
AS
BEGIN
    -- Các câu lệnh SQL
END;
  • procedure_name: Tên của Stored Procedure.
  • BEGIN ... END: Khối lệnh chứa các câu lệnh SQL.

4. Ví dụ tạo Stored Procedure đơn giản

a. Tạo Stored Procedure mà không có tham số

Ví dụ 1: Tạo Stored Procedure để lấy tất cả nhân viên trong bảng employees

shell
CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM employees;
END;

Stored Procedure này khi được gọi sẽ trả về tất cả các hàng trong bảng employees.

b. Tạo Stored Procedure với tham số đầu vào

Stored Procedures có thể nhận tham số đầu vào để thực hiện các thao tác tùy chỉnh dựa trên dữ liệu đầu vào.

Ví dụ 2: Tạo Stored Procedure để lấy thông tin nhân viên theo phòng ban

shell
CREATE PROCEDURE GetEmployeesByDepartment
    @Department NVARCHAR(50)
AS
BEGIN
    SELECT * 
    FROM employees
    WHERE department = @Department;
END;

Trong ví dụ này, Stored Procedure GetEmployeesByDepartment nhận một tham số là @Department và trả về danh sách các nhân viên thuộc phòng ban đó.

5. Gọi Stored Procedures

Để gọi một Stored Procedure, bạn sử dụng câu lệnh EXEC hoặc EXECUTE:

shell
EXEC GetAllEmployees;

Hoặc với Stored Procedure có tham số:

shell
EXEC GetEmployeesByDepartment @Department = 'Sales';

6. Stored Procedure với tham số đầu ra

Stored Procedures có thể sử dụng tham số đầu ra để trả về kết quả cho người gọi.

Ví dụ 3: Tạo Stored Procedure để trả về tổng số nhân viên trong một phòng ban

shell
CREATE PROCEDURE GetEmployeeCountByDepartment
    @Department NVARCHAR(50),
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*)
    FROM employees
    WHERE department = @Department;
END;

Để gọi Stored Procedure này và nhận tham số đầu ra, bạn làm như sau:

shell
DECLARE @Count INT;
EXEC GetEmployeeCountByDepartment @Department = 'Sales', @EmployeeCount = @Count OUTPUT;
SELECT @Count AS TotalEmployees;

Kết quả:

TotalEmployees
3

7. Stored Procedure với tham số đầu vào mặc định

Bạn có thể định nghĩa các tham số mặc định trong Stored Procedures. Điều này cho phép bạn gọi Stored Procedure mà không cần truyền giá trị cho tham số đó, và SQL Server sẽ sử dụng giá trị mặc định.

Ví dụ 4: Tạo Stored Procedure với tham số mặc định

shell
CREATE PROCEDURE GetEmployeesByDepartmentWithDefault
    @Department NVARCHAR(50) = 'Sales'
AS
BEGIN
    SELECT * 
    FROM employees
    WHERE department = @Department;
END;

Khi gọi Stored Procedure mà không truyền giá trị cho tham số, SQL Server sẽ sử dụng giá trị mặc định là 'Sales':

shell
EXEC GetEmployeesByDepartmentWithDefault;

Bạn cũng có thể truyền tham số tùy chọn:

shell
EXEC GetEmployeesByDepartmentWithDefault @Department = 'Marketing';

8. Stored Procedure với câu lệnh IF, WHILE, và TRY...CATCH

Stored Procedures có thể sử dụng các câu lệnh điều kiện như IF, WHILE, và TRY...CATCH để xử lý logic phức tạp.

Ví dụ 5: Sử dụng IF...ELSE trong Stored Procedure

shell
CREATE PROCEDURE GetEmployeeSalary
    @EmployeeID INT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM employees WHERE employee_id = @EmployeeID)
    BEGIN
        SELECT salary FROM employees WHERE employee_id = @EmployeeID;
    END
    ELSE
    BEGIN
        PRINT 'Employee not found';
    END;
END;

Stored Procedure này kiểm tra xem nhân viên có tồn tại không. Nếu tồn tại, nó sẽ trả về lương của nhân viên, nếu không, nó sẽ in thông báo "Employee not found".

9. Xử lý ngoại lệ bằng TRY...CATCH

TRY...CATCH được sử dụng trong Stored Procedure để xử lý ngoại lệ (lỗi) khi thực hiện các câu lệnh SQL.

Ví dụ 6: Sử dụng TRY...CATCH trong Stored Procedure

shell
CREATE PROCEDURE SafeDivision
    @Num1 INT,
    @Num2 INT
AS
BEGIN
    BEGIN TRY
        DECLARE @Result INT;
        SET @Result = @Num1 / @Num2;
        SELECT @Result AS DivisionResult;
    END TRY
    BEGIN CATCH
        PRINT 'Error: Division by zero is not allowed';
    END CATCH;
END;

Stored Procedure này thực hiện phép chia và sử dụng TRY...CATCH để xử lý ngoại lệ nếu có lỗi chia cho 0.

10. Một số lưu ý khi sử dụng Stored Procedures

a. Tính tái sử dụng và bảo trì

Stored Procedures giúp tập trung hóa logic xử lý dữ liệu, làm cho quá trình bảo trì dễ dàng hơn. Khi cần thay đổi, bạn chỉ cần cập nhật Stored Procedure thay vì cập nhật mọi nơi mà truy vấn được sử dụng.

b. Cải thiện hiệu suất

Stored Procedures trong SQL Server được biên dịch trước và tối ưu hóa, giúp chúng có hiệu suất tốt hơn so với các truy vấn động. Chúng cũng giảm thiểu lưu lượng mạng vì các lệnh SQL được thực hiện trên máy chủ thay vì truyền đi nhiều câu truy vấn nhỏ lẻ.

c. Bảo mật

Stored Procedures giúp kiểm soát quyền truy cập vào dữ liệu. Thay vì cho phép người dùng truy cập trực tiếp vào các bảng dữ liệu, bạn có thể giới hạn họ chỉ có thể gọi Stored Procedures, giúp tăng cường bảo mật.

Kết luận

Stored Procedures là một công cụ mạnh mẽ và linh hoạt trong SQL Server, giúp cải thiện hiệu suất, bảo mật, và khả năng tái sử dụng của các truy vấn MySQL. Bằng cách sử dụng Stored Procedures, bạn có thể dễ dàng tổ chức code, xử lý các tác vụ phức tạp, và tối ưu hóa quy trình làm việc với cơ sở dữ liệu.

Hãy thử áp dụng các ví dụ này trong các bài tập và dự án thực tế trên CodeTutHub để hiểu rõ hơn về cách sử dụng SQL Stored Procedures trong MySQL Server!