Stored Procedure trong SQL: khái niệm và so sánh với UDF

Trong quá trình phát triển hệ quản trị cơ sở dữ liệu quan hệ (RDBMS), các hệ thống SQL ngày càng phải xử lý nhiều logic nghiệp vụ phức tạp hơn, đồng thời đáp ứng yêu cầu về hiệu năng, bảo mật và khả năng bảo trì. Một trong những cơ chế quan trọng giúp giải quyết các yêu cầu này là Stored Procedure. Tuy nhiên, trong thực tế học tập và làm việc với SQL, người học thường băn khoăn khi lựa chọn giữa Stored Procedure và User-Defined Function cho từng bài toán cụ thể.

Bài viết này cung cấp một góc nhìn có hệ thống và mang tính học thuật về Stored Procedure trong SQL, bao gồm khái niệm, ưu điểm, hạn chế và sự khác biệt giữa Stored Procedure và User-Defined Function. 

Nội dung bài viết hướng tới các đối tượng sau: 

• Sinh viên ngành dữ liệu và công nghệ thông tin 

• Người học SQL ở mức trung cấp 

• Lập trình viên làm việc với SQL 

• Chuyên viên cơ sở dữ liệu / phân tích dữ liệu, mong muốn hiểu rõ cách lựa chọn giữa Stored Procedure và User-Defined Function trong các bài toán thiết kế hệ thống. 

Bài viết giả định người đọc đã có nền tảng SQL cơ bản, bao gồm các câu lệnh thao tác dữ liệu, khái niệm bảng, khóa, giao dịch và các đối tượng SQL phổ biến. 

Thay vì tập trung vào cú pháp chi tiết, trọng tâm của bài viết là phân tích bản chất, vai trò và ưu – nhược điểm của từng đối tượng, từ đó giúp người đọc đưa ra lựa chọn phù hợp trong thiết kế và xây dựng cơ sở dữ liệu. 

Stored Procedure là gì?

Khái niệm Stored Procedure

Stored Procedure là một tập hợp các câu lệnh SQL (có thể kèm theo logic điều khiển như điều kiện, vòng lặp) được lưu trữ sẵn trong hệ quản trị cơ sở dữ liệu và được thực thi thông qua một lời gọi (CALL hoặc EXEC).

Theo định nghĩa trong sách Database System Concepts (Silberschatz, Korth, Sudarshan), Stored Procedure là:

“A stored procedure is a set of SQL statements with an assigned name that is stored in the database and can be executed repeatedly.”

Nói cách khác, Stored Procedure cho phép đóng gói logic xử lý dữ liệu ở tầng cơ sở dữ liệu thay vì viết trực tiếp trong từng câu lệnh truy vấn của ứng dụng.

Đặc điểm cơ bản của Stored Procedure

Một Stored Procedure trong SQL thường có các đặc điểm sau:

• Được lưu trữ và quản lý bởi DBMS (ví dụ: SQL Server, Oracle, MySQL, PostgreSQL).
• Có thể nhận tham số đầu vào (INPUT) và tham số đầu ra (OUTPUT).
• Có khả năng thực hiện các thao tác INSERT, UPDATE, DELETE, SELECT.
• Có thể chứa logic điều khiển như IF…ELSE, WHILE, TRY…CATCH (tùy hệ quản trị).

Ví dụ minh họa Stored Procedure (SQL Server – AdventureWorksDW)

Bối cảnh bài toán:
Trong hệ thống kho dữ liệu AdventureWorksDW, bộ phận phân tích kinh doanh cần truy vấn doanh thu Internet theo sản phẩm. Tuy nhiên, chỉ những nhân viên đang còn hiệu lực (CurrentFlag = 1) mới được phép thực hiện truy vấn, và mỗi lần truy vấn cần được ghi nhận log để phục vụ mục đích theo dõi và audit. Yêu cầu này đòi hỏi xử lý nhiều bước nghiệp vụ, phù hợp với việc sử dụng Stored Procedure:

CREATE PROCEDURE usp_GetInternetSalesByProduct
  @ProductKey INT,
  @EmployeeKey INT
AS
BEGIN
  -- 1. Kiểm tra quyền: nhân viên phải đang còn hiệu lực
  IF NOT EXISTS (
    SELECT 1
    FROM DimEmployee
    WHERE EmployeeKey = @EmployeeKey
      AND CurrentFlag = 1
  )
  BEGIN
    RAISERROR (N'Nhân viên không hợp lệ hoặc đã nghỉ việc.', 16, 1);
    RETURN;
  END;

  -- 2. Ghi log truy vấn
  INSERT INTO DatabaseLog (PostTime, DatabaseUser, Event, Object, TSQL)
  VALUES (
    GETDATE(),
    SYSTEM_USER,
    'EXECUTE',
    'usp_GetInternetSalesByProduct',
    'Query Internet Sales by Product'
  );

  -- 3. Truy vấn doanh thu Internet theo sản phẩm
  SELECT
    p.ProductKey,
    p.EnglishProductName,
    SUM(f.SalesAmount) AS TotalSalesAmount
  FROM FactInternetSales f
  JOIN DimProduct p ON f.ProductKey = p.ProductKey
  WHERE p.ProductKey = @ProductKey
  GROUP BY p.ProductKey, p.EnglishProductName;
END;

Câu lệnh thực thi:

EXEC usp_GetInternetSalesByProduct @ProductKey = 214, @EmployeeKey = 5;

Giải thích:
Stored Procedure trên cho phép kết hợp kiểm tra điều kiện nghiệp vụ, ghi log và truy vấn dữ liệu trong một đơn vị xử lý. Đây là ví dụ điển hình cho việc sử dụng Stored Procedure trong các bài toán nghiệp vụ phức tạp, yêu cầu kiểm soát quyền và audit.

Ưu điểm của Stored Procedure

Cải thiện hiệu năng (Performance)

Một trong những ưu điểm quan trọng nhất của Stored Procedure là giảm chi phí phân tích và biên dịch câu lệnh SQL. Khi Stored Procedure được gọi lần đầu, DBMS sẽ phân tích cú pháp và tạo execution plan, sau đó kế hoạch này có thể được tái sử dụng cho các lần gọi tiếp theo.

Theo tài liệu chính thức của Microsoft SQL Server:

“Stored procedures can improve performance by reducing network traffic and reusing cached execution plans.”

Điều này đặc biệt quan trọng trong các hệ thống có tần suất truy vấn cao.

Tăng cường bảo mật dữ liệu

Stored Procedure cho phép:

• Cấp quyền EXECUTE thay vì cấp quyền trực tiếp trên bảng.
• Ẩn cấu trúc bảng và logic xử lý khỏi người dùng hoặc ứng dụng.

Nhờ đó, Stored Procedure góp phần giảm nguy cơ SQL Injection và truy cập trái phép dữ liệu nhạy cảm.

Khả năng tái sử dụng và bảo trì

• Logic nghiệp vụ được tập trung tại một nơi trong cơ sở dữ liệu.
• Khi cần thay đổi logic, chỉ cần chỉnh sửa Stored Procedure mà không phải sửa mã nguồn ứng dụng.

Điều này phù hợp với các hệ thống lớn, nhiều ứng dụng cùng truy cập chung một cơ sở dữ liệu.

Hỗ trợ logic nghiệp vụ phức tạp

Stored Procedure cho phép sử dụng:

• Biến cục bộ
• Cấu trúc điều khiển
• Xử lý lỗi

Do đó, chúng phù hợp cho các nghiệp vụ phức tạp như xử lý giao dịch (transaction processing), tổng hợp dữ liệu, hoặc kiểm tra ràng buộc nâng cao.

User-Defined Function (UDF) trong SQL

Khái niệm UDF

User-Defined Function (UDF) là hàm do người dùng tự định nghĩa, được sử dụng để trả về một giá trị đơn (scalar) hoặc một tập kết quả (table-valued).

Theo tài liệu của Oracle Database:

“A user-defined function is a schema object that encapsulates a sequence of SQL statements and returns a single value or a table.”

Đặc điểm chính của UDF

• Luôn trả về giá trị (bắt buộc), có thể là Scalar hoặc Table-valued.
• Có thể được gọi trực tiếp trong SELECT, WHERE, JOIN.
• Không (hoặc bị hạn chế) thực hiện các thao tác thay đổi dữ liệu, tùy DBMS.

Ví dụ UDF (Scalar Function)

Bối cảnh bài toán:
Với cùng yêu cầu nghiệp vụ là tính doanh thu Internet theo sản phẩm, trong một số trường hợp người dùng chỉ cần một giá trị tổng hợp để sử dụng trực tiếp trong câu lệnh SELECT, JOIN hoặc WHERE. Khi đó, UDF có thể được sử dụng để chuẩn hóa logic tính toán, nhưng không đảm nhiệm các nhiệm vụ như kiểm tra quyền hay ghi log.

Ví dụ dưới đây minh họa một Scalar UDF trả về tổng doanh thu Internet của một sản phẩm:

CREATE FUNCTION fn_GetTotalInternetSalesByProduct(@ProductKey INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
  DECLARE @TotalSales DECIMAL(18,2);
  SELECT @TotalSales = SUM(SalesAmount)
  FROM FactInternetSales
  WHERE ProductKey = @ProductKey;
  RETURN @TotalSales;
END;

Cách sử dụng UDF:

SELECT ProductKey, EnglishProductName,
dbo.fn_GetTotalInternetSalesByProduct(ProductKey) AS TotalSalesAmount
FROM DimProduct;

Giải thích:
UDF trên chỉ đảm nhiệm chức năng tính toán và trả về một giá trị. Hàm có thể được nhúng trực tiếp trong câu lệnh SELECT, giúp tái sử dụng logic tính toán. Tuy nhiên, UDF không thể thực hiện các thao tác thay đổi dữ liệu hay xử lý nghiệp vụ nhiều bước như Stored Procedure.

So sánh Stored Procedure và UDF

So sánh tổng quan

Khi nào nên dùng Stored Procedure?

Nên sử dụng Stored Procedure khi:

• Cần xử lý nghiệp vụ phức tạp.
• Cần ghi dữ liệu (INSERT, UPDATE, DELETE).
• Cần quản lý quyền truy cập và bảo mật chặt chẽ.
• Logic không cần nhúng trực tiếp trong câu lệnh SELECT.

Khi nào nên dùng UDF?

Nên sử dụng UDF khi:

• Cần tái sử dụng logic tính toán.
• Cần sử dụng trực tiếp trong SELECT hoặc WHERE.
• Logic đơn giản, không thay đổi trạng thái dữ liệu.

Kết luận

Trong SQL, Stored Procedure và User-Defined Function đều là những công cụ quan trọng giúp tổ chức và tái sử dụng logic. Tuy nhiên, mỗi đối tượng phục vụ mục đích khác nhau:

• Stored Procedure phù hợp cho xử lý nghiệp vụ, giao dịch và tối ưu hiệu năng.
• UDF phù hợp cho chuẩn hóa các phép tính và logic đơn giản trong truy vấn.

Việc hiểu rõ sự khác biệt giữa hai khái niệm này sẽ giúp người học và người làm nghề thiết kế cơ sở dữ liệu hiệu quả, an toàn và dễ bảo trì hơn.

Một số tài liệu chuyên sâu người học có thể tham khảo kỹ hơn sau bài viết:

• Microsoft SQL Server Stored Procedure
• Microsoft SQL Server UDF

Tài liệu tham khảo

1. Silberschatz, A., Korth, H. F., & Sudarshan, S. (n.d.). Database System Concepts. McGraw-Hill.
2. Microsoft. (n.d.). SQL Server Documentation – Stored Procedures.
3. Oracle. (n.d.). Oracle Database SQL Language Reference.

My Đào Thị Trà