SQL Server: FUNCTION (Hàm) Tự Tạo Trong SQL Server | V1Study

Bạn có thể tự tạo một số đối tượng của riêng bạn trong cơ sở dữ liệu và một trong số này chắc chắn là bạn tự tạo được hàm. Khi được sử dụng thì hàm là một công cụ rất mạnh góp phần quản trị cơ sở dữ liệu.

Trong bài học này ta sẽ tìm hiểu cách tạo, gọi, thay đổi và xóa hàm.

Một hàm do người dùng tự tạo là một quy trình Transact-SQL chấp nhận các tham số, thực hiện một công việc cụ thể, chẳng hạn như một phép tính phức tạp và trả về kết quả của công việc đó dưới dạng một giá trị. Giá trị trả về có thể là một giá trị vô hướng (bất kỳ) hoặc một bảng (table). Việc tạo hàm sẽ tạo một quy trình tái sử dụng và ta có thể sử dụng trong những tình huống sau:

• Trong câu lệnh Transact-SQL chẳng hạn như SELECT
• Trong các ứng dụng gọi hàm
• Trong định nghĩa của một hàm khác do người dùng tự tạo
• Để tham số hóa View hoặc cải thiện chức năng của View được lập chỉ mục
• Để tạo một cột trong bảng
• Để tạo ràng buộc CHECK trên một cột
• Để thay thế một thủ tục đã lưu trữ
• Sử dụng hàm nội tuyến làm bộ lọc cho vấn đề bảo mật.

Đối tượng máy chủ SQL

Như đã đề cập ở trên, có nhiều loại đối tượng khác nhau mà bạn có thể tạo trong cơ sở dữ liệu. Bên cạnh bảng và khóa, ta còn có các đối tượng nổi tiếng khác như là thủ tục lưu trữ (SP), trigger và view. Và, tất nhiên, các hàm tự tạo cũng là các đối tượng và là chủ đề của bài học này. Ý tưởng chính đằng sau các đối tượng là lưu trữ chúng trong cơ sở dữ liệu và tránh viết đi viết lại cùng một đoạn mã. Ngoài ra, bạn có thể kiểm soát đầu vào là gì và xác định cấu trúc/loại đầu ra. Và cuối cùng, nhưng không kém phần quan trọng, bạn có thể xác định các quyền để quyết định ai sẽ có thể sử dụng chúng và người đó sẽ có thể làm điều đó theo cách nào.

Việc tạo một hàm có một số ưu điểm chính sau:

• Mã phức tạp được lưu trữ trong một cấu trúc. Sau đó, bạn có thể xem cấu trúc đó như trên hộp đen, lúc này bạn chỉ cần quan tâm đến việc truyền các giá trị thích hợp làm tham số và hàm sẽ thực hiện phần việc còn lại
• Bạn có thể kiểm tra các tham số đầu vào dễ dàng hơn nhiều bằng cách sử dụng IF-ELSE hoặc CASE-WHEN, và thậm chí sử dụng các vòng lặp trong các hàm. Điều này đôi khi rất khó (đôi khi không thể) để mô phỏng trực tiếp trong các câu lệnh SELECT
• Khi bạn tạo một hàm và sau khi nó được kiểm thử và chạy chuẩn rồi thì bạn không phải bận tâm về sau nó có hoạt động như mong đợi hay không và bạn đang tránh khả năng mắc lỗi vì bạn không viết đi viết lại cùng một đoạn mã (chưa kể rằng bạn sẽ sử dụng ít thời gian hơn khi không viết lại cùng một đoạn mã)
• Nếu bạn cần thay đổi mã của mình, bạn sẽ thực hiện ở một nơi và nó sẽ được áp dụng ở mọi nơi mà hàm được sử dụng (được gọi).

Cú pháp tạo/sửa hàm (Create, Alter function)

Cú pháp tạo hàm vô hướng (không trả về bảng):

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ ,...n ] ] ) RETURNS return_data_type [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END [ ; ]

Cú pháp tạo hàm trả về bảng (table):

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ ,...n ] ] ) RETURNS TABLE [ WITH <function_option> [ ,...n ] ] [ AS ] RETURN [ ( ] select_stmt [ ) ] [ ; ]

Cú pháp tạo hàm trả về bảng với nhiều lệnh:

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] [READONLY] } [ ,...n ] ] ) RETURNS @return_variable TABLE <table_type_definition> [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN function_body RETURN END [ ; ]

Giải thích cú pháp:

+ <function_option>: tùy chọn thêm cho việc tạo/sửa hàm, có thể là các tùy chọn sau:

[ ENCRYPTION ] | [ SCHEMABINDING ] | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] | [ EXECUTE_AS_Clause ] | [ INLINE = { ON | OFF }]

+ <table_type_definition>: có thể là:

( { <column_definition> <column_constraint> | <computed_column_definition> } [ <table_constraint> ] [ ,...n ] )

+ <column_definition>: có thể là:

{ { column_name data_type } [ [ DEFAULT constant_expression ] [ COLLATE collation_name ] | [ ROWGUIDCOL ] ] | [ IDENTITY [ (seed , increment ) ] ] [ <column_constraint> [ ...n ] ] }

+ <column_constraint>: có thể là các tùy chọn sau:

{ [ NULL | NOT NULL ] { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( < index_option > [ , ...n ] ) [ ON { filegroup | "default" } ] | [ CHECK ( logical_expression ) ] [ ,...n ] }

+ <computed_column_definition>: column_name AS computed_column_expression

+ <table_constraint>: Có thể là các tùy chọn sau:

{ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ ,...n ] [ WITH FILLFACTOR = fillfactor | WITH ( <index_option> [ , ...n ] ) | [ CHECK ( logical_expression ) ] [ ,...n ] }

+ <index_option>: có thể là các tùy chọn sau:

{ PAD_INDEX = { ON | OFF } | FILLFACTOR = fillfactor | IGNORE_DUP_KEY = { ON | OFF } | STATISTICS_NORECOMPUTE = { ON | OFF } | ALLOW_ROW_LOCKS = { ON | OFF } | ALLOW_PAGE_LOCKS ={ ON | OFF } }

+ OR ALTER : Áp dụng cho SQL Server từ bản 2016 (13.x) SP1 trở lên. Chỉ có tác dụng khi ta đã tạo xong hàm.

+ schema_name : Là tên của lược đồ chứa hàm tự tạo.

+ function_name : Là tên của hàm tự tạo. Tên hàm phải tuân thủ các quy tắc cho số nhận dạng và phải là duy nhất trong cơ sở dữ liệu và lược đồ của nó.

Ghi chú: Cặp ngoặc tròn sau tên hàm là bắt buộc phải có ngay cả khi không có tham số.

+ @parameter_name Là tên tham số trong hàm tự tạo, có thể không có, có một hoặc nhiều tham số.

Một hàm có thể có tối đa 2.100 tham số. Giá trị của mỗi tham số đã khai báo phải được cung cấp bởi người dùng khi hàm được thực thi (được gọi), trừ khi giá trị mặc định cho tham số được xác định.

Chỉ định tên tham số bằng cách sử dụng dấu (@) làm ký tự đầu tiên. Tên tham số phải tuân thủ các quy tắc đặt tên chung. Các tham số là cục bộ cho hàm, nghĩa là ta có thể dùng tên tham số tương tự trong các hàm khác. Các tham số chỉ có thể thay thế cho các hằng số; chúng không thể được sử dụng thay cho tên bảng, tên cột hoặc tên của các đối tượng cơ sở dữ liệu khác.

Ghi chú: ANSI_WARNINGS không được chấp nhận khi bạn chuyển các tham số trong một thủ tục được lưu trữ, hàm do người dùng xác định hoặc khi bạn khai báo và đặt các biến trong một câu lệnh hàng loạt. Ví dụ: nếu một biến được định nghĩa là char(3), và sau đó cho biến đó lưu chuỗi có kích thước lớn hơn 3 thì dữ liệu sẽ được cắt bớt theo kích thước đã xác định và câu lệnh INSERT hoặc UPDATE sẽ vẫn thực hiện được.

+ [type_schema_name.] parameter_data_type : Là kiểu dữ liệu của tham số, và tùy chọn là lược đồ mà nó thuộc về. Đối với các hàm Transact-SQL, tất cả các kiểu dữ liệu, bao gồm cả kiểu dữ liệu tự tạo và kiểu bảng do người dùng tự tạo thì đều được phép ngoại trừ kiểu dữ liệu timestamp. Không thể chỉ định kiểu nonscalar, con trỏ và bảng làm kiểu dữ liệu tham số trong các hàm Transact-SQL.

Nếu type_schema_name không được chỉ định thì Database Engine sẽ tìm kiếm scalar_parameter_data_type theo thứ tự sau:

• Lược đồ chứa tên của kiểu dữ liệu hệ thống SQL Server.
• Lược đồ mặc định của người dùng hiện tại trong cơ sở dữ liệu hiện tại.
• Các lược đồ dbo trong cơ sở dữ liệu hiện tại.

+ [=default] : Là giá trị mặc định cho tham số. Nếu một giá trị mặc định được xác định, hàm có thể được thực thi mà không cần chỉ định giá trị cho tham số đó.

Khi một tham số của hàm có giá trị mặc định, từ khóa DEFAULT phải được chỉ định khi hàm được gọi để lấy giá trị mặc định. Hành vi này khác với việc sử dụng các tham số có giá trị mặc định trong các thủ tục lưu trữ, trong đó việc bỏ qua tham số cũng ngụ ý giá trị mặc định. Tuy nhiên, từ khóa DEFAULT không bắt buộc khi gọi một hàm vô hướng bằng cách sử dụng câu lệnh EXECUTE.

+ READONLY : Chỉ ra rằng tham số không thể được cập nhật hoặc sửa đổi trong định nghĩa của hàm. READONLY là bắt buộc đối với các tham số loại bảng do người dùng xác định (TVP) và không thể được sử dụng cho bất kỳ loại tham số nào khác.

+ return_data_type : Là giá trị trả về của một hàm vô hướng do người dùng định nghĩa. Đối với các hàm Transact-SQL, tất cả các kiểu dữ liệu, bao gồm cả kiểu do người dùng tự tạo, đều được phép ngoại trừ kiểu kiểu timestamp. Không thể chỉ định các kiểu nonscalar, con trỏ và bảng làm kiểu dữ liệu trả về trong các hàm Transact-SQL.

+ function_body : Chỉ định rằng một loạt các câu lệnh Transact-SQL, cùng nhau không tạo ra tác dụng phụ như sửa đổi bảng, xác định giá trị của hàm. function_body chỉ được sử dụng trong các hàm vô hướng và các hàm có giá trị bảng nhiều câu lệnh (MSTVF).

Trong các hàm vô hướng, function_body là một chuỗi các câu lệnh Transact-SQL được thực thi để trả về một giá trị vô hướng.

Trong MSTVFs, function_body là một chuỗi các câu lệnh Transact-SQL được thực thi để trả về TABLE.

+ scalar_expression Chỉ định giá trị vô hướng mà hàm vô hướng trả về.

TABLE chỉ định rằng giá trị trả về của hàm giá trị bảng (TVF) là một bảng. Chỉ các hằng số và các @local_variables mới có thể được truyền tới TVF.

Trong TVF nội tuyến, giá trị trả về TABLE được xác định thông qua một câu lệnh SELECT. Các hàm nội tuyến không có các biến liên quan được trả về.

Trong MSTVF, @return_variable là một biến TABLE, được sử dụng để lưu trữ và tích lũy các hàng sẽ được trả về dưới dạng giá trị của hàm. @return_variable chỉ có thể được chỉ định cho các hàm Transact-SQL.

+ select_stmt : Là câu lệnh SELECT đơn xác định giá trị trả về của một hàm giá trị bảng nội tuyến (TVF).

+ ORDER (<order_clause>) : Chỉ định thứ tự các kết quả được trả về từ hàm giá trị bảng.

+ EXTERNAL NAME <method_specifier> assembly_name . tên_ lớp . method_name : Áp dụng cho : SQL Server (SQL Server 2008 SP1 trở lên)

Chỉ định assembly và phương thức mà tên hàm đã tạo sẽ tham chiếu đến.

• assembly_name : phải khớp với một giá trị trong cột name của SELECT * FROM sys.assemblies;.

Đây là tên đã được sử dụng trong câu lệnh CREATE ASSEMBLY.

• class_name : phải khớp với một giá trị trong cột assembly_name của SELECT * FROM sys.assembly_modules;.

Thường giá trị chứa dấu chấm hoặc dấu chấm được nhúng. Trong những trường hợp như vậy, cú pháp Transact-SQL yêu cầu giá trị được giới hạn bằng một cặp dấu ngoặc vuông [] hoặc bằng một cặp dấu nháy kép "".

• method_name : phải khớp với một giá trị trong cột method_name của SELECT * FROM sys.assembly_modules;.

Phương thức phải là tĩnh (status).

+ <table_type_definition> ({<column_definition> <column_constraint> | <computed_column_definition>} [<table_constraint>] [, ... n ]) : Xác định kiểu dữ liệu bảng cho một hàm Transact-SQL. Khai báo bảng bao gồm các định nghĩa cột và các ràng buộc cột hoặc bảng. Bảng luôn được đặt trong nhóm tệp chính.

+ NULL | NOT NULL : Chỉ được hỗ trợ cho các hàm vô hướng, được biên dịch nguyên bản do người dùng định nghĩa.

+ NATIVE_COMPILATION : Cho biết liệu tự tạo có được biên dịch nguyên bản hay không. Đối số này là bắt buộc đối với các hàm vô hướng thì hàm tự tạo sẽ được biên dịch nguyên bản.

+ BEGIN ATOMIC WITH : Chỉ được hỗ trợ cho các hàm vô hướng, được biên dịch nguyên bản và được yêu cầu.

+ SCHEMABINDING : Đối số SCHEMABINDING là bắt buộc đối với các hàm vô hướng, được biên dịch nguyên bản.

+ EXECUTE AS EXECUTE AS : Được yêu cầu cho các hàm vô hướng, được biên dịch nguyên bản do người dùng định nghĩa.

+ <function_option> : Chỉ định rằng hàm sẽ có một hoặc nhiều tùy chọn sau:

ENCRYPTION : Áp dụng cho : SQL Server (SQL Server 2008 SP1 trở lên)

Tùy chọn này cho biết rằng Database Engine sẽ chuyển đổi văn bản gốc của câu lệnh CREATE FUNCTION sang định dạng xáo trộn. Đầu ra của obfuscation không hiển thị trực tiếp trong bất kỳ chế độ xem danh mục nào. Người dùng không có quyền truy cập vào bảng hệ thống hoặc tệp cơ sở dữ liệu không thể truy xuất văn bản xáo trộn. Tuy nhiên, văn bản sẽ có sẵn cho những người dùng có đặc quyền có thể truy cập các bảng hệ thống qua cổng DAC hoặc truy cập trực tiếp vào các tệp cơ sở dữ liệu. Ngoài ra, người dùng có thể đính kèm trình gỡ lỗi vào quy trình máy chủ có thể truy xuất quy trình ban đầu từ bộ nhớ trong thời gian chạy.

Sử dụng tùy chọn này sẽ ngăn không cho người dùng thấy được định nghĩa hàm.

SCHEMABINDING : Chỉ định rằng hàm được liên kết với các đối tượng cơ sở dữ liệu mà nó tham chiếu. Khi SCHEMABINDING được chỉ định, các đối tượng cơ sở không thể được sửa đổi theo cách có thể ảnh hưởng đến định nghĩa hàm. Bản thân định nghĩa hàm trước tiên phải được sửa đổi hoặc loại bỏ để loại bỏ các phụ thuộc vào đối tượng sẽ được sửa đổi.

Sự ràng buộc của hàm với các đối tượng mà nó tham chiếu chỉ bị loại bỏ khi không xảy ra các hành động sau:

• Hàm bị loại bỏ (xóa hàm).
• Hàm được sửa đổi bằng cách sử dụng câu lệnh ALTER trong đó không chỉ định tùy chọn SCHEMABINDING.

Một hàm chỉ có thể bị ràng buộc giản đồ nếu các điều kiện sau là đúng:

• Hàm là một hàm Transact-SQL.
• Các hàm tự tạo và các view được tham chiếu bởi hàm cũng bị ràng buộc bởi lược đồ.
• Các đối tượng được tham chiếu bởi hàm được tham chiếu bằng tên gồm hai phần.
• Hàm và các đối tượng mà nó tham chiếu thuộc cùng một cơ sở dữ liệu.
• Người dùng đã thực thi câu lệnh CREATE FUNCTION mà có quyền REFERENCES đối với các đối tượng cơ sở dữ liệu mà hàm tham chiếu.

+ RETURN NULL ON NULL INPUT | CALLED ON NULL INPUT : Chỉ định thuộc tính OnNULLCall của một hàm vô hướng. Nếu không được chỉ định thì CALLED ON NULL INPUT sẽ là mặc định. Điều này có nghĩa là thân hàm thực thi ngay cả khi NULL được truyền dưới dạng đối số.

Mệnh đề EXECUTE AS : Chỉ định ngữ cảnh bảo mật mà theo đó hàm tự tạo được thực thi. Do đó, bạn có thể kiểm soát tài khoản người dùng mà SQL Server sử dụng để xác thực quyền trên bất kỳ đối tượng cơ sở dữ liệu nào được tham chiếu bởi hàm.

Ghi chú: EXECUTE AS không thể được chỉ định cho các hàm có giá trị bảng nội tuyến.

Để biết thêm thông tin, hãy xem lại Mệnh đề EXECUTE AS (Transact-SQL).

+ INLINE = {ON | OFF} : Áp dụng cho SQL Server 2019 trở lên.

Chỉ định liệu UDF vô hướng này có nên được nội tuyến hay không. Mệnh đề này chỉ áp dụng cho các hàm vô hướng tự tạo. Mệnh đề INLINE là không bắt buộc. Nếu mệnh đề INLINE không được chỉ định thì nó sẽ tự động được đặt thành ON/OFF dựa trên việc UDF có nội tuyến hay không. Nếu INLINE=ON được chỉ định nhưng UDF được phát hiện là không thể nội tuyến thì lỗi sẽ được đưa ra.

+ <column_definition> : Định nghĩa kiểu dữ liệu bảng. Khai báo bảng bao gồm các định nghĩa cột và các ràng buộc.

+ column_name : Là tên của một cột trong bảng. Tên cột phải tuân thủ các quy tắc đặt tên chung và phải là duy nhất trong bảng. column_name có thể bao gồm từ 1 đến 128 ký tự.

+ data_type : Chỉ định kiểu dữ liệu cột. Đối với các hàm Transact-SQL, tất cả các kiểu dữ liệu, bao gồm cả các kiểu tự tạo thì đều được phép ngoại trừ kiểu timestamp. Con trỏ kiểu nonscalar không thể được chỉ định làm kiểu dữ liệu cột.

+ DEFAULT constant_expression : Chỉ định giá trị cung cấp cho cột khi cột không được cung cấp giá trị trong quá trình INSERT. constant_expression là một hằng số, NULL hoặc một giá trị hàm hệ thống. Định nghĩa DEFAULT có thể được áp dụng cho bất kỳ cột nào ngoại trừ những cột có thuộc tính IDENTITY.

+ COLLATE collation_name : Chỉ định đối chiếu cho cột. Nếu không được chỉ định thì cột sẽ được chỉ định đối chiếu mặc định của cơ sở dữ liệu. Tên đối chiếu có thể là tên đối chiếu Windows hoặc tên đối chiếu SQL.

Mệnh đề COLLATE chỉ có thể được sử dụng để thay đổi đối chiếu của các cột thuộc kiểu dữ liệu char, varchar, nchar và nvarchar.

+ ROWGUIDCOL : Cho biết rằng cột mới là một cột định danh duy nhất trên toàn cầu (tìm hiểu thêm về GUID). Chỉ được phép có duy nhất một cột uniqueidentifier cho mỗi bảng có thể được chỉ định là cột ROWGUIDCOL. Thuộc tính ROWGUIDCOL có thể được gán duy nhất một cột uniqueidentifier.

Thuộc tính ROWGUIDCOL không thực thi tính duy nhất của các giá trị được lưu trữ trong cột. Nó cũng không tự động tạo giá trị cho các hàng mới được chèn vào bảng. Để tạo các giá trị duy nhất cho mỗi cột, hãy sử dụng hàm NEWID() trên các câu lệnh INSERT. Giá trị mặc định có thể được chỉ định; tuy nhiên, NEWID() không thể được chỉ định để tạo giá trị mặc định.

+ IDENTITY : Chỉ ra rằng cột mới là một cột nhận dạng. Khi một hàng mới được thêm vào bảng, SQL Server cung cấp một giá trị gia tăng, duy nhất cho cột. Các cột nhận dạng thường được sử dụng cùng với ràng buộc KHÓA CHÍNH để dùng làm mã định danh hàng duy nhất cho bảng. Thuộc tính IDENTITY có thể được gán cho các cột tinyint , smallint , int , bigint , decimal(p,0) hoặc numeric(p,0). Mỗi bảng chỉ có thể tạo một cột nhận dạng. Không thể sử dụng ràng buộc identity và ràng buộc DEFAULT với cột nhận dạng. Bạn phải hoặc là chỉ định cả giá trị ban đầu (seed) và bước nhảy (increment) hoặc là không chỉ định cả hai. Nếu cả hai đều không được chỉ định, giá trị mặc định là (1,1).

+ <column_constraint> và <table_constraint>

Xác định ràng buộc cho một cột hoặc bảng cụ thể.

+ NULL | NOT NULL : Xác định xem giá trị null có được phép trong cột hay không. NULL không hoàn toàn là một ràng buộc nhưng có thể được chỉ định giống như NOT NULL.

+ PRIMARY KEY : Là một ràng buộc thực thi tính toàn vẹn của thực thể đối với một cột được chỉ định thông qua một chỉ mục duy nhất. Trong các hàm tự tạo mà trả về bảng thì ta chỉ có thể tạo ràng buộc PRIMARY KEY trên một cột cho mỗi bảng.

+ UNIQUE : Là một ràng buộc cung cấp tính toàn vẹn của thực thể cho một cột hoặc các cột được chỉ định thông qua một chỉ mục duy nhất. Một bảng có thể có nhiều ràng buộc UNIQUE.

+ CLUSTERED | NONCLUSTERED : Cho biết rằng một chỉ mục được phân cum hoặc không phân cum được tạo cho KHÓA CHÍNH hoặc ràng buộc UNIQUE. Ràng buộc KHÓA CHÍNH sử dụng CLUSTERED và ràng buộc UNIQUE sử dụng NONCLUSTERED.

CLUSTERED chỉ có thể được chỉ định cho một ràng buộc. Nếu CLUSTERED được chỉ định cho một ràng buộc UNIQUE và một ràng buộc PRIMARY KEY cũng được chỉ định, thì PRIMARY KEY sẽ sử dụng NONCLUSTERED.

CHECK : Là một ràng buộc thực thi tính toàn vẹn của miền bằng cách giới hạn các giá trị khả dĩ có thể được nhập vào một cột hoặc nhiều cột.

+ logic_expression : Là một biểu thức logic trả về TRUE hoặc FALSE.

+ <computed_column_definition>

Chỉ định một cột được tính toán. Để biết thêm thông tin về các cột được tính toán, hãy xem TẠO BẢNG (Transact-SQL).

+ column_name : Là tên của cột được tính toán.

+ computed_column_expression : Là một biểu thức xác định giá trị của một cột được tính toán.

+ <index_option>

Chỉ định các tùy chọn chỉ mục cho KHÓA CHÍNH hoặc UNIQUE.

PAD_INDEX = {ON | OFF} : Chỉ định phần đệm chỉ mục. Mặc định là OFF.

FILLFACTOR = fillfactor : Chỉ định một tỷ lệ phần trăm cho biết mức độ đầy đủ của Database Engine sẽ tạo ra mức độ chi tiết của mỗi trang chỉ mục trong khi tạo hoặc thay đổi chỉ mục. fillfactor phải là một giá trị số duy nhất 1 đến 100. Giá trị mặc định là 0.

IGNORE_DUP_KEY = {ON | OFF} : Chỉ định phản hồi lỗi khi thao tác INSERT cố gắng chèn các giá trị khóa trùng lặp vào một chỉ mục unique. Tùy chọn IGNORE_DUP_KEY chỉ áp dụng cho các hoạt động chèn sau khi chỉ mục được tạo hoặc xây dựng lại. Mặc định là OFF.

STATISTICS_NORECOMPUTE = {ON | OFF} : Chỉ định liệu thống kê phân phối có được tính toán lại hay không. Mặc định là OFF.

ALLOW_ROW_LOCKS = {ON | OFF} : Chỉ định xem có cho phép khóa hàng hay không. Mặc định là ON.

ALLOW_PAGE_LOCKS = {ON | OFF} : Chỉ định xem có cho phép khóa trang hay không. Mặc định là ON.

Best Practices

Nếu một hàm do người dùng định nghĩa không được tạo với mệnh đề SCHEMABINDING, thì các thay đổi được thực hiện đối với các đối tượng cơ bản có thể ảnh hưởng đến định nghĩa của hàm và tạo ra kết quả không mong muốn khi nó được gọi. Lời khuyên là bạn nên triển khai một trong các phương pháp sau để đảm bảo rằng hàm không trở nên lỗi thời do những thay đổi đối với các đối tượng cơ bản của nó:

• Chỉ định mệnh đề WITH SCHEMABINDING khi bạn tạo hàm. Điều này đảm bảo rằng các đối tượng được tham chiếu trong định nghĩa hàm không thể được sửa đổi trừ khi hàm cũng được sửa đổi.
• Thực thi thủ tục lưu trữ sp_refreshsqlmodule sau khi sửa đổi bất kỳ đối tượng nào được chỉ định trong định nghĩa của hàm.

Quan trọng

Để biết thêm thông tin và cân nhắc về hiệu suất về các hàm giá trị bảng nội tuyến (TVF nội tuyến) và các hàm giá trị bảng nhiều câu lệnh (MSTVF), hãy xem Tạo hàm do người dùng xác định (Công cụ cơ sở dữ liệu).

Nhận xét chung

Các hàm vô hướng có thể được gọi khi sử dụng các biểu thức vô hướng. Điều này bao gồm các cột được tính toán và định nghĩa ràng buộc CHECK. Các hàm vô hướng cũng có thể được thực thi bằng cách sử dụng câu lệnh EXECUTE. Các hàm vô hướng phải được gọi bằng cách sử dụng ít nhất tên gồm hai phần của hàm (.). Hàm trả về kiểu bảng có thể được gọi nơi biểu thức bảng được phép trong mệnh đề FROM của SELECT, INSERT, UPDATE, hoặc DELETE.

Khả năng tương tác

Các câu lệnh sau đây hợp lệ trong một hàm:

• Các lệnh gán.
• Các lệnh Control-of-Flow ngoại trừ các lệnh TRY...CATCH.
• Câu lệnh DECLARE xác định biến dữ liệu cục bộ và con trỏ cục bộ.
• Câu lệnh SELECT chứa danh sách chọn với các biểu thức gán giá trị cho các biến cục bộ.
• Các hoạt động con trỏ tham chiếu đến các con trỏ cục bộ được khai báo, mở, đóng và phân bổ trong hàm. Chỉ cho phép các câu lệnh FETCH gán giá trị cho các biến cục bộ bằng mệnh đề INTO; câu lệnh FETCH trả về dữ liệu cho máy khách không được phép.
• Các lệnh INSERT, UPDATE và DELETE sửa đổi các biến bảng cục bộ.
• Các câu lệnh EXECUTE gọi các thủ tục được lưu trữ mở rộng.

Gọi các thủ tục được lưu trữ mở rộng từ các hàm

Thủ tục lưu trữ mở rộng, khi nó được gọi từ bên trong một hàm thì sẽ không thể trả về bộ kết quả cho máy khách. Bất kỳ API ODS nào trả về bộ kết quả cho máy khách sẽ trả về FAIL. Thủ tục được lưu trữ mở rộng có thể kết nối trở lại một phiên bản của SQL Server; tuy nhiên, nó không nên cố gắng tham gia cùng một giao dịch như hàm đã gọi thủ tục được lưu trữ mở rộng.

Tương tự như các lệnh gọi từ một lô hoặc thủ tục lưu trữ, thủ tục lưu trữ mở rộng sẽ được thực thi trong ngữ cảnh của tài khoản bảo mật Windows mà SQL Server đang chạy. Chủ sở hữu của thủ tục lưu trữ nên cân nhắc điều này khi cấp quyền EXECUTE nó cho người dùng.

Hạn chế

Các hàm tự tạo không thể được sử dụng để thực hiện các hành động sửa đổi trạng thái của cơ sở dữ liệu.

Các hàm tự tạo không thể chứa mệnh đề OUTPUT INTO có một bảng là mục tiêu của nó.

Các câu lệnh Service Broker sau đây không thể được đưa vào định nghĩa của một hàm tự tạo:

• BEGIN DIALOG CONVERSATION
• END CONVERSATION
• GET CONVERSATION GROUP
• MOVE CONVERSATION
• RECEIVE
• SEND

Các hàm tự tạo có thể được lồng vào nhau; nghĩa là, một hàm tự tạo có thể gọi một hàm khác. Mức lồng được tăng lên khi hàm được gọi bắt đầu thực thi và giảm khi hàm được gọi kết thúc quá trình thực thi. Các hàm tự tạo có thể được lồng vào nhau lên đến 32 cấp độ. Vượt quá mức lồng ghép tối đa sẽ khiến toàn bộ chuỗi hàm gọi không thành công (không thực thi được). Mọi tham chiếu đến mã được quản lý từ một hàm tự tạo trong Transact-SQL được tính là một cấp so với giới hạn lồng 32 cấp. Các phương thức được gọi từ bên trong mã được quản lý không được tính vào giới hạn này.

Quyền

Yêu cầu quyền CREATE FUNCTION trong cơ sở dữ liệu và quyền ALTER trên lược đồ mà hàm đang được tạo. Nếu hàm chỉ định một kiểu do người dùng xác định thì sẽ yêu cầu quyền EXECUTE đối với kiểu đó.

Các ví dụ

Ví dụ 1: Tạo hàm vô hướng trả về chuỗi

Đoạn code dưới đây tạo một hàm có tên printString để trả về một chuỗi có dấu, sau đó sử dụng câu lệnh select để gọi hàm.

create function printString() returns nvarchar(100) as begin return N'Đang trong hàm demo()'; end; go select dbo.printString() as Result;

Kết quả:

Result
1	Đang trong hàm demo()

Ví dụ 2: Tạo hàm vô hướng trả về giá trị gấp đôi tham số

Đoạn code dưới đây tạo một hàm có tên doubleValue() để trả về một số có giá trị gấp đôi so với giá trị mà tham số nhận được, sau đó sử dụng print để gọi hàm.

create function doubleValue(@number float) returns float as begin return @number*2; end; go declare @number float; set @number = 125; select N'Gấp đôi của '+str(@number,3)+' là: '+str(dbo.doubleValue(@number),3)[Kết quả];

Kết quả:

Kết quả
1	Gấp đôi của 125 là: 250

Ví dụ 3: Sử dụng hàm tự tạo vô hướng để để tính toán tuần ISO

Ví dụ sau tạo hàm có tên ISOweek. Hàm này nhận đối số ngày tháng và tính toán số tuần ISO. Để hàm này tính toán chính xác thì ta phải thực hiện lệnh SET DATEFIRST 1 trước khi gọi hàm.

Ví dụ cũng cho thấy việc sử dụng mệnh đề EXECUTE AS để chỉ định ngữ cảnh bảo mật trong đó một thủ tục được lưu trữ có thể được thực thi. Trong ví dụ, tùy chọn CALLER chỉ định rằng thủ tục sẽ được thực thi trong ngữ cảnh của người dùng gọi nó. Các tùy chọn khác mà bạn có thể xác định được là SELF, OWNER và user_name.

CREATE FUNCTION dbo.ISOweek (@DATE datetime) RETURNS int WITH EXECUTE AS CALLER AS BEGIN DECLARE @ISOweek int; SET @ISOweek = DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104'); --Trường hợp đặc biệt: Ngày 1, 2, 3 của tháng 1 có thể --thuộc về năm trước IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1; --Trường hợp đặc biệt: Ngày 29, 30, 31 tháng 12 có thể --thuộc về năm sau IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1; RETURN(@ISOweek); END; GO SET DATEFIRST 1; SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';

Kết quả:

ISO Week
1	52

Ví dụ 4: Tạo một hàm trả về bảng

Ví dụ sau sẽ tạo một hàm trả về bảng trong cơ sở dữ liệu AdventureWorks2012. Nó trả về ba cột ProductID, Name và tổng year-to-date với bí danh Total cho mỗi sản phẩm bán cho các cửa hàng.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int) RETURNS TABLE AS RETURN ( SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total' FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID WHERE C.StoreID = @storeid GROUP BY P.ProductID, P.Name ); GO SELECT * FROM Sales.ufn_SalesByStore(602);

Ví dụ 5: Tạo hàm tính ngày trong tuần

Câu lệnh dưới đây định nghĩa hàm tính ngày trong tuần (thứ trong tuần) của một giá trị kiểu ngày.

CREATE FUNCTION dbo.thu(@ngay DATETIME) RETURNS NVARCHAR(10) AS BEGIN DECLARE @st NVARCHAR(10); SELECT @st=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 (@st) /* Trị trả về của hàm */ END; GO SELECT dbo.thu(GETDATE())[Ngày trong tuần];

Một hàm khi đã được định nghĩa có thể được sử dụng như các hàm do hệ quản trị cơ sở dữ liệu cung cấp (thông thường trước tên hàm ta phải chỉ định thêm tên của người sở hữu hàm).

Kết quả:

Ngày trong tuần
1	Thứ ba

Ví dụ 6: Tạo một hàm trả về bảng được định nghĩa

Ví dụ sau tạo hàm ufn_FindReports(InEmpID) trong cơ sở dữ liệu AdventureWorks2012. Khi được cung cấp ID nhân viên hợp lệ, hàm sẽ trả về một bảng tương ứng với tất cả các nhân viên báo cáo cho nhân viên đó trực tiếp hoặc gián tiếp. Hàm sử dụng biểu thức bảng chung đệ quy (CTE) để tạo danh sách phân cấp các nhân viên.

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER) RETURNS @retFindReports TABLE ( EmployeeID int primary key NOT NULL, FirstName nvarchar(255) NOT NULL, LastName nvarchar(255) NOT NULL, JobTitle nvarchar(50) NOT NULL, RecursionLevel int NOT NULL ) -- Trả về tập kết quả là tất cả các nhân viên báo cáo cho -- nhân viên cụ thể trực tiếp hoặc gián tiếp AS BEGIN WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns AS ( -- Lấy danh sách khởi tạo các nhân viên cho giám đốc n SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 FROM HumanResources.Employee e INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID WHERE e.BusinessEntityID = @InEmpID UNION ALL -- Liên kết thành viên đệ quy để nối SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 FROM HumanResources.Employee e INNER JOIN EMP_cte ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID ) -- copy các cột bắt buộc tới kết quả hàm INSERT @retFindReports SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel FROM EMP_cte RETURN END; GO -- gọi hàm SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel FROM dbo.ufn_FindReports(1); GO