PROCEDURE (Thủ tục) trong SQL Server

Procedure trong SQL Server có ý nghĩa thế nào trong lập trình? Cách sử dụng Procedure SQL Server có khó không? Hãy cùng Quantrimang.com tìm hiểu nhé!

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.

Thủ tục lưu trữ tạm thời

Bạn có thể tạo các thủ tục tạm thời tương tự như tạo bảng tạm thời. Database tempdb được dùng để tạo những thủ tục này. Bạn có thể chia thủ tục tạm thời thành 2 loại:

• Thủ tục lưu trữ tạm thời cục bộ
• Thủ tục tạm thời cục bộ

Thủ tục lưu trữ tạm thời cục bộ: Bạn có thể tạo kiểu này bằng cách dùng tiền tố # và chỉ truy cập trong phiên mà chúng được tạo. Khi đóng kết nối, quá trình này ngay lập tức kết thúc.

Ví dụ cách tạo một thủ tục tạm thời nội bộ:

CREATE PROCEDURE #Temp
AS
BEGIN
PRINT 'Local temp procedure'
END

Thủ tục lưu trữ tạm thời toàn cục: Bạn có thể tạo kiểu này bằng cách dùng ## làm tiền tố và truy cập từ phiên bất kỳ. Khi kết nối được dùng để tạo quá trình này đóng lại, thủ tục sẽ tự động kết thúc.

Ví dụ cách tạo thủ tục tạm thời toàn cục:

CREATE PROCEDURE ##TEMP
AS
BEGIN
PRINT 'Global temp procedure'
END

Những hạn chế của Procedure SQL Server

• Gỡ lỗi: Vì gỡ lỗi các thủ tục được lưu trữ chưa bao giờ đơn giản nên bạn không nên viết và triển khai các logic nghiệp vụ phức tạp bằng cách sử dụng chúng. Nếu không, kết quả dễ bị lỗi vì không được xử lý chính xác.
• Phụ thuộc: Như đã biết, lập trình viên DBA và cơ sở dữ liệu chuyên nghiệp xử lý tập dữ liệu “khủng” ở các tổ chức lớn. Lập trình viên ứng dụng phải phụ thuộc vào họ bởi bất kỳ thay đổi nhỏ đều phải tham chiếu tới DBA - người có thể sửa lỗi trong các thủ tục hiện tại hoặc xây dựng mới.
• Đắt: Tạo thủ tục trong SQL Server thường có chi phí quản lý DBA đắt đỏ vì các tổ chức phải trả thêm tiền cho DBA chuyên nghiệp. DBA có chuyên môn, kỹ thuật cao xử lý các thủ tục database phức tạp tốt hơn.
• Chuyên dành cho nhà cung cấp: Các thủ tục lưu trữ được viết trong một nền tảng không thể chạy trên các nền tảng khác. Vì thủ tục được viết ở Oracle phức tạp hơn nên bạn cần viết lại toàn bộ procedure cho SQL Server.

Bài trước: FUNCTION (Hàm) trong SQL Server

Bài tiếp: Lệnh IF...ELSE trong SQL Server