Function người dùng tự định nghĩa trong SQL

Đôi lúc chúng ta muốn tự tạo một hàm riêng cho hệ thống để dễ mở rộng và tái sử dụng, trong SQL cũng hỗ trợ chúng ta thực hiện công việc này. Bài viết này mình xin chia sẻ một số kiến thức về Function (Hàm) trong MS SQL Server.

Function là gì?

Định nghĩa:

Là một đối tượng trong cơ sở dữ liệu (CSDL) sử dụng trong các câu lệnh SQL, được biên dịch sẵn và lưu trong CSDL nhằm mục đích thực hiện xử lý nào đó như tính toán phức tạp và trả về kết quả là giá trị nào đó.

Đặc điểm:

• Luôn trả về giá trị
• Gồm 2 loại: Function hệ thống và Function do người dùng tự định nghĩa
• Function người dùng tự định nghĩa gồm 2 loại:

• Scalar-valued: Trả về giá trị vô hướng của các kiểu dữ liệu T-SQL
• Table-valued: Trả về bảng, là kết quả của một hoặc nhiều lệnh

Cách định nghĩa Function

1. Tạo Function trả về giá trị loại Scalar-valued

CREATE FUNCTION <Tên function>
([@<tên tham số> <kiểu dữ liệu> [= <giá trị mặc định>], …,[...]])
RETURNS <kiểu dữ liệu>
[WITH ENCRYPTION]
[AS]
BEGIN
    [Thân của hàm]
    RETURN <Biểu thức giá trị đơn>
END

Trong đó:

• Tên function: Tên của hàm chúng ta sẽ tạo
• Tên tham số: Là các tham số Input cho hàm. Khai báo báo gồm tên của tham số (trước tên tham số sử dụng tiền tố @), kiểu dữ liệu của tham số, chúng ta có thể chỉ định giá trị mặc định cho tham số. Có thể chỉ định nhiều tham số đầu vào
• RETURNS: từ khóa này chỉ định kiểu dữ liệu hàm sẽ trả về. Kiểu dữ liệu phải được chỉ định kiểu độ dài dữ liệu. Ví dụ: varchar(100)
• WITH ENCRYPTION: Từ khóa chỉ định code của hàm sẽ được mã hóa trong bảng syscomments.
• AS: Từ khóa cho biết code của hàm bắt đầu.
• BEGIN: Đi cùng với END để tạo thành bao khối bao các câu lệnh trong thân hàm.
• RETURN: Từ khóa này sẽ gửi giá trị tới thủ tục gọi hàm. Một số lưu ý:
• Tên function phải là duy nhất trong 1 CSDL. Function được tạo/định nghĩa trong CSDL nào thì chỉ sử dụng trong CSDL đó. Khác với Function có sẵn của SQL được truy cập ở bất cứ đâu.
• Danh sách tham số tối đa 1024 tham số.

2. Tạo Function trả về giá trị loại Table-valued

Function Table-valued có 2 loại:

• Hàm giá trị bảng đơn giản: Trả về bảng, là kết quả của một câu lệnh SELECT đơn
• Hàm giá trị bảng đa câu lệnh: Trả về bảng, là kết quả của nhiều câu lệnh

a) Hàm giá trị bảng đơn giản

CREATE FUNCTION <Tên function>
([@<tên tham số> <kiểu dữ liệu> [= <giá trị mặc định>], …,[...]])
RETURNS TABLE
[WITH ENCRYPTION]
[AS]
    RETURN <Câu lệnh SQL>
END

Lưu ý Hàm giá trị bảng đơn còn được gọi là hàm giá trị bảng nội tuyến. Có thể được dùng trong câu lệnh truy vấn thay thế cho tên bảng hoặc tên View.

b) Hàm giá trị bảng đa câu lệnh

CREATE FUNCTION <Tên function>
([@<tên tham số> <kiểu dữ liệu> [= <giá trị mặc định>], …,[...]])
RETURNS @<tên biến trả về> TABLE (<tên cột 1> <kiểu dữ liệu> [tùy chọn thuộc tính], ..., <tên cột n> <kiểu dữ liệu> [tùy chọn thuộc tính])
[AS]
BEGIN
    <Câu lệnh SQL>
RETURN
END

Thay đổi, xóa, xem nội dung Function

1. Thay đổi Function

Để thay đổi các hàm đã khai báo ta sử dụng câu lệnh ALTER FUNCTION. Cú pháp tương tự như tạo mới Function, chỉ thay từ khóa CREATE bằng từ khóa ALTER

ALTER FUNCTION <Tên function>
([@<tên tham số> <kiểu dữ liệu> [= <giá trị mặc định>], …,[...]])
RETURNS <kiểu dữ liệu> | TABLE
[WITH ENCRYPTION]
[AS]
BEGIN
    [Thân của hàm]
    RETURN <Biểu thức giá trị đơn> | Câu lệnh SQL
END

2. Xóa Function

Để xóa hàm ta dùng câu lệnh DROP FUNCTION.

DROP FUNCTION [schema_name.] <Tên function> 

3. Xem nội dung Function

Để xem nội dung function ta sử dụng Store Procedure (Thủ tục) có sẵn của SQL là sp_helptext (Transact-SQL)

EXEC sp_helptext 'FunctionName'

Ví dụ cụ thể

Cho bài toán quản lý Vay có thế chấp tài sản đơn giản thể hiện qua sơ đồ mức vật lý như sau:

Ví dụ 1. Tạo function cho biết số lượng khách hàng theo địa chỉ bất kỳ nhận vào từ tham số với điều kiện là khách hàng có tổng số tiền vay từ trước đến nay từ 200 triệu trở lên.

Cách 1: Trả về giá trị vô hướng

CREATE FUNCTION count_customer_with_address (@address varchar(200))
RETURNS int
AS 
BEGIN
	DECLARE @count int = 0
	SELECT @count = count(*) FROM (
        SELECT Vay.MaKH 
        FROM Vay,KhachHang
        WHERE Vay.MaKH=KhachHang.MaKH AND KhachHang.DiaChi= @address
        GROUp BY Vay.MaKH
        HAVING SUM(Vay.SoTienVay)>=200
    ) AS Temp
    RETURN @count
END

Gọi thực thi function:

PRINT dbo.count_customer_with_address('Ha Noi')

Cách 2: Trả về giá trị table bằng câu lệnh đơn

CREATE FUNCTION count_customer_with_address2 (@address varchar(200))
RETURNS TABLE
AS 
RETURN SELECT count(*) AS 'Total customers' FROM (
    SELECT Vay.MaKH 
    FROM Vay,KhachHang
    WHERE Vay.MaKH=KhachHang.MaKH AND KhachHang.DiaChi= @address
    GROUp BY Vay.MaKH
    HAVING SUM(Vay.SoTienVay)>=200
) AS Temp

Gọi thực thi function:

SELECT * FROM dbo.count_customer_with_address2 ('Da Nang')

Cách 3: Trả về giá trị table bằng đa câu lệnh

CREATE FUNCTION count_customer_with_address3 (@address varchar(200))
RETURNS @new_table TABLE (DiaChi varchar(200), SoLuong int)
AS
BEGIN
	DECLARE @count int = 0
	SELECT @count = count(*) FROM (
        SELECT Vay.MaKH 
        FROM Vay,KhachHang
        WHERE Vay.MaKH=KhachHang.MaKH AND KhachHang.DiaChi= @address
        GROUp BY Vay.MaKH
        HAVING SUM(Vay.SoTienVay)>=200
    ) AS Temp
    INSERT INTO @new_table VALUES (@address, @count)
    RETURN
END

Ví dụ 2. Sửa đổi function đã tạo ở trên bổ sung điều kiện khách hàng có tuổi từ 30 trở lên

ALTER FUNCTION count_customer_with_address (@address varchar(200))
RETURNS int
AS
BEGIN
	DECLARE @count int = 0
	SELECT @count = count(*) FROM (
        SELECT Vay.MaKH 
        FROM Vay JOIN KhachHang ON Vay.MaKH=KhachHang.MaKH
        WHERE KhachHang.DiaChi= @address AND YEAR(GETDATE()) - YEAR(NgaySinh) >= 30
        GROUp BY Vay.MaKH
        HAVING SUM(Vay.SoTienVay)>=200
    ) AS Temp
    RETURN @count
END

Ví dụ 3. Xóa bỏ function đã tạo

DROP FUNCTION count_customer_with_address

Kết luận

Trên đây là những gì mình tổng hợp và tự đưa ra ví dụ minh họa cho từng phần. Hi vọng sẽ giúp ích cho các bạn trong quá trình sử dụng function trong câu lệnh sql.