Khám phá và tìm hiểu Procedure trong SQL - từ khái niệm cơ bản đến cách tối ưu hóa và quản lý cơ sở dữ liệu hiệu quả

Trong lĩnh vực SQL, Procedure là một khái niệm quan trọng cho phép bạn tạo ra các khối mã SQL. Qua bài viết này, chúng ta sẽ khám phá và tìm hiểu về Procedure, từ định nghĩa cơ bản đến ứng dụng thực tế, giúp bạn hiểu rõ và áp dụng nó một cách hiệu quả.

Procedure Trong SQL Là Gì?

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.

Procedure trong SQL là gì ?

Mục Đích Của Procedure Trong SQL

Dưới đây là một số mục đích khi sử dụng PROCEDURE:

• Tái sử dụng: Procedure cho phép gom nhóm các câu lệnh SQL liên quan thành một khối độc lập, cho phép tái sử dụng mã hiệu quả trong nhiều ứng dụng hoặc truy vấn khác nhau. Điều này không những giúp giảm bớt việc viết lại mã mà còn làm cho việc quản lý mã SQL trở nên dễ dàng hơn.

• Tính chất tập trung: Với việc áp dụng PROCEDURE, các câu lệnh SQL chuyên biệt được tổ chức tập trung tại một vị trí trong cơ sở dữ liệu, điều này làm cho việc quản lý và khai thác mã trở nên thuận tiện hơn.

• Hiệu suất cơ sở dữ liệu: PROCEDURE có thể cải thiện hiệu suất của cơ sở dữ liệu bằng cách giảm số lần phải gửi các câu lệnh SQL lên máy chủ cơ sở dữ liệu. Thay vào đó, chỉ cần gọi PROCEDURE một lần và thực hiện nhiều tác vụ trên máy chủ.

Cú pháp tạo Procedure

Để 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 spNhanvien @site_name OUT; PRINT @site_name; GO

Cú Pháp Xóa Procedure Trong SQL

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

Lợi Ích Khi Dùng Procedure Trong SQL

• Tính năng tái sử dụng: PROCEDURE cho phép bạn gom nhóm các lệnh SQL liên quan lại với nhau thành một khối độc lập, có khả năng được triệu hồi lại nhiều lần từ các ứng dụng hoặc truy vấn khác nhau. Điều này giúp giảm bớt sự trùng lặp của code và làm cho việc bảo trì mã SQL trở nên dễ dàng hơn.

• Tính năng tập hợp: Thông qua việc sử dụng PROCEDURE, bạn có thể tổ chức các lệnh SQL vào một vị trí duy nhất trong cơ sở dữ liệu, làm cho việc quản lý và hiểu biết về mã trở nên thuận tiện hơn.

• Cải thiện hiệu suất: PROCEDURE có thể tăng cường hiệu suất của cơ sở dữ liệu bằng cách giảm thiểu số lần các lệnh SQL cần được gửi đến máy chủ. Bằng cách chỉ gọi PROCEDURE một lần để thực hiện nhiều hoạt động, nó giúp tối ưu hóa quá trình xử lý trên máy chủ.

• Tính linh hoạt trong chỉnh sửa: Lập trình viên có thể dễ dàng thay đổi code trong procedure của SQL Server mà không cần phải khởi động lại hoặc triển khai lại toàn bộ ứng dụng. Điều này khác biệt rõ ràng so với việc phải sửa đổi code trực tiếp trong ứng dụng, yêu cầu một quy trình triển khai phức tạp. Sử dụng procedure giúp loại bỏ vấn đề này, cho phép chỉnh sửa logic nghiệp vụ một cách dễ dàng thông qua lệnh Alter Procedure.

• Giảm bớt gánh nặng truy cập mạng: Việc sử dụng procedure lưu trữ thay vì các truy vấn T-SQL trực tiếp từ ứng dụng giúp giảm đáng kể lượng dữ liệu cần phải chuyển qua mạng. Thay vì gửi toàn bộ truy vấn T-SQL, chỉ cần gửi tên của procedure, làm giảm băng thông sử dụng và tăng hiệu suất ứng dụng.

• Cải thiện bảo mật: Procedure lưu trữ cung cấp một lớp bảo mật thêm bằng cách hạn chế truy cập trực tiếp vào bảng và dữ liệu. Điều này giúp giảm thiểu rủi ro từ các mối đe dọa bảo mật bằng cách kiểm soát chặt chẽ quyền truy cập và các thao tác trên dữ liệu.

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

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: 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.

Khi Nào Nên Chọn Procedure Thay Vì Truy Vấn Trực Tiếp Từ Ứng Dụng?

Việc lựa chọn giữa sử dụng Procedure và truy vấn trực tiếp từ ứng dụng phụ thuộc vào nhiều yếu tố cụ thể của dự án và mục tiêu quản lý dữ liệu. Dưới đây là một số tình huống nên chọn Procedure thay vì truy vấn trực tiếp:

• Cần tái sử dụng logic truy vấn: Khi cùng một truy vấn hoặc tập hợp các truy vấn được sử dụng nhiều lần ở nhiều nơi trong ứng dụng, việc đóng gói chúng trong một Procedure sẽ giúp tái sử dụng code và giảm trùng lặp.

• Cải thiện hiệu suất: Procedure có thể giúp cải thiện hiệu suất bằng cách giảm thiểu số lượng giao tiếp giữa ứng dụng và cơ sở dữ liệu. Việc gửi một lệnh gọi Procedure thay vì nhiều truy vấn T-SQL có thể giảm độ trễ mạng và tải trên máy chủ cơ sở dữ liệu.

• Tăng cường bảo mật: Procedure cho phép giấu đi chi tiết thực hiện và truy cập dữ liệu, hạn chế truy cập trực tiếp đến bảng và dữ liệu nhạy cảm. Điều này giúp kiểm soát tốt hơn việc ai có quyền thực hiện những truy vấn nào.

• Phức tạp về logic nghiệp vụ: Khi logic nghiệp vụ trở nên phức tạp, việc sử dụng Procedure cho phép tập trung quản lý và xử lý logic này trên máy chủ cơ sở dữ liệu, giúp giảm tải cho ứng dụng client và tập trung quản lý code.

• Cần giảm tải truy cập mạng: Khi ứng dụng và cơ sở dữ liệu được triển khai trên môi trường mạng có băng thông hạn chế hoặc độ trễ cao, việc sử dụng Procedure có thể giảm bớt lưu lượng truy cập mạng cần thiết để thực thi logic nghiệp vụ.

• Yêu cầu về giao dịch: Khi logic nghiệp vụ yêu cầu thực hiện nhiều bước và cần được quản lý trong một giao dịch duy nhất để đảm bảo tính nhất quán của dữ liệu, việc sử dụng Procedure giúp dễ dàng quản lý giao dịch này.

• Tối ưu hóa cho các tác vụ lập lịch: Khi cần thực hiện các tác vụ định kỳ hoặc lập lịch, như bảo trì dữ liệu hoặc tạo báo cáo, việc sử dụng Procedure cho phép dễ dàng tự động hóa và lập lịch chúng từ bên trong cơ sở dữ liệu.

Trong trường hợp nào thì việc sử dụng procedure trong SQL là phù hợp nhất?

Việc sử dụng procedure trong SQL phù hợp nhất trong các trường hợp sau:

• Xử lý phức tạp: Khi cần thực hiện các thao tác phức tạp trên dữ liệu, như các bước xử lý nhiều giai đoạn, kiểm tra điều kiện, lặp, và xử lý ngoại lệ, việc sử dụng procedure giúp tổ chức mã lệnh một cách rõ ràng và hiệu quả hơn.

• Tái sử dụng mã lệnh: Khi có những đoạn mã cần được sử dụng lại nhiều lần trong ứng dụng hoặc giữa các ứng dụng khác nhau, việc đóng gói chúng trong procedure giúp dễ dàng tái sử dụng và quản lý.

• Bảo mật: Procedure cho phép kiểm soát quyền truy cập một cách tinh tế, hạn chế trực tiếp truy cập và sửa đổi dữ liệu. Bằng cách chỉ cung cấp quyền thực thi procedure cho người dùng mà không cần cung cấp quyền trực tiếp đến bảng dữ liệu, có thể giảm thiểu rủi ro về an ninh dữ liệu.

• Hiệu suất: Procedure được biên dịch và lưu trữ trong dạng đã biên dịch tại server. Điều này giúp tăng hiệu suất thực thi vì SQL Server không cần phải biên dịch lại mã lệnh mỗi khi nó được chạy. Ngoài ra, việc sử dụng procedure cũng giảm bớt số lượng truy vấn cần thiết để truyền từ ứng dụng đến cơ sở dữ liệu, giảm thiểu tải trên mạng và cải thiện thời gian phản hồi.

• Giao tiếp giữa các hệ thống: Khi cần tích hợp dữ liệu từ nhiều hệ thống khác nhau, việc sử dụng procedure giúp giảm thiểu sự phụ thuộc vào cấu trúc bên ngoài và tạo ra một lớp trừu tượng cho việc truy xuất và thay đổi dữ liệu.

• Quản lý dữ liệu phân tán: Trong môi trường có nhiều địa điểm và cơ sở dữ liệu phân tán, việc sử dụng procedure giúp quản lý dữ liệu một cách hiệu quả và đồng nhất.

Với các ưu điểm và tính năng đặc biệt của mình, việc sử dụng procedure trong SQL là một lựa chọn hợp lý để quản lý dữ liệu và cải thiện hiệu suất trong các dự án phức tạp.