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

CÓ THỂ BẠN QUAN TÂM

SQL Server - Mệnh đề HAVING

SQL Server - Mệnh đề HAVING

Chức năng: xuất ra kết quả là các dữ liệu thỏa mãn các điều kiện theo từng nhóm quy định trong GROUP BY.

Lập trình T SQL

Lập trình T SQL

T-SQL (Transact SQL) là ngôn ngữ lập trình cơ sở dữ liệu thủ tục độc quyền của Microsoft dành cho SQL Server. Chúng cũng được sử dụng để viết các thủ tục được lưu trữ, là một đoạn mã nằm trên máy chủ...

SQL Server - Lệnh INSERT

SQL Server - Lệnh INSERT

Chức năng: cho phép thêm một hoặc nhiều dòng dữ liệu vào bảng đã tạo sẵn trong cơ sở dữ liệu.

Giới thiệu SQL Server

Giới thiệu SQL Server

Giới thiệu chung SQL (Structured Query Language, đọc là "sequel") là tập lệnh truy xuất trên cơ dữ liệu (CSDL) quan hệ. Ngôn ngữ SQL, được IBM sử dụng đầu tiên trong hệ quản trị CSDL Sytem R vào...

SQL Server - Backup / Restore Database

SQL Server - Backup / Restore Database

Mục đích của việc sao lưu và phục hồi dữ liệu Sao lưu và phục hồi dữ liệu là vấn đề rất quan trọng khi quản trị cơ sở dữ liệu. Hoạt động này đảm bảo cho dữ liệu khi xảy ra sự cố, ta có thể khôi phục...

Cài Đặt SQL Server 2019

Cài Đặt SQL Server 2019

Microsoft SQL Server là một hệ quản trị cơ sở dữ liệu quan hệ được phát triển bởi Microsoft. Là một máy chủ cơ sở dữ liệu, nó là một sản phẩm phần mềm có chức năng chính là lưu trữ và truy xuất dữ liệ...

SQL Server - Constraints

SQL Server - Constraints

Trong một CSDL, luôn luôn tồn tại rất nhiều mối liên hệ ảnh hưởng qua lại lẫn nhau giữa các thuộc tính của một quan hệ, giữa các bộ giá trị trong một quan hệ và giữa các thuộc tính của các bộ giá trị...

SQL Server - Lệnh DELETE

SQL Server - Lệnh DELETE

Chức năng: cho phép người dùng xóa một hoặc nhiều dòng dữ liệu trên bảng dữ liệu đã có sẵn trong cơ sở dữ liệu.

SQL Server - Mệnh đề GROUP BY

SQL Server - Mệnh đề GROUP BY

Chức năng: gom nhóm các dòng dữ liệu theo tiêu chí gom nhóm đặt trong mệnh đề GROUP BY nhằm hỗ trợ và phục vụ các mục đích của câu truy vấn.

ManhDanBlogs