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 - 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 - Mệnh đề WHERE

SQL Server - Mệnh đề WHERE

Chức năng : Rút trích các dòng dữ liệu từ các quan hệ tham gia truy vấn thỏa mãn điều kiện trong mệnh đề WHERE. Các biểu thức trong mệnh đề WHERE tương ứng với các điều kiện của phép chọn trong đại...

T SQL - Cấu trúc IF ... ELSE ...

T SQL - Cấu trúc IF ... ELSE ...

Câu lệnh IF ... ELSE ... Là một cấu trúc điều kiện, cho phép chúng ta thực hiện hoặc bỏ qua một hay nhiều lệnh dựa trên điều kiện đã chỉ định.

SQL Server - Mệnh đề SELECT

SQL Server - Mệnh đề SELECT

Chức năng: rút trích một vài thông tin, một vài cột từ các quan hệ tham gia câu truy vấn. Câu lệnh SELECT tương ứng với chức năng của phép tham chiếu trong đại số quan hệ.

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.

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...

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ủ...

T SQL - Cấu trúc WHILE

T SQL - Cấu trúc WHILE

Câu lệnh WHILE là một cấu trúc lặp sẽ thực hiện các thao tác lặp cho đến khi biểu thức điều kiện trong câu lệnh WHILE có giá trị Fasle. Biểu thức điều kiện lặp lại có thể là một câu lệnh SELECT

SQL Server - Mệnh đề ORDER BY

SQL Server - Mệnh đề ORDER BY

Chức năng: sắp xếp theo thứ tự tăng dần hoặc giảm dần các dòng dữ liệu trong quan hệ, kết quả theo tiêu chí sắp xếp.

ManhDanBlogs