Stored Procedures và Functions trong SQL Server - Học SQL Online | Cơ sở dữ liệu

Stored Procedures là gì ?

Trong SQL Server, Stored Procedures là là một tập hợp các câu lệnh SQL được viết và lưu trữ trong cơ sở dữ liệu SQL Server. Nó cho phép bạn thực thi lại những câu lệnh này nhiều lần mà không cần phải viết lại chúng, giúp tự động hóa và tối ưu hóa các tác vụ trong cơ sở dữ liệu. Stored Procedure hoạt động như một hàm trong lập trình, có thể nhận tham số đầu vào và trả về kết quả đầu ra.

Cấu trúc cơ bản của Stored Procedure

Stored Procedure cơ bản trong SQL Server như sau:

-- Tạo Stored Procedure
CREATE PROCEDURE Ten_Stored_Procedure
AS
BEGIN
    -- Các câu lệnh SQL
    SELECT * FROM Ten_Bang
END

-- Gọi Stored Procedure
EXEC Ten_Stored_Procedure

Stored Procedure có tham số trong SQL Server như sau:

-- Tạo Stored Procedure có tham số đầu vào
CREATE PROCEDURE Tim_Theo_ID
    @ID INT
AS
BEGIN
    SELECT * FROM Ten_Bang WHERE ID = @ID
END

-- Gọi Stored Procedure với tham số
EXEC Tim_Theo_ID @ID = 1

Stored Procedure có tham số đầu ra với từ khóa OUTPUT trong SQL Server như sau:

-- Tạo Stored Procedure có tham số đầu vào ra
CREATE PROCEDURE TinhTong
    @GiaTri1 INT,
    @GiaTri2 INT,
    @Tong INT OUTPUT
AS
BEGIN
    SET @Tong = @GiaTri1 + @GiaTri2;
END

-- Gọi Stored Procedure với tham số đầu ra:
DECLARE @KetQua INT;
EXEC TinhTong @GiaTri1 = 10, @GiaTri2 = 20, @Tong = @KetQua OUTPUT;
PRINT @KetQua; -- Kết quả là 30

Tại sao phải sử dụng Stored Procedures ?

– Tái sử dụng: Lưu trữ câu lệnh SQL giúp bạn sử dụng lại nhiều lần.
– Hiệu suất cao: Các Stored Procedure đã được biên dịch sẵn (precompiled) nên thường chạy nhanh hơn câu lệnh SQL thông thường.
– Bảo mật: Kiểm soát quyền truy cập dễ dàng hơn bằng cách chỉ cho phép người dùng gọi Stored Procedure thay vì truy cập trực tiếp vào bảng dữ liệu.
– Giảm thiểu lỗi: Đóng gói logic phức tạp trong Stored Procedure giúp giảm sai sót khi thực thi các lệnh SQL thủ công.
– Dễ bảo trì: Khi cần sửa đổi logic, chỉ cần cập nhật Stored Procedure thay vì sửa đổi các đoạn mã SQL trong ứng dụng.

Functions là gì ?

Functions (Hàm) trong SQL Server là các khối mã được định nghĩa trước, có thể được sử dụng để thực hiện một nhiệm vụ cụ thể và trả về kết quả. Không giống như Stored Procedures, hàm luôn trả về một giá trị (hoặc một bảng dữ liệu) và có thể được sử dụng trực tiếp trong các câu lệnh SQL, chẳng hạn như trong SELECT, WHERE, hoặc JOIN.

Phân loại Functions trong SQL Server

SQL Server có 2 loại hàm như sau:
1. Hàm hệ thống (System Functions):
Các hàm được tích hợp sẵn trong SQL Server:
Hàm toán học: ABS(), ROUND(), POWER().
Hàm chuỗi: LEN(), SUBSTRING(), UPPER().
Hàm ngày giờ: GETDATE(), DATEADD(), DATEDIFF().
Hàm chuyển đổi: CAST(), CONVERT().
2. Hàm do người dùng định nghĩa (User-Defined Functions – UDFs):
Các hàm được người dùng tạo ra, chia thành 2 loại:
Scalar Function: Trả về một giá trị đơn (như INT, VARCHAR, DATETIME…)
Table-Valued Function: Trả về một bảng dữ liệu hoặc xử lý dữ liệu trên bảng như insert, update, delete, gồm có:
– Inline Table-Valued Function: Hàm đọc bảng
– Multi-Statement Table-Valued Function: Hàm xử lý dữ liệu bảng
Ví dụ:

-- Scalar Function trả về kiểu INT khi tính tổng 2 số
CREATE FUNCTION dbo.Tong2So (@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
    RETURN (@num1 + @num2)
END
GO
--Gọi thực hiện
SELECT dbo.Tong2So(3, 5)
GO

--Tạo hàm đọc danh sách nhân viên theo mã phòng truyền vào (Inline Table-Valued Function)
CREATE FUNCTION dbo.EmployeeByDep(@DepartmentID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM Employee
    WHERE DepartmentID = @DepartmentID
)
GO
--Gọi hàm
SELECT * FROM EmployeeByDep(5)

Sửa Stored Procedure và FUNCTION

Khi sửa Stored Procedure và FUNCTION, chúng ta dùng từ khóa ALTER thay cho từ CREATE.

--sửa hàm EmployeeByDep
ALTER FUNCTION dbo.EmployeeByDep(@DepartmentID INT)
...
GO

Nếu sử dụng từ phiên bản SQL Server 2017 trở về sau, chúng ta có thể viết CREATE OR ALTER FUNCTION cho cả 2 mục đích tạo và sửa.

-- tạo và sửa hàm EmployeeByDep cho SQL Server 2017 về sau
CREATE OR ALTER FUNCTION dbo.EmployeeByDep(@DepartmentID INT)
...
GO