Function trong SQL Server: Hướng dẫn từ A-Z về function

Trong hướng dẫn này, bạn sẽ tìm hiểu mọi thứ bạn cần biết về function do người dùng tự định nghĩa trong SQL Server, bao gồm scalar-valued function (hàm vô hướng) trả về một giá trị đơn và table-valued function (hàm bảng) trả về các bản ghi dữ liệu.

Function do người dùng tự định nghĩa trong SQL Server giúp bạn đơn giản hóa công việc lập trình của mình bằng cách đóng gói các logic nghiệp vụ phức tạp để sử dụng lại trong mọi truy vấn.

Scalar function trong SQL Server

Trong phần này, bạn sẽ tìm hiểu về các scalar function (hàm vô hướng) trong SQL Server và cách sử dụng chúng để đóng gói các công thức hoặc logic nghiệp vụ và sử dụng lại chúng trong các truy vấn.

Scalar function là gì?

Scalar function (hàm vô hướng) trong SQL Server yêu cầu một hoặc nhiều tham số và trả về một giá trị đơn.

Scalar function giúp bạn đơn giản hóa mã của mình. Ví dụ: bạn có thể có một phép tính phức tạp xuất hiện trong nhiều truy vấn SELECT. Thay vì thêm công thức trong mọi truy vấn, bạn có thể tạo một scalar function đóng gói công thức này và sử dụng nó trong mỗi truy vấn.

Tạo scalar function trong SQL Server

Để tạo một scalar function, bạn sử dụng câu lệnh CREATE FUNCTION như sau:

CREATE FUNCTION [schema_name.]function_name 
(
    parameter_list
)
RETURNS data_type AS
BEGIN
    statements
    RETURN value
END

Trong cú pháp này:

• Đầu tiên, chỉ định tên của function sau các từ khóa CREATE FUNCTION. Tên lược đồ là tùy chọn. Nếu bạn không chỉ định rõ ràng, SQL Server sẽ sử dụng lược đồ dbo theo mặc định.
• Thứ hai, chỉ định một danh sách các tham số được bao quanh bởi cặp dấu ngoặc đơn sau tên function.
• Thứ ba, chỉ định kiểu dữ liệu của giá trị trả về trong câu lệnh RETURNS.
• Cuối cùng, thêm một câu lệnh RETURN để trả về một giá trị bên trong phần thân của function.

Lưu ý: kiểu dữ liệu trả về trong thân của function phải giống với kiểu dữ liệu được khai báo sau từ khóa RETURNS.

Ví dụ sau đây tạo ra một function tính toán doanh thu thuần dựa trên số lượng, giá niêm yết và chiết khấu:

CREATE FUNCTION sales.udfNetSale
(
    @quantity INT,
    @list_price DEC(10,2),
    @discount DEC(4,2)
)
RETURNS DEC(10,2)
AS 
BEGIN
    RETURN @quantity * @list_price * (1 - @discount);
END;

Sau đó, chúng ta có thể sử dụng function udfNetSale để tính toán doanh thu ròng của bất kỳ đơn đặt hàng trong bảng order_items từ cơ sở dữ liệu mẫu BikeStores.

Sau khi tạo scalar function, bạn có thể tìm thấy nó trong Programmability > Functions > Scalar-valued Functions như trong hình sau:

Gọi scalar function trong SQL Server

Bạn gọi một scalar function (hàm vô hướng) giống như hàm tích hợp. Ví dụ, câu lệnh sau minh họa cách gọi function udfNetSale:

SELECT 
    sales.udfNetSale(10,100,0.1) net_sale;

Đây là kết quả đầu ra:

Ví dụ sau minh họa cách sử dụng function sales.udfNetSale để có được doanh thu thuần của các đơn đặt hàng trong bảng order_items:

SELECT 
    order_id, 
    SUM(sales.udfNetSale(quantity, list_price, discount)) net_amount
FROM 
    sales.order_items
GROUP BY 
    order_id
ORDER BY
    net_amount DESC;

Hình ảnh sau đây cho thấy một phần kết quả đầu ra:

Chỉnh sửa scalar function trong SQL Server

Để chỉnh sửa một scalar function, bạn sử dụng từ khóa ALTER thay vì CREATE như sau:

ALTER FUNCTION [schema_name.]function_name 
(
    parameter_list
)
RETURN data_type AS
BEGIN
    statements
    RETURN value
END

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

CREATE OR ALTER FUNCTION [schema_name.]function_name 
(
    parameter_list
)
RETURN data_type AS
BEGIN
    statements
    RETURN value
END

Xóa scalar function trong SQL Server

Để xóa một scalar function (hàm vô hướng) hiện có, bạn sử dụng câu lệnh DROP FUNCTION như sau:

DROP FUNCTION [schema_name.]function_name;

Ví dụ: để xóa function sales.udfNetSale, bạn sử dụng câu lệnh sau:

DROP FUNCTION sales.udfNetSale;

Những điểm cần nhớ về scalar function trong SQL Server

Sau đây là một số điểm chính của các scalar function trong SQL Server:

• Scalar function có thể được sử dụng ở hầu hết mọi nơi trong các câu lệnh T-SQL.
• Scalar function chấp nhận một hoặc nhiều tham số nhưng chỉ trả về một giá trị đơn, do đó chúng phải có một câu lệnh RETURN.
• Scalar function có thể sử dụng logic như khối IF hoặc vòng lặp WHILE.
• Scalar function không thể UPDATE dữ liệu. Nó có thể truy cập dữ liệu nhưng đây không phải là một thực hành tốt.
• Scalar function có thể gọi các function khác.

Trong phần này, bạn đã học cách sử dụng scalar function của SQL Server để đóng gói các công thức phức tạp hoặc logic nghiệp vụ phức tạp và sử dụng lại chúng trong các truy vấn.

Biến kiểu bảng trong SQL Server

Trong phần này, bạn sẽ tìm hiểu về biến kiểu bảng (table variable) trong SQL Server để lưu trữ các bản ghi dữ liệu.

Biến kiểu bảng là gì?

Biến kiểu bảng là loại biến cho phép bạn lưu trữ các bản ghi dữ liệu, tương tự như các bảng tạm.

Cách khai báo biến kiểu bảng trong SQL Server

Để khai báo một biến kiểu bảng, bạn sử dụng câu lệnh DECLARE như sau:

DECLARE @table_variable_name TABLE 
(
    column_list
);

Trong cú pháp này, bạn chỉ định tên của biến kiểu bảng ở giữa từ khóa DECLARE và TABLE. Tên của các biến kiểu bảng phải bắt đầu bằng ký tự @.

Theo sau từ khóa TABLE, bạn định nghĩa cấu trúc của biến kiểu bảng tương tự như cấu trúc của bảng thông thường bao gồm định nghĩa các cột, kiểu dữ liệu, kích thước, ràng buộc tùy chọn, v.v.

Phạm vi của các biến kiểu bảng trong SQL Server

Tương tự như các biến cục bộ, biến kiểu bảng sẽ không còn tồn tại sau khi kết thúc khối lệnh.

Nếu bạn định nghĩa một biến kiểu bảng trong một stored procedure hoặc function, biến kiểu bảng sẽ không còn tồn tại sau khi stored procedure hoặc function kết thúc.

Ví dụ về biến kiểu bảng trong SQL Server

Ví dụ, câu lệnh sau khai báo một biến kiểu bảng tên là @product_table, nó có ba cột: product_name, brand_id và list_price:

DECLARE @product_table TABLE 
(
    product_name VARCHAR(MAX) NOT NULL,
    brand_id INT NOT NULL,
    list_price DEC(11,2) NOT NULL
);

Chèn dữ liệu vào biến kiểu bảng

Sau khi khai báo, biến kiểu bảng sẽ chưa có dữ liệu. Bạn có thể chèn các bản ghi vào biến kiểu bảng bằng cách sử dụng câu lệnh INSERT:

INSERT INTO @product_table
SELECT
    product_name,
    brand_id,
    list_price
FROM
    production.products
WHERE
    category_id = 1;

Truy vấn dữ liệu trong biến kiểu bảng

Tương tự như bảng tạm, bạn có thể truy vấn dữ liệu từ biến kiểu bảng bằng cách sử dụng câu lệnh SELECT như sau:

SELECT
    *
FROM
    @product_table;

Lưu ý rằng bạn cần phải thực thi toàn bộ khối câu lệnh nếu không sẽ gặp lỗi:

DECLARE @product_table TABLE (
    product_name VARCHAR(MAX) NOT NULL,
    brand_id INT NOT NULL,
    list_price DEC(11,2) NOT NULL
);

INSERT INTO @product_table
SELECT
    product_name,
    brand_id,
    list_price
FROM
    production.products
WHERE
    category_id = 1;

SELECT
    *
FROM
    @product_table;
GO

Hình ảnh sau đây cho thấy một phần kết quả đầu ra:

Hạn chế của biến kiểu bảng trong SQL Server

Đầu tiên, bạn phải định nghĩa cấu trúc của biến kiểu bảng. Không giống như bảng thông thường hoặc bảng tạm, bạn không thể thay đổi cấu trúc của các biến kiểu bảng sau khi chúng được khai báo.

Thứ hai, số liệu thống kê giúp trình tối ưu hóa truy vấn đưa ra kế hoạch thực hiện truy vấn tốt. Thật không may, các biến kiểu bảng không chứa số liệu thống kê. Do đó, bạn chỉ nên sử dụng các biến kiểu bảng để lưu trữ một số lượng nhỏ bản ghi.

Thứ ba, bạn không thể sử dụng biến kiểu bảng làm tham số đầu vào hoặc đầu ra như các kiểu dữ liệu khác. Tuy nhiên, bạn có thể trả về biến kiểu bảng từ function.

Thứ tư, bạn không thể tạo non-clustered index cho biến kiểu bảng. Tuy nhiên, bắt đầu từ SQL Server 2014, biến kiểu bảng được tối ưu hóa bộ nhớ có sẵn với việc giới thiệu OLTP trong bộ nhớ mới cho phép bạn thêm non-clustered index như một phần của khai báo biến kiểu bảng.

Thứ năm, nếu bạn đang sử dụng biến kiểu bảng với JOIN, bạn cần đặt bí danh cho bảng để thực hiện truy vấn. Ví dụ:

SELECT
    brand_name,
    product_name,
    list_price
FROM
    brands b
INNER JOIN @product_table pt 
    ON p.brand_id = pt.brand_id;

Hiệu suất của biến kiểu bảng trong SQL Server

Sử dụng các biến kiểu bảng trong một stored procedure dẫn đến việc biên dịch lại ít hơn so với sử dụng bảng tạm.

Ngoài ra, một biến kiểu bảng sử dụng ít tài nguyên hơn một bảng tạm với ít chi phí khóa và ghi nhật ký hơn.

Tương tự như bảng tạm, các biến kiểu bảng thực hiện trong cơ sở dữ liệu tempdb, không phải trong bộ nhớ.

Sử dụng biến kiểu bảng trong function do người dùng định nghĩa

Function do người dùng định nghĩa sau có tên là ufnSplit() trả về biến kiểu bảng.

CREATE OR ALTER FUNCTION udfSplit(
    @string VARCHAR(MAX), 
    @delimiter VARCHAR(50) = ' ')
RETURNS @parts TABLE
(    
idx INT IDENTITY PRIMARY KEY,
val VARCHAR(MAX)   
)
AS
BEGIN

DECLARE @index INT = -1;

WHILE (LEN(@string) > 0) 
BEGIN 
    SET @index = CHARINDEX(@delimiter , @string)  ;
    
    IF (@index = 0) AND (LEN(@string) > 0)  
    BEGIN  
        INSERT INTO @parts 
        VALUES (@string);
        BREAK  
    END 

    IF (@index > 1)  
    BEGIN  
        INSERT INTO @parts 
        VALUES (LEFT(@string, @index - 1));
        
        SET @string = RIGHT(@string, (LEN(@string) - @index));  
    END 
    ELSE
    SET @string = RIGHT(@string, (LEN(@string) - @index)); 
    END
RETURN
END
GO

Câu lệnh sau gọi function udfSplit():

SELECT 
    * 
FROM 
    udfSplit('foo,bar,baz',',');

Đây là kết quả đầu ra:

Trong phần này, bạn đã tìm hiểu cách sử dụng biến kiểu bảng trong SQL Server giúp mang lại một số lợi ích hiệu suất và tính linh hoạt so với các bảng tạm.

Table function trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng table function trong SQL Server bao gồm table function đơn câu lệnh và table function đa câu lệnh.

Table function trong SQL Server là gì

Table function (hàm bảng) là function do người dùng tự định nghĩa trả về dữ liệu kiểu bảng. Kiểu trả về của table function là một bảng, do đó bạn có thể sử dụng table function giống như bạn sẽ sử dụng bảng.

Tạo table function trong SQL Server

Câu lệnh sau đây tạo ra một table function trả về danh sách các sản phẩm bao gồm tên sản phẩm, năm model và giá niêm yết cho một năm model cụ thể:

CREATE FUNCTION udfProductInYear 
(
    @model_year INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;

Cú pháp tương tự như cú pháp tạo function do người dùng định nghĩa.

Câu lệnh RETURNS TABLE xác định function sẽ trả về một bảng. Như bạn có thể thấy, không có khối lệnh BEGIN...END. Câu lệnh chỉ đơn giản là trả về truy vấn dữ liệu từ bảng production.products.

Function udfProductInYear yêu cầu một tham số có tên @model_year kiểu INT. Nó trả về các sản phẩm có năm model bằng tham số @model_year.

Khi table function được tạo, bạn có thể tìm thấy nó trong Programmability > Functions > Table-valued Functions như trong hình sau:

Function trên trả về tập kết quả của một câu lệnh SELECT, do đó nó còn được gọi là table function đơn câu lệnh.

Thực thi table function trong SQL Server

Để thực thi một table function, bạn sử dụng nó trong mệnh đề FROM của câu lệnh SELECT như sau:

SELECT 
    * 
FROM 
    udfProductInYear(2017);

Đây là một phần kết quả đầu ra:

Trong ví dụ này, chúng tôi đã chọn các sản phẩm có năm model là  2017.

Bạn cũng có thể chỉ định các cột được trả về từ table function như sau:

SELECT 
    product_name,
    list_price
FROM 
    udfProductInYear(2018);

Đây là đầu ra một phần:

Sửa đổi table function trong SQL Server

Để sửa đổi table function, bạn sử dụng từ khóa ALTER thay vì CREATE rồi thay đổi các thành phần bên trong fuction theo nhu cầu của bạn.

Ví dụ: câu lệnh sau sửa đổi function udfProductInYear bằng cách thay đổi tham số hiện có và thêm một tham số mới:

ALTER FUNCTION udfProductInYear (
    @start_year INT,
    @end_year INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year BETWEEN @start_year AND @end_year

Function udfProductInYear bây giờ trở về các sản phẩm mà năm model nằm trong khoảng từ năm bắt đầu đến năm kết thúc.

Câu lệnh sau gọi function udfProductInYear để lấy các sản phẩm có năm model từ năm 2017 đến năm 2018:

SELECT 
    product_name,
    model_year,
    list_price
FROM 
    udfProductInYear(2017,2018)
ORDER BY
    product_name;

Đây là một phần kết quả đầu ra:

Table function đa câu lệnh trong SQL Server

Table function đa câu lệnh hoặc MSTVF là function có nhiều câu lệnh và trả về giá trị kiểu bảng.

Table function rất hữu ích vì bạn có thể thực hiện nhiều truy vấn trong function và tổng hợp kết quả vào bảng được trả về.

Để định nghĩa table function, bạn sử dụng biến kiểu bảng làm giá trị trả về. Bên trong function, bạn thực hiện một hoặc nhiều truy vấn và chèn dữ liệu vào biến kiểu bảng này.

Function udfContacts() sau đây kết hợp nhân viên và khách hàng vào một danh sách liên lạc duy nhất:

CREATE FUNCTION udfContacts()
    RETURNS @contacts TABLE (
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        email VARCHAR(255),
        phone VARCHAR(25),
        contact_type VARCHAR(20)
    )
AS
BEGIN
    INSERT INTO @contacts
    SELECT 
        first_name, 
        last_name, 
        email, 
        phone,
        'Staff'
    FROM
        sales.staffs;

    INSERT INTO @contacts
    SELECT 
        first_name, 
        last_name, 
        email, 
        phone,
        'Customer'
    FROM
        sales.customers;
    RETURN;
END;

Câu lệnh sau minh họa cách thực thi function udfContacts:

SELECT 
    * 
FROM
    udfContacts();

Đây là một phần kết quả đầu ra:

Khi nào nên sử dụng table function trong SQL Server

Chúng tôi thường sử dụng table function như là một dạng view có tham số (view động). So với stored procedure, table function linh hoạt hơn bởi vì chúng ta có thể sử dụng chúng ở bất cứ nơi nào bảng được sử dụng.

Trong phần này, bạn đã tìm hiểu về table function trong SQL Server bao gồm table function đơn câu lệnh và table function đa câu lệnh.

Xóa function trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách xóa function do người dùng tự định nghĩa bằng cách sử dụng câu lệnh DROP FUNCTION trong SQL Server.

Giới thiệu về câu lệnh DROP FUNCTION trong SQL Server

Để xóa function do người dùng tự định nghĩa được tạo bởi câu lệnh CREATE FUNCTION, bạn sử dụng câu lệnh DROP FUNCTION như sau:

DROP FUNCTION [ IF EXISTS ] [ schema_name. ] function_name;

Trong cú pháp này:

• Tùy chọn IF EXISTS cho phép bạn xóa function nếu nó tồn tại. Nếu function không tồn tại thì câu lệnh sẽ không làm gì cả. Nếu bạn cố gắng xóa một function không tồn tại mà không chỉ định tùy chọn IF EXISTS, bạn sẽ gặp lỗi.
• Tùy chọn schema_name chỉ định tên của lược đồ mà function do người dùng tự định nghĩa thuộc về. Tên lược đồ là tùy chọn.
• function_name là tên của function mà bạn muốn xóa.

Ghi chú:

Nếu function mà bạn muốn xóa được tham chiếu trong view hoặc function khác được tạo bằng tùy chọn WITH SCHEMABINDING thì câu lệnh DROP FUNCTION sẽ thất bại.

Ngoài ra, nếu có các ràng buộc như CHECK hoặc DEFAULT và các cột được tính toán liên quan đến function, câu lệnh DROP FUNCTION cũng sẽ thất bại.

Để xóa nhiều function do người dùng tự định nghĩa, bạn chỉ định danh sách tên function được phân tách bằng dấu phẩy sau mệnh đề DROP FUNCTION như sau:

DROP FUNCTION [IF EXISTS] 
    schema_name.function_name1, 
    schema_name.function_name2,
    ...;

Ví dụ về câu lệnh DROP FUNCTION trong SQL Server

Chúng tôi sẽ sử dụng bảng order_items từ cơ sở dữ liệu mẫu BikeStores để minh họa xóa function trong SQL Server.

Ví dụ xóa function đơn giản trong SQL Server

Ví dụ sau đây tạo ra một function tính số tiền chiết khấu từ số lượng, giá niêm yết và tỷ lệ chiết khấu:

CREATE FUNCTION sales.udf_get_discount_amount 
(
    @quantity INT,
    @list_price DEC(10,2),
    @discount DEC(4,2) 
)
RETURNS DEC(10,2) 
AS 
BEGIN
    RETURN @quantity * @list_price * @discount
END

Để xóa function sales.udf_get_discount_amount, bạn sử dụng câu lệnh sau:

DROP FUNCTION IF EXISTS sales.udf_get_discount_amount;

Ví dụ xóa function có WITH SCHEMABINDING trong SQL Server

Ví dụ sau đây tạo lại function sales.udf_get_discount_amount bằng cách sử dụng tùy chọn WITH SCHEMABINDING:

CREATE FUNCTION sales.udf_get_discount_amount 
(
    @quantity INT,
    @list_price DEC(10,2),
    @discount DEC(4,2) 
)
RETURNS DEC(10,2) 
WITH SCHEMABINDING
AS 
BEGIN
    RETURN @quantity * @list_price * @discount
END

Và câu lệnh sau đây tạo view sales.discounts sử dụng function sales.udf_get_discount_amount như sau:

CREATE VIEW sales.discounts
WITH SCHEMABINDING
AS
SELECT
    order_id,
    SUM(sales.udf_get_discount_amount(
        quantity,
        list_price,
        discount
    )) AS discount_amount
FROM
    sales.order_items i
GROUP BY
    order_id;

Bây giờ, nếu bạn cố gắng xóa function sales.udf_get_discount_amount, bạn sẽ gặp lỗi:

DROP FUNCTION sales.udf_get_discount_amount;

SQL Server trả về lỗi sau:

Cannot DROP FUNCTION 'sales.udf_get_discount_amount' because it is being referenced by object 'discounts'.

Nếu bạn muốn xóa function này, trước tiên bạn phải xóa view sales.discounts như sau:

DROP VIEW sales.discounts;

Và sau đó thực thi lại câu lệnh xóa function:

DROP FUNCTION sales.udf_get_discount_amount;

Trong phần này, bạn đã học cách sử dụng câu lệnh DROP FUNCTION trong SQL Server để xóa một hoặc nhiều function do người dùng tự định nghĩa.