SQL Server - User Defined Function

Khái niệm

User Defined Function là những hàm do người dùng tự định nghĩa để đáp ứng một mục tiêu nào đó. Một số hạn chế so với thủ tục là các tham số truyền vào không được mang thuộc tính OUTPUT, nghĩa là giá trị của tham số không được truyền ra bên ngoài hàm User Defined Function, thay vào đó ta phải sử dụng giải pháp là trả về giá trị cho hàm bằng RETURN. Giá trị trả về của hàm có thể là một giá trị vô hướng (Scalar valued) hoặc bảng (Table valued).

Cú pháp

CREATE FUNCTION function_name(params)
RETURNS data_type
AS
   sql_statement
GO

Trong đó:

+ params: Các tham số cách nhau bởi dấu phẩy. Khai báo của mỗi một tham số tối thiểu phải bao gồm hai phần: tên tham số được bắt đầu bới dấu @, kiểu dữ liệu của tham số.

+ data_type: Kiểu dữ liệu trả về của hàm.

+ sql_statement: Tập hợp các câu lệnh sử dụng trong nội dung của hàm. Các câu lệnh này có thể đặt trong cặp từ khóa BEGIN ... END hoặc có thể không.

Các dạng cơ bản của User Defined Function

Scalar Functions

Đặc điểm 

Là dạng hàm vô hướng trả về một giá trị đơn và có thể sử dụng như một biến/giá trị trong một biểu thức (câu lệnh Select, mệnh đề Set của câu lệnh Update). Một hàm vô hướng có thể được xem như kết quả của vài phép toán hoặc hàm chuỗi.

Cú pháp

CREATE FUNCTION function_name(params)
RETURNS data_type
AS
    BEGIN
        sql_statement
        RETURN result
    END
GO

Ví dụ minh họa

Ví dụ 1: Viết hàm trả về thứ của một ngày trong tuần.

Cài đặt

CREATE FUNCTION NGAYTRONGTUAN(@ngay DATETIME)
RETURNS NVARCHAR(10)
AS
    BEGIN
        DECLARE @result NVARCHAR(10)
        SELECT @result = CASE DATEPART(DW, @ngay)
                   WHEN 1 THEN N'Chủ nhật'
                   WHEN 2 THEN N'Thứ hai'
                   WHEN 3 THEN N'Thứ ba'
                   WHEN 4 THEN N'Thứ tư'
                   WHEN 5 THEN N'Thứ năm'
                   WHEN 6 THEN N'Thứ sáu'
                   ELSE N'Thứ bảy'
               END
        RETURN @result
    END
GO

SELECT HONV, TENLOT, TENNV, DBO.NGAYTRONGTUAN(NGSINH) AS NGTRONGTUAN
FROM NHANVIEN

Kết quả

Table Valued Functions

Đặc điểm

Là dạng hàm có giá trị bảng trả về một tập kết quả và có thể được sử dụng như một bảng dữ liệu hay view. Hàm có giá trị bảng có thể được tham chiếu trong mệnh đề From của câu lệnh Select.

Cú pháp

CREATE FUNCTION function_name(params)
RETURNS TABLE
AS
    BEGIN
        RETURN (SELECT sql_statement)
    END
GO

Ví dụ minh họa

Ví dụ 1: Hãy trả về danh sách các nhân viên ở phòng có tên theo yêu cầu của người truy vấn.

Cài đặt

CREATE FUNCTION DSNVIEN(@tenphong NVARCHAR(30))
RETURNS TABLE
AS
    RETURN (
        SELECT HONV, TENLOT, TENNV
        FROM NHANVIEN, PHONGBAN
        WHERE TENPHG = @tenphong AND PHG = MAPHG
    )
GO

SELECT *
FROM DBO.DSNVIEN('Nghien cuu')

Kết quả

Multi Statement Table Valued Functions

Đặc điểm

Là dạng hàm này được xây dựng tập kết quả từ một hay nhiều câu lệnh Select.

Cú pháp

CREATE FUNCTION function_name(params)
RETURNS @table_name TABLE
(
    table_variable_definition
)
AS
    BEGIN
        sql_statement
        RETURN
    END
GO

Ví dụ minh họa

Ví dụ 1: Hãy viết hàm tạo biến bảng thống kê, biến bảng này mang giá trị là một bảng dữ liệu gồm có 3 cột: mã phòng, tên phòng, tổng số nhân viên của phòng đó.

Cài đặt

CREATE FUNCTION THONGKENV(@phong SMALLINT)
RETURNS @thongke TABLE (
	MAPHG SMALLINT,
	TENPHONG NVARCHAR(50),
	TONGSONV INT
)
AS
	BEGIN
		IF @phong = 0
			INSERT INTO @thongke
			SELECT MAPHG, TENPHG, COUNT(MANV)
			FROM NHANVIEN INNER JOIN PHONGBAN ON PHG = MAPHG
			GROUP BY MAPHG, TENPHG
		ELSE
			INSERT INTO @thongke
			SELECT MAPHG, TENPHG, COUNT(MANV)
			FROM NHANVIEN INNER JOIN PHONGBAN ON PHG = MAPHG
			WHERE PHG = @phong
			GROUP BY MAPHG, TENPHG
		RETURN
	END
GO

SELECT *
FROM DBO.THONGKENV(5)

Kết quả

Chỉnh sửa User Defined Function

Cú pháp sửa

ALTER FUNCTION function_name(params)
RETURNS data_type
AS
   sql_statement
GO

Lưu ý: Bạn có thể sử dụng  CREATE OR ALTER để tạo function nếu function không tồn tại hoặc để chỉnh sửa nếu function tồn tại.

CREATE OR ALTER FUNCTION function_name(params)
RETURNS data_type
AS
   sql_statement
GO

Ví dụ 1: Hãy trả về danh sách các nhân viên ở phòng nghiên cứu, sau đó hãy chỉnh sửa hàm thành lấy danh sách nhân viên trong phòng điều hành.

Cài đặt

CREATE FUNCTION EDITDSNVIEN()
RETURNS TABLE
AS
    RETURN (
        SELECT HONV, TENLOT, TENNV
        FROM NHANVIEN, PHONGBAN
        WHERE TENPHG = 'Nghien cuu' AND PHG = MAPHG
    )
GO

ALTER FUNCTION EDITDSNVIEN()
RETURNS TABLE
AS
    RETURN (
        SELECT HONV, TENLOT, TENNV
        FROM NHANVIEN, PHONGBAN
        WHERE TENPHG = 'Dieu hanh' AND PHG = MAPHG
    )
GO

SELECT *
FROM DBO.EDITDSNVIEN()

Kết quả

Xóa User Defined Function

Cú pháp

DROP FUNCTION function_name

Ví dụ 1: Hãy xóa function có tên EDITDSNVIEN.

DROP FUNCTION EDITDSNVIEN