PROCEDURE (Thủ tục) trong SQL Server

Procedure trong SQL là gì? Bài viết sẽ cung cấp cho bạn những thông tin cần biết về thủ tục trong SQL.

Procedure (Thủ tục) là một chương trình trong cơ sở dữ liệu gồm nhiều câu lệnh mà bạn lưu lại cho những lần sử dụng sau. Trong SQL Server, bạn có thể truyền các tham số vào procedure, tuy nó không trả về một giá trị cụ thể như function (hàm) nhưng cho biết việc thực thi thành công hay thất bại.

Lợi ích khi dùng Procedure trong SQL

• Dễ dàng chỉnh sửa: Lập trình viên dễ dàng chỉnh sửa code bên trong thủ tục ở SQL Server mà không cần khởi động lại hay triển khai ứng dụng. Ví dụ, nếu truy vấn T-SQL được viết trong ứng dụng và bạn cần thay đổi logic, bạn phải thay code trong ứng dụng đó và triển khai lại nó. Thủ tục trong SQL Server loại bỏ những khó khăn đó bằng cách lưu trữ code trong database. Vì thế, khi muốn thay đổi logic bên trong procedure, bạn chỉ có thể làm việc đó bằng lệnh Alter Procedure đơn giản.
• Giảm tải truy cập mạng: Khi dùng procedure được lưu trữ thay vì viết các truy vấn T-SQL ở cấp ứng dụng, chỉ tên thủ tục được chuyển qua mạng thay vì toàn bộ code T-SQL.
• Có thể tái sử dụng: Bạn có thể triển khai procedure được lưu trữ bằng nhiều người dùng hoặc nhiều ứng dụng client mà không cần viết lại code.
• Bảo mật: Giảm các mối đe dọa bằng cách xóa truy cập trực tiếp tới bảng.

Bài viết sẽ cung cấp cho bạn cú pháp và những ví dụ về cách tạo và xóa bỏ procedure trong SQL Server.

CREATE PROCEDURE (Tạo Procedure)

Cú pháp

Để tạo một procedure trong SQL Server, ta sử dụng cú pháp như dưới đây:

CREATE { PROCEDURE | PROC } [schema_name.]procedure_name
[ @parameter [type_schema_name.] datatype
[ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ]
, @parameter [type_schema_name.] datatype
[ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ] ]

[ WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause } ]
[ FOR REPLICATION ]

AS

BEGIN
[declaration_section]

executable_section

END;

Tham số:

• schema_name: Tên schema (lược đồ) sở hữu procedure.
• procedure_name: Tên gán cho procedure
• @parameter: Một hay nhiều tham số được truyền vào hàm.
• type_schema_name: Kiểu dữ liệu của schema (nếu có).
• Datatype: Kiểu dữ liệu cho @parameter.
• Default: Giá trị mặc định gán cho @parameter.
• OUT/OUTPUT: @parameter là một tham số đầu ra
• READONLY: @parameter không thể bị procedure ghi đè lên.
• ENCRYPTION: Mã nguồn (source) của procedure sẽ không được lưu trữ dưới dạng text trong hệ thống.
• RECOMPILE: Truy vấn sẽ không được lưu ở bộ nhớ đệm (cache) cho thủ tục này.
• EXECUTE AS clause: Xác định ngữ cảnh bảo mật để thực thi thủ tục.
• FOR REPLICATION: Procedure đã lưu sẽ chỉ được thực thi trong quá trình replication (nhân bản).

Ví dụ

CREATE PROCEDURE spNhanvien
@nhanvien_name VARCHAR(50) OUT

AS
BEGIN
DECLARE @nhanvien_id INT;
SET @nhanvien_id = 8;
IF @nhanvien_id < 10
SET @nhanvien_name = 'Smith';
ELSE
SET @nhanvien_name = 'Lawrence';

END;

Thủ tục trên được gán tên là spNhanvien, có một tham số là @nhanvien_name, output của tham số sẽ được dựa trên @nhanvien_id.

Sau đó, bạn có thể thực hiện tham chiếu spNhanvien như sau:

USE [test]
GO

DECLARE @site_name varchar(50);
EXEC FindSite @site_name OUT;
PRINT @site_name;

GO

Drop Procedure (Xóa bỏ Procedure)

Một khi đã tạo thành công các procedure thì cũng sẽ có những trường hợp bạn muốn xóa bỏ procedure khỏi cơ sở dữ liệu vì một vài lý do.

Cú pháp

Để xóa bỏ một procedure, ta có cú pháp sau:

DROP PROCEDURE procedure_name;

Tham số:

procedure_name: Tên procedure bạn muốn xóa bỏ...

Ví dụ

DROP PROCEDURE spNhanvien;

Thực hiện lệnh này là bạn đã vừa xóa bỏ thủ tục spNhanvien khỏi database.