Thủ tục trong SQL: Hướng dẫn sử dụng bằng ví dụ cụ thể

Thủ tục (Stored Procedure) là một phần quan trọng trong SQL, giúp thực hiện tự động các tác vụ phức tạp và lặp lại. Việc hiểu rõ và nắm vững cách sử dụng thủ tục trong SQL sẽ giúp tăng cường bảo mật, tăng hiệu suất và giảm thiểu rủi ro cho cơ sở dữ liệu và ứng dụng.

Đọc bài viết này để hiểu rõ hơn về:

• Thủ tục là gì?
• Lợi ích và lưu ý khi sử dụng thủ tục
• Phân loại thủ tục
• Một số cú pháp với thủ tục

Thủ tục trong SQL là gì?

Trong SQL, thủ tục (Procedure), hay còn được gọi là thủ tục lưu trữ (Stored Procedure) là một tập hợp các câu lệnh SQL được lưu trữ trong cơ sở dữ liệu, hoạt động như một tiểu chương trình (subroutine) và có thể được gọi ra để thực hiện trong các hành động cụ thể. Thủ tục giúp thực hiện các tác vụ như truy vấn, biến đổi, cập nhật dữ liệu, tạo báo cáo cơ bản, tối ưu hóa hiệu suất ứng dụng và tăng cường bảo mật cơ sở dữ liệu.

Thủ tục còn hỗ trợ việc truyền tham số, cho phép người dùng cung cấp dữ liệu đầu vào để thủ tục thực thi các tác vụ cụ thể dựa trên các giá trị này. Nhờ đó, thủ tục tăng cường tính hiệu quả, tính tái sử dụng và bảo mật trong quản lý cơ sở dữ liệu, đồng thời giảm thiểu việc phải viết lại các câu lệnh SQL nhiều lần.

Lợi ích của việc sử dụng thủ tục trong SQL

• Có thể tái sử dụng: Thủ tục có thể được sử dụng lại bởi nhiều người dùng và ứng dụng chỉ bằng cách gọi nó, giúp giảm thiểu việc phải viết lại các câu lệnh SQL nhiều lần và tiết kiệm thời gian.
• Dễ chỉnh sửa: Bạn có thể dễ dàng thay đổi nội dung bên trong thủ tục bằng lệnh ALTER, giúp điều chỉnh nhanh chóng khi có yêu cầu mới.
• Tăng cường bảo mật: Thủ tục giúp hạn chế truy cập trực tiếp vào bảng, từ đó nâng cao mức độ bảo mật cho cơ sở dữ liệu hoặc ứng dụng.
• Giảm lưu lượng mạng: Thay vì truyền toàn bộ câu truy vấn, máy chủ chỉ cần gọi tên thủ tục, giúp giảm tải lưu lượng mạng.
• Tăng hiệu suất: Khi được sử dụng lần đầu, thủ tục sẽ được tối ưu hóa và lưu vào bộ đệm, giúp thực thi nhanh chóng cho những lần tiếp theo.
• Dễ bảo trì: Khi có nhiều nơi cùng sử dụng một logic, thủ tục giúp việc bảo trì đơn giản hơn. Nếu logic cần thay đổi, chỉ cần chỉnh sửa thủ tục thay vì phải cập nhật ở nhiều nơi khác nhau.
• Giảm thiểu rủi ro lỗi: Logic được thiết kế sẵn trong thủ tục sẽ giảm bớt rủi ro lỗi do người dùng hoặc hệ thống gây ra, bởi vì các nội dung quan trọng đã được cố định trước.

Nhược điểm khi sử dụng thủ tục trong SQL

• Phụ thuộc vào hệ quản trị cơ sở dữ liệu: Mỗi hệ quản trị CSDL sử dụng ngôn ngữ riêng nên khi chuyển sang hệ quản trị khác, bạn có thể cần viết lại cho phù hợp.
• Tính năng khác nhau: Ngôn ngữ thủ tục của mỗi nhà cung cấp có các tính năng và khả năng hỗ trợ khác nhau. Ví dụ, PL/SQL của Oracle có nhiều chức năng nâng cao hơn so với T-SQL của Microsoft.
• Hỗ trợ công cụ còn hạn chế: Các công cụ để viết và gỡ lỗi thủ tục thường không được hỗ trợ tốt như các ngôn ngữ lập trình khác. Mức độ hỗ trợ này còn tùy thuộc vào nhà cung cấp và ngôn ngữ bạn sử dụng.

Phân loại thủ tục trong SQL theo nguồn gốc

Thủ tục có sẵn

SQL cung cấp một số thủ tục được định nghĩa sẵn nhằm thực hiện các tác vụ quản lý và xử lý dữ liệu cơ bản. Các thủ tục này có sẵn trong hệ thống của một số DBMS như SQL Server và giúp người dùng thực hiện nhanh chóng các thao tác thường gặp như lấy thông tin về bảng, cột, hoặc kết nối. Ví dụ về các thủ tục được định nghĩa sẵn phổ biến có thể bao gồm:

Thủ tục do người dùng tạo (User-defined Stored Procedures)

Loại thủ tục này được tạo bởi người dùng để thực hiện các tác vụ cụ thể cho yêu cầu riêng biệt. Người dùng có thể tự thiết kế logic phù hợp với mục tiêu của họ.

Phân loại thủ tục trong SQL theo kiến trúc

• Native SQL procedure: Là thủ tục được viết hoàn toàn bằng SQL, bao gồm ngôn ngữ thủ tục SQL (SQL PL). Thủ tục này được tạo ra bằng một câu lệnh duy nhất, không yêu cầu biên dịch hay liên kết với chương trình bên ngoài. Native SQL procedure có hiệu suất cao và hỗ trợ nhiều tính năng phức tạp, như quản lý nhiều phiên bản hoặc thực hiện các lệnh kết hợp lồng nhau.
• Thủ tục ngoại vi (External SQL procedure): Loại thủ tục này cũng được viết bằng SQL, nhưng cần phải liên kết với chương trình bên ngoài (thường là C). Thủ tục ngoại vi đã bị hạn chế trong các phiên bản SQL mới do hiệu suất kém hơn và phức tạp hơn trong việc triển khai.
• Thủ tục lồng nhau (Nested Stored Procedures): Là thủ tục có thể gọi một hoặc nhiều thủ tục khác bên trong nó, giúp tăng tính modular cho các tác vụ phức tạp, cho phép phân chia và tổ chức các quy trình một cách logic.
• Thủ tục đệ quy (Recursive Stored Procedures): Đây là loại thủ tục có khả năng gọi chính nó trong quá trình thực thi. Thủ tục đệ quy thường được sử dụng để xử lý các tác vụ yêu cầu lặp lại một cách tuần tự như tính giai thừa hoặc duyệt qua cây phân cấp.

Phân loại thủ tục trong SQL theo chức năng

• Thủ tục có tham số: Là thủ tục nhận một hoặc nhiều tham số đầu vào từ người dùng, cho phép thực hiện các tác vụ dựa trên các giá trị truyền vào, từ đó tăng tính linh hoạt trong xử lý dữ liệu.
• Thủ tục không có tham số: Thủ tục này thực hiện các tác vụ cố định, không thay đổi và không nhận tham số đầu vào. Đây là loại thủ tục đơn giản, chủ yếu để thực hiện các thao tác cụ thể trong cơ sở dữ liệu.
• Thủ tục tạm thời: Là thủ tục chỉ tồn tại trong một phiên làm việc và thường được sử dụng để xử lý các tác vụ ngắn hạn hoặc thử nghiệm. Thủ tục này giúp tránh tác động lâu dài lên cơ sở dữ liệu.

Tiếp theo, ta sẽ cùng đi qua các cú pháp với thủ tục trong SQL bằng ví dụ thực tế. Giả sử ta có bảng Employees sau:

Lưu ý: Cú pháp với thủ tục có thể khác nhau giữa các hệ quản trị cơ sở dữ liệu (DBMS) như SQL Server, MySQL, Oracle,… Dưới đây là các cú pháp phổ biến dành riêng cho SQL Server.

Cú pháp thủ tục trong SQL được định nghĩa sẵn

Ví dụ để lấy thông tin chi tiết về bảng Employees trong SQL Server, ta có thể sử dụng thủ tục sp_help như sau:

EXEC sp_help Employees;

Ví dụ để đổi tên cột từ EmployeeName thành FullName trong bảng Employees, ta có thể sử dụng thủ tục sp_rename như sau:

EXEC sp_rename 'Employees.EmployeeName', 'FullName', 'COLUMN';

Cú pháp thủ tục trong SQL do người dùng định nghĩa

Create procedure – Tạo thủ tục

Câu lệnh CREATE PROCEDURE được sử dụng để tạo một thủ tục mới trong SQL Server. Thủ tục này có thể bao gồm các câu lệnh SQL nhằm thực hiện các tác vụ như truy vấn, cập nhật hoặc xóa dữ liệu.

Cú pháp:

CREATE PROCEDURE tên-thủ-tục 

AS 

BEGIN 

-- Các câu lệnh SQL thực thi 

END;

Ví dụ để tạo một thủ tục lấy toàn bộ dữ liệu từ bảng Employees ta sử dụng cú pháp sau:

CREATE PROCEDURE GetAllEmployees 

AS 

BEGIN 

SELECT * FROM Employees; 

END;

Execute procedure – Thực thi thủ tục

Lệnh EXEC hoặc EXECUTE được sử dụng để gọi và thực thi một thủ tục đã được tạo trước đó trong cơ sở dữ liệu.

Cú pháp:

EXEC tên-thủ-tục;

Ví dụ để thực thi thủ tục GetAllEmployees đã tạo ở trên thì ta dùng cú pháp sau:

EXEC GetAllEmployees;

Alter procedure – Sửa đổi thủ tục

Để sửa đổi một thủ tục đã tồn tại hay thay đổi nội dung hoặc cấu trúc của thủ tục, ta sử dụng lệnh  ALTER PROCEDURE.

Cú pháp:

ALTER PROCEDURE tên-thủ-tục 

AS 

BEGIN 

-- Các câu lệnh SQL mới 

END;

Ví dụ để chỉnh sửa lại thủ tục GetAllEmployees thay vì trả về toàn bộ thông tin của nhân viên thành chỉ trả về tên và chức vụ của nhân viên, ta sử dụng cú pháp sau:

ALTER PROCEDURE GetAllEmployees 

AS 

BEGIN SELECT EmployeeName, Position FROM Employees; 

END;

Drop procedure – Xoá thủ tục

Lệnh DROP PROCEDURE dùng để xóa một thủ tục đã tồn tại khỏi cơ sở dữ liệu.

Cú pháp:

DROP PROCEDURE tên-thủ-tục;

Ví dụ để xoá thủ tục GetAllEmployees, ta sử dụng cú pháp sau:

DROP PROCEDURE GetAllEmployees;

Thủ tục với một tham số

Là thhủ tục có thể nhận một tham số đầu vào để thực hiện các tác vụ cụ thể dựa trên giá trị của tham số.

Cú pháp:

CREATE PROCEDURE tên-thủ-tục (@ThamSo LoaiDuLieu) 

AS 

BEGIN 

-- Các câu lệnh SQL sử dụng tham số 

END;

Ví dụ để tạo thủ tục GetEmployeeByID nhận một tham số @EmployeeID để truy vấn thông tin nhân viên theo ID ta sử dụng cú pháp:

CREATE PROCEDURE GetEmployeeByID @EmployeeID INT 

AS

BEGIN 

SELECT * FROM Employees WHEREEmployeeID = @EmployeeID; 

END;

Thủ tục với nhiều tham số

Là thủ tục có thể nhận nhiều tham số đầu vào, cho phép thực hiện các tác vụ phức tạp dựa trên nhiều giá trị khác nhau.

Cú pháp:

CREATE PROCEDURE TenThuTuc (@ThamSo1 LoaiDuLieu, @ThamSo2 LoaiDuLieu) 

AS 

BEGIN 

-- Các câu lệnh SQL sử dụng nhiều tham số 

END;

Ví dụ để tạo thủ tục GetEmpByDepartmentAndSalary nhận hai tham số @DepartmentID và @MinSalary để tìm các nhân viên trong một phòng ban có mức lương tối thiểu nhất định ta sử dụng cú pháp sau:

CREATE PROCEDURE GetEmployeesByDepartmentAndSalary @DepartmentID INT, @MinSalary DECIMAL 

AS

BEGIN SELECT * FROM Employees WHERE DepartmentID = @DepartmentID AND Salary >= @MinSalary; 

END;

Để thực thi thủ tục trên, ta sử dụng cú pháp:

EXEC GetEmployeesByDepartmentAndSalary @DepartmentID = 2, @MinSalary = 5000;

Các câu hỏi thường gặp về thủ tục trong SQL

Thủ tục khác gì so với hàm (Function) trong SQL?

Đọc thêm: Function trong SQL là gì? Hướng dẫn sử dụng function trong SQL

Thủ tục có hỗ trợ xử lý lỗi không?

Có, thủ tục trong SQL hỗ trợ xử lý lỗi bằng cách sử dụng khối TRY-CATCH. Khối này cho phép phát hiện và xử lý lỗi trong quá trình thực thi, giúp duy trì tính ổn định của ứng dụng và cơ sở dữ liệu.

Giả sử bạn có một thủ tục tính toán giá trị bằng cách chia 10 cho một số. Trong quá trình thực hiện, nếu có lỗi xảy ra (như chia cho 0), khối TRY-CATCH sẽ giúp xử lý tình huống này. Cụ thể, khi thủ tục chạy, SQL sẽ thử thực hiện phép chia trong khối TRY. Nếu phép chia cho 0 xảy ra, SQL sẽ chuyển sang khối CATCH, hiển thị thông báo lỗi “Đã xảy ra lỗi: chia cho 0”, giúp người dùng dễ dàng nhận biết và ứng dụng không bị gián đoạn.

Khi nào nên sử dụng thủ tục trong SQL?

Nên sử dụng thủ tục khi cần thực hiện các tác vụ lặp lại như truy vấn, cập nhật, xóa dữ liệu hoặc xử lý logic phức tạp vì thủ tục giúp tái sử dụng mã, tăng hiệu suất, bảo mật và giảm lưu lượng mạng khi làm việc với cơ sở dữ liệu lớn.

Tổng kết thủ tục trong SQL

Thủ tục trong SQL không chỉ giúp tăng hiệu suất và bảo mật cơ sở dữ liệu mà còn hỗ trợ tự động hóa các tác vụ lặp lại. Việc hiểu và sử dụng thành thạo thủ tục sẽ giúp bạn quản lý dữ liệu hiệu quả hơn, giảm thiểu sai sót và tối ưu hóa công việc. 

ITviec hy vọng qua bài viết này, bạn đã có cái nhìn tổng quan và cách sử dụng thủ tục trong SQL.