Поделиться через


CREATE FUNCTION (Transact-SQL)

Создает определяемую пользователем функцию. Функция представляет собой подпрограмму Transact-SQL или среды CLR, которая возвращает значение. Пользовательские функции бывают скалярными или возвращающими табличное значение. Функция является скалярной, если в предложении RETURNS указан один из скалярных типов данных. Скалярные функции могут состоять из нескольких инструкций Transact-SQL. Функция является возвращающей табличное значение, если в предложении RETURNS указывается ключевое слово TABLE. В зависимости от того, каким образом определено тело функции, функции, возвращающие табличное значение, подразделяются на встроенные функции и функции из нескольких инструкций. Дополнительные сведения см. в разделе Возвращающие табличное значение определяемые пользователем функции.

Значок ссылки на разделСинтаксические обозначения языка Transact-SQL

Синтаксис

Scalar Functions
                CREATE 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
                [ ; ]
                
                Inline Table-Valued Functions
                CREATE 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 [ ) ]
                [ ; ]
                
                Multistatement Table-valued Functions
                CREATE 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
                [ ; ]
                
                CLR Scalar Functions
                CREATE FUNCTION [ schema_name. ] function_name ( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type
                    
                    [ =default ] } 
                    [ ,...n ]
                )
                RETURNS { return_data_type }
                    [ WITH <clr_function_option> [ ,...n ] ]
                    [ AS ] EXTERNAL NAME <method_specifier>
                [ ; ]
                
                CLR Table-Valued Functions
                CREATE FUNCTION [ schema_name. ] function_name ( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type
                    
                    [ =default ] } 
                    [ ,...n ]
                )
                RETURNS TABLE <clr_table_type_definition> 
                    [ WITH <clr_function_option> [ ,...n ] ]
                    [ ORDER ( <order_clause> ) ]
                    [ AS ] EXTERNAL NAME <method_specifier>
                [ ; ]
                
                <order_clause> ::= 
                {
                   <column_name_in_clr_table_type_definition>
                   [ ASC | DESC ] 
                } [ ,...n] 
                
                Method Specifier<method_specifier>::=    assembly_name.class_name.method_nameFunction Options<function_option>::= 
                {
                    [ ENCRYPTION ]
                  | [ SCHEMABINDING ]
                  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
                  | [ EXECUTE_AS_Clause ]
                }
                
                <clr_function_option>::=
                }
                    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
                  | [ EXECUTE_AS_Clause ]
                }
                
                Table Type Definitions<table_type_definition>:: = ( { <column_definition> <column_constraint>
                    
                  | <computed_column_definition> } 
                    [ <table_constraint> ] [ ,...n ]
                ) <clr_table_type_definition>::= ( { column_name data_type } [ ,...n ] )<column_definition>::=
                {
                    { column_name data_type }
                    [ [ DEFAULT constant_expression ] 
                      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
                    ]
                    | [ IDENTITY [ (seed ,increment ) ] ]
                    [ <column_constraint> [ ...n ] ] 
                }
                
                <column_constraint>::= 
                {
                    [ 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>::=
                { 
                    { PRIMARY KEY | UNIQUE }
                      [ CLUSTERED | NONCLUSTERED ] 
                      (column_name [ ASC | DESC ] [ ,...n ] )
                        [ WITH FILLFACTOR = fillfactor
                    
                        | WITH ( <index_option> [ , ...n ] )
                  | [ CHECK (logical_expression ) ] [ ,...n ]
                }
                
                <index_option>::=
                { 
                    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 } 
                }

Аргументы

  • schema_name
    Имя схемы, к которой принадлежит определяемая пользователем функция.

  • function_name
    Имя пользовательской функции. Имена функций должны удовлетворять правилам построения идентификаторов и должны быть уникальными в пределах базы данных и схемы.

    ПримечаниеПримечание

    Скобки после имени функции обязательны даже при отсутствии параметров.

  • @parameter\_name
    Аргумент пользовательской функции. Может быть объявлен один или несколько аргументов.

    Для функций допускается не более 2 100 параметров. При выполнении функции значение каждого из объявленных параметров должно быть указано пользователем, если для данного параметра не определено значение по умолчанию.

    Определяет имя аргумента, используя знак @ как первый символ. Имя параметра должно соответствовать правилам построения идентификаторов. Параметры являются локальными в пределах функции; в разных функциях могут быть использованы одинаковые имена параметров. Параметры могут использоваться только вместо констант. Они не могут использоваться вместо имен таблиц, имен столбцов или имен других объектов базы данных.

    ПримечаниеПримечание

    Параметры ANSI_WARNINGS не годятся для передачи в хранимые процедуры, пользовательские функции и при объявлении и установке переменных в пакетных инструкциях. Например, если объявить переменную типа char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до размера переменной, а инструкция INSERT или UPDATE завершится без ошибок.

  • [ type_schema_name. ] parameter_data_type
    Тип данных параметра (возможно, с указанием схемы, которой он принадлежит). Для функций Transact-SQL допустимы любые типы данных, включая определяемые пользователем типы данных CLR и определяемые пользователем табличные типы, за исключением типа данных timestamp. Для функций CLR допустимы все типы данных, включая определяемые пользователем типы данных CLR, за исключением типов данных text, ntext, image, определяемых пользователем табличных типов и типов данных timestamp. Нескалярные типы cursor и table не могут быть указаны в качестве типов данных параметров ни для функций Transact-SQL, ни для функций CLR.

    Если параметр type_schema_name не определен, то Database Engine ищет параметр scalar_parameter_data_type в следующем порядке:

    • в схеме, содержащей имена системных типов данных SQL Server;

    • в установленной по умолчанию для текущего пользователя схеме в текущей базе данных;

    • в схеме dbo в текущей базе данных.

  • [ =default ]
    Значение по умолчанию для аргумента. Если определено значение default, то функция выполняется даже в том случае, если для данного аргумента значение не указано.

    ПримечаниеПримечание

    Для функций CLR также могут указываться значения параметров по умолчанию, кроме типов varchar(max) и varbinary(max).

    Если параметр функции имеет значение по умолчанию, то для него должно быть указано ключевое слово DEFAULT для получения функцией значения по умолчанию. Применение ключевого слова DEFAULT следует отличать от использования аргументов со значениями по умолчанию в хранимых процедурах, когда неуказанный аргумент неявно принимает значение по умолчанию.

  • READONLY
    Указывает, что параметр не может быть обновлен или изменен при определении функции. Если тип параметра является определяемым пользователем табличным типом, то должно быть указано ключевое слово READONLY.

  • return_data_type
    Возвращаемое значение скалярной функцией, определяемой пользователем. Для функций Transact-SQL допустимы любые типы данных, включая определяемые пользователем типы данных CLR, за исключением типа данных timestamp. Для функций CLR допустимы все типы данных, включая определяемые пользователем типы данных CLR, за исключением типов данных text, ntext, image и timestamp. Нескалярные типы данных cursor и table не могут быть указаны в качестве возвращаемых типов данных ни для функций Transact-SQL, ни для функций CLR. 

  • function_body
    Указывает ряд инструкций Transact-SQL, которая в совокупности не вызывает побочных эффектов (например, изменение содержимого таблиц) и формирует возвращаемое значение функции. function_body используется только в скалярных функциях и функциях, возвращающих табличное значение, из нескольких инструкций.

    Для скалярных функций function_body представляет собой серию инструкций Transact-SQL, которые в совокупности вычисляют скалярное выражение.

    Для функций, возвращающих табличное значение из нескольких инструкций, аргумент function_body представляет собой серию инструкций Transact-SQL, заполняющих возвращаемую переменную TABLE.

  • scalar_expression
    Указывает скалярное значение, возвращаемое скалярной функцией.

  • TABLE
    Указывает, что возвращаемым значением функции, возвращающей табличное значение, является таблица. Функциям, возвращающим табличное значение, могут передаваться только константы и @local\_variables.

    Во встроенных функциях возвращаемое значение TABLE определяется при использовании единственной инструкции SELECT. Встроенные функции не имеют соответствующих возвращаемых переменных.

    В функциях, возвращающих табличное значение из нескольких инструкций, переменной @return\_variable является переменная TABLE, используемая для сохранения данных и накопления строк, которые будут возвращены в качестве значения функции. Аргумент @return\_variable может быть указан только для функций Transact-SQL, но не для функций CLR.

  • select_stmt
    Единичная инструкция SELECT, определяющая возвращаемое значение встроенной функции, возвращающей табличное значение.

  • ORDER (<order_clause>)
    Указывает порядок, в котором возвращаются результаты из возвращающей табличное значение функции. Дополнительные сведения см. в подразделе «Руководство по использованию порядка сортировки» далее в этом разделе.

  • EXTERNAL NAME <method_specifier> assembly_name.class_name.method_name
    Указывает метод сборки, привязываемый к функции. Значение assembly_name должно соответствовать имени существующей сборки в SQL Server в текущей базе данных, для которой включена видимость. Значение class_name должно быть допустимым идентификатором SQL Server, указывающим имя класса в сборке. Если имя класса через точку (.) предваряется квалификатором пространства имен, то оно должно быть заключено в квадратные скобки ([]) или двойные кавычки (""). Значение method_name должно быть допустимым идентификатором SQL Server, указывающим имя статического метода в указанном классе.

    ПримечаниеПримечание

    По умолчанию SQL Server не производит выполнение кода CLR. Допускается создание, изменение и удаление объектов базы данных, содержащих ссылки на модули CLR, однако SQL Server их не выполняет, пока не включен параметр clr enabled. Для его включения воспользуйтесь хранимой процедурой sp_configure.

  • <table_type_definition> ( { <column_definition> <column_constraint> | <computed_column_definition> } [ <table_constraint> ] [ ,...n ] )
    Определяет тип данных таблицы для функции Transact-SQL. Объявление таблицы включает определения столбцов, а также ограничений для столбцов и таблиц. Таблица всегда помещается в первичную файловую группу.

  • < clr_table_type_definition > ( { column_namedata_type } [ ,...n ] )
    Определяет табличные типы данных для функции CLR. Объявление таблицы включает только имена столбцов и типы данных. Таблица всегда помещается в первичную файловую группу.

<function_option>::= and <clr_function_option>::=

Указывает, что функция будет иметь один или несколько из следующих параметров.

  • ENCRYPTION
    Указывает, что компонент Database Engine преобразует исходный текст инструкции CREATE PROCEDURE в скрытый формат. Выходные данные запутывания не видны непосредственно ни в одном представлении каталога. Пользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить скрытый текст. Однако этот текст будет доступен привилегированным пользователям, которые либо смогут обращаться к системным таблицам через порт DAC, либо будут иметь непосредственный доступ к файлам баз данных. Кроме того, пользователи, имеющие право на подключение отладчика к серверному процессу, могут получить исходный текст процедуры из памяти в период выполнения. Дополнительные сведения о доступе к системным метаданным см. в разделе Настройка видимости метаданных.

    Использование этого параметра препятствует публикации данной функции как части репликации SQL Server. Этот параметр для функций CLR указывать нельзя.

  • SCHEMABINDING
    Указывает, что функция привязана к объектам базы данных, которые содержат ссылки на нее. Если аргумент SCHEMABINDING указан, нельзя изменить базовые объекты таким способом, который может повлиять на определение функции. Сначала нужно изменить или удалить само определение функции, чтобы удалить зависимости от объекта, который требуется изменить.

    Привязка функции к объектам, на которые она ссылается, удаляется только в следующих случаях.

    • При удалении функции.

    • При изменении функции инструкцией ALTER, если не указан параметр SCHEMABINDING.

    Функция может быть привязана к схеме только в том случае, если выполняются следующие условия.

    • Функция является функцией Transact-SQL.

    • Пользовательские функции и представления, на которые ссылается данная функция, также привязаны к схеме.

    • Объекты, на которые ссылается функция, указываются двухкомпонентными именами.

    • Функция и объекты, на которые она ссылается, относятся к одной и той же базе данных.

    • Пользователь, выполняющий инструкцию CREATE FUNCTION, имеет разрешение REFERENCES на объекты базы данных, на которые ссылается функция.

  • RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
    Указывает атрибут OnNULLCall скалярной функции. Если данный аргумент не указан, по умолчанию предполагается CALLED ON NULL INPUT. Это означает, что текст функции выполняется даже в том случае, если в качестве аргумента передано значение NULL.

    Если атрибут RETURNS NULL ON NULL INPUT указан для функции CLR, это означает, что SQL Server может вернуть NULL, вообще не вызывая тело функции, если в качестве какого-либо из аргументов указано значение NULL. Если метод функции CLR, указанный в <method_specifier>, уже имеет пользовательский атрибут, определяющий RETURNS NULL ON NULL INPUT, но инструкция CREATE FUNCTION определяет CALLED ON NULL INPUT, то инструкция CREATE FUNCTION имеет больший приоритет. Атрибут OnNULLCall не может быть указан для функций CLR, возвращающих табличное значение.

  • Предложение EXECUTE AS
    Указывает контекст безопасности, в котором выполняется пользовательская функция. Иными словами, есть возможность управлять тем, какую учетную запись пользователя SQL Server использует при определении разрешений на объекты базы данных, на которые ссылается функция.

    ПримечаниеПримечание

    Предложение EXECUTE AS не может быть указано для встроенных пользовательских функций.

    Дополнительные сведения см. в разделе EXECUTE AS, предложение (Transact-SQL).

< column_definition >::=

Определяет тип данных таблицы. Объявление таблицы включает определения столбцов и ограничений. Для функций CLR могут быть указаны только column_name и data_type.

  • column_name
    Представляет собой имя столбца таблицы. Имена столбцов должны соответствовать требованиям к идентификаторам и должны быть уникальны для таблицы. Значение column_name может содержать от 1 до 128 символов.

  • data_type
    Указывает тип данных столбца. Для функций Transact-SQL допустимы любые типы данных, включая определяемые пользователем типы данных CLR, кроме типа данных timestamp. Для функций CLR допустимы любые типы данных, включая определяемые пользователем типы данных CLR, кроме типов данных text, ntext, image, char, varchar, varchar(max) и timestamp. Нескалярный тип данных cursor не может указываться в качестве типа данных столбца ни для функций Transact-SQL, ни для функций CLR.

  • DEFAULT constant_expression
    Указывает значение, присваиваемое столбцу, в том числе, когда при вставке его значение явно не указано. В качестве значения constant_expression может быть указана константа, NULL или значение системной функции. Определения DEFAULT могут применяться к любым столбцам, кроме тех, которые имеют свойство IDENTITY. Предложение DEFAULT не может быть указано для функций CLR, возвращающих табличное значение.

  • COLLATE collation_name
    Задает параметры сортировки для столбца. Если данный режим не указан, столбцу назначаются параметры сортировки базы данных по умолчанию. Имя параметров сортировки может быть либо именем параметров сортировки Windows, либо именем параметров сортировки SQL. Перечень и дополнительные сведения о параметрах сортировки см. в разделах Имя параметров сортировки Windows (Transact-SQL) и Имя параметров сортировки SQL Server (Transact-SQL).

    Предложение COLLATE может изменять параметры сортировки только для столбцов, имеющих типы char, varchar, nchar и nvarchar.

    Предложение COLLATE не может быть указано для функций CLR, возвращающих табличное значение.

  • ROWGUIDCOL
    Показывает, что новый столбец является столбцом глобального уникального идентификатора строки. Только один столбец uniqueidentifier в таблице может быть назначен в качестве столбца ROWGUIDCOL. Свойство ROWGUIDCOL может быть присвоено только столбцу uniqueidentifier.

    Свойство ROWGUIDCOL не обеспечивает уникальности значений, хранимых в столбце. Кроме того, данное свойство не производит автоматическое формирование значений для новых строк, вставляемых в таблицу. Для формирования уникальных значений произвольного столбца используйте функцию NEWID в инструкции INSERT. Может быть указано значение по умолчанию, однако функция NEWID не может быть указана в качестве значения по умолчанию.

  • IDENTITY
    Указывает, что новый столбец является столбцом идентификаторов. При добавлении в таблицу новой строки SQL Server формирует для столбца уникальное, последовательное значение. Столбцы идентификаторов обычно используются с ограничением PRIMARY KEY для поддержания уникальности идентификаторов строк в таблице. Свойство IDENTITY присвоено столбцам типа tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0). Возможно создание только одного столбца идентификаторов в каждой таблице. Привязанные аргументы по умолчанию и ограничения DEFAULT со столбцом идентификаторов использоваться не могут. Необходимо указывать либо оба параметра и (seed, и increment), либо ни тот, ни другой. Если ничего не указано, применяется значение по умолчанию (1,1).

    Предложение IDENTITY не может быть указано для функций CLR, возвращающих табличное значение.

    • seed
      Целочисленное значение, присваиваемое первой строке в таблице.

    • increment
      Целочисленное значение, добавляемое к значению seed при каждой успешной вставке строки в таблицу.

< column_constraint >::= and < table_constraint>::=

Определяет ограничение для указанного столбца или таблицы. Для функций CLR единственное допустимое ограничение — NULL. Именованные ограничения недопустимы.

  • NULL | NOT NULL
    Определяет, допустимы ли для столбца значения NULL. Параметр NULL не является ограничением в строгом смысле слова, но может быть указан так же, как и NOT NULL. Ограничение NOT NULL не может быть указано для функций CLR, возвращающих табличное значение.

  • PRIMARY KEY
    Ограничение, обеспечивающее целостность сущностей для указанного столбца через уникальный индекс. В возвращающих табличное значение пользовательских функциях ограничение PRIMARY KEY может быть создано только для одного столбца таблицы. Ограничение PRIMARY KEY не может быть указано для функций CLR, возвращающих табличное значение.

  • UNIQUE
    Ограничение, которое обеспечивает целостность сущностей для указанного столбца или столбцов с помощью уникального индекса. В таблице может быть несколько ограничений UNIQUE. Ограничение UNIQUE не может быть указано для функций CLR, возвращающих табличное значение.

  • CLUSTERED | NONCLUSTERED
    Указывает, что для ограничения PRIMARY KEY или UNIQUE создается кластеризованный или некластеризованный индекс. Ограничения PRIMARY KEY используют параметр CLUSTERED, а ограничения UNIQUE используют параметр NONCLUSTERED.

    Параметр CLUSTERED может быть указан только для одного ограничения. Если параметр CLUSTERED указан для ограничения UNIQUE и указано ограничение PRIMARY KEY, то PRIMARY KEY использует NONCLUSTERED.

    Параметры СLUSTERED и NONСLUSTERED не могут быть указаны для функций CLR, возвращающих табличное значение.

  • CHECK
    Ограничение, обеспечивающее целостность домена путем ограничения возможных значений, которые могут быть введены в столбец или столбцы. Ограничения CHECK не могут быть указаны для функций CLR, возвращающих табличное значение.

    • logical_expression
      Логическое выражение, возвращающее значения TRUE или FALSE.

<computed_column_definition>::=

Указывает вычисляемый столбец. Дополнительные сведения о вычисляемых столбцах см. в разделе Инструкция CREATE TABLE (Transact-SQL).

  • column_name
    Имя вычисляемого столбца.

  • computed_column_expression
    Выражение, определяющее значение вычисляемого столбца.

<index_option>::=

Указывает параметры индекса для индекса PRIMARY KEY или UNIQUE. Дополнительные сведения о параметрах индекса см. в разделе CREATE INDEX (Transact-SQL).

  • PAD_INDEX = { ON | OFF }
    Определяет заполнение индекса. Значение по умолчанию — OFF.

  • FILLFACTOR = fillfactor
    Указывает значение в процентах, показывающее, насколько полным компонент Database Engine должен сделать конечный уровень каждой страницы индекса во время создания или изменения индекса. Аргумент fillfactor должен быть целым числом от 1 до 100. Значение по умолчанию — 0.

  • IGNORE_DUP_KEY = { ON | OFF }
    Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа. Параметр IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Значение по умолчанию — OFF.

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Указывает, разрешена ли блокировка строк. Параметр по умолчанию — ON.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Указывает, разрешена ли блокировка страниц. Параметр по умолчанию — ON.

Советы и рекомендации

Если определяемая пользователем функция создана без применения предложения SCHEMABINDING, то изменения базовых объектов могут повлиять на определение функции и привести к непредвиденным результатам при вызове функции. Рекомендуется реализовать один из следующих методов, чтобы обеспечить, что функция не устареет из-за изменения ее базовых объектов.

  • Укажите при создании функции предложение WITH SCHEMABINDING. Это обеспечит невозможность изменения объектов, на которые ссылается определение функции, если при этом не изменяется сама функция.

  • Выполняйте хранимую процедуру sp_refreshsqlmodule после изменения любого объекта, указанного в определении функции.

Типы данных

Если в функции CLR указаны параметры, они должны иметь тип данных SQL Server, как было ранее определено для scalar_parameter_data_type. Дополнительные сведения о сравнении системных типов данных SQL Server с типами данных интеграции со средой CLR и типами данных среды CLR платформы .NET Framework см. в разделе Сопоставление данных о параметрах CLR.

Чтобы SQL Server смог ссылаться на нужный метод, если он переопределен в классе, метод, указанный в <method_specifier>, должен иметь следующие характеристики.

  • Принимать то же число параметров, которое указано в [ ,...n ].

  • Принимать все параметры по значению, а не по ссылке.

  • Принимать типы параметров, совместимые с теми, что указаны в функции SQL Server.

Если в качестве возвращаемого значения функции CLR указан табличный тип (RETURNS TABLE), то для метода, определенного в <method_specifier>, должен быть указан возвращаемый тип IEnumerator или IEnumerable, что подразумевает, что реализация этого интерфейса возлагается на автора функции. В отличие от функций Transact-SQL, функции CLR не могут в <table_type_definition> содержать ограничений PRIMARY KEY, UNIQUE и CHECK. Типы данных столбцов, указанных в <table_type_definition>, должны совпадать с типами данных соответствующих столбцов результирующего набора, возвращаемого на этапе выполнения методом, указанным в <method_specifier>. Проверка типов на этапе создания функции не производится.

Дополнительные сведения о программировании функций CLR см. в разделе Определяемые пользователем функции среды CLR.

Общие замечания

Скалярная функция может быть указана в любом месте вместо скалярного выражения. в том числе в вычисляемых столбцах и определениях ограничений CHECK. Кроме того, скалярная функция может быть выполнена инструкцией EXECUTE. Скалярные функции должны вызываться с помощью как минимум двухкомпонентного имени. Дополнительные сведения о многокомпонентных именах см. в разделе Синтаксические обозначения в Transact-SQL (Transact-SQL). Функция, возвращающая табличное значение, может быть вызвана в любом месте, где допускаются табличные выражения, — в предложении FROM инструкций SELECT, INSERT, UPDATE и DELETE. Дополнительные сведения см. в разделе Выполнение определяемых пользователем функций (компонент Database Engine).

Совместимость

В функциях допустимы следующие инструкции.

  • Инструкции присваивания.

  • Инструкции управления потоком, за исключением инструкций TRY...CATCH.

  • Инструкции DECLARE, объявляющие локальные переменные и локальные курсоры.

  • Инструкции SELECT, которые содержат списки выбора с выражениями, присваивающими значения локальным переменным.

  • Операции над локальными курсорами, которые объявляются, открываются, закрываются и освобождаются в теле функции. Допустимы только те инструкции FETCH, которые предложением INTO присваивают значения локальным переменным. Инструкции FETCH, возвращающие данные клиенту, недопустимы.

  • Инструкции INSERT, UPDATE и DELETE, которые изменяют локальные табличные переменные.

  • Инструкции EXECUTE, вызывающие расширенные хранимые процедуры.

  • Дополнительные сведения см. в разделе Создание определяемых пользователем функций (компонент Database Engine).

Взаимодействие с вычисляемыми столбцами

В SQL Server 2005 и более поздних версиях функции имеют перечисленные ниже свойства. Значения этих свойств определяют, может ли данная функция быть указана в вычисляемых столбцах, которые могут быть материализованными или индексированными.

Свойство

Описание

Примечания

IsDeterministic

Функция детерминированная или недетерминированная.

Для детерминированных функций разрешается доступ к локальным данным. Например, функция, которая при вызове с одними и теми же параметрами и в одном том же состоянии базы данных всегда возвращает один и тот же результат, называется детерминированной.

IsPrecise

Функция точная или неточная.

Неточные функции содержат такие операции, как операции с плавающей запятой.

IsSystemVerified

SQL Server может проверять свойства точности и детерминированности функций.

 

SystemDataAccess

Функции, производящие доступ к системным данным (системным каталогам или виртуальным системным таблицам) в локальном экземпляре SQL Server.

 

UserDataAccess

Функция производит доступ к данным пользователя в локальном экземпляре SQL Server.

Сюда входят определяемые пользователем и временные таблицы, но не табличные переменные.

Для функций Transact-SQL свойства точности и детерминизма SQL Server определяет автоматически. Дополнительные сведения см. в разделе Рекомендации по написанию определяемых пользователем функций. Свойства доступа к данным и детерминированности функций CLR могут быть указаны пользователем. Дополнительные сведения см. в разделе Общие сведения о пользовательских атрибутах интеграции со средой CLR.

Для отображения текущих значений этих свойств используйте функцию OBJECTPROPERTYEX.

Вычисляемый столбец, который обращается к определяемой пользователем функции, может быть включен в индекс, если функция имеет следующие значения свойств:

  • IsDeterministic = true

  • IsSystemVerified = true (если вычисляемый столбец не материализован)

  • UserDataAccess = false

  • SystemDataAccess = false

Дополнительные сведения см. в разделе Создание индексов вычисляемых столбцов.

Вызов расширенной хранимой процедуры из функций

Расширенные хранимые процедуры, если они вызываются из тела функции, не могут возвращать клиенту результирующие наборы. Все интерфейсы прикладных программ ODS, которые возвращают результирующие наборы клиенту, вернут FAIL. Расширенная хранимая процедура может подключаться к экземпляру SQL Server, но она не должна пытаться присоединиться к той же транзакции, что и функция, из которой вызвана расширенная хранимая процедура.

Как и при вызове из пакета или хранимой процедуры, расширенная хранимая процедура будет выполняться в контексте учетной записи системы безопасности Windows, от имени которой выполняется SQL Server. Владелец хранимой процедуры должен это понимать, когда он предоставляет пользователям разрешение EXECUTE на нее.

Ограничения

Пользовательская функция не может выполнять действия, изменяющие состояние базы данных.

Определяемые пользователем функции не могут содержать предложение OUTPUT INTO, целью которого является таблица.

Следующие инструкции компонента Service Broker не могут быть включены в определение определяемой пользователем функции Transact-SQL:

  • BEGIN DIALOG CONVERSATION

  • END CONVERSATION

  • GET CONVERSATION GROUP

  • MOVE CONVERSATION

  • RECEIVE

  • SEND

Определяемые пользователем функции могут быть вложенными, то есть из одной функции может быть вызвана другая. Уровень вложенности увеличивается на единицу каждый раз, когда начинается выполнение вызванной функции и уменьшается на единицу, когда ее выполнение завершается. Вложенность пользовательских функций не может превышать 32 уровня. Превышение максимального уровня вложенности приводит к ошибке выполнения для всей цепочки вызываемых функций. Каждый вызов управляемого кода из определяемой пользователем функции Transact-SQL считается за один уровень вложенности из 32 возможных. Методы, вызываемые из управляемого кода, под это ограничение не подпадают.

Использование порядка сортировки в возвращающих табличное значение функциях CLR

При использовании предложения ORDER в возвращающих табличное значение функциях CLR придерживайтесь следующих рекомендаций.

  • Необходимо гарантировать, чтобы результаты всегда были упорядочены в указанном порядке. Если результаты находятся не в указанном порядке, при выполнении запроса SQL Server сформирует сообщение об ошибке.

  • Если указано предложение ORDER, выходные данные возвращающей табличное значение функции должны быть отсортированы в соответствии с параметрами сортировки столбца (явными или неявными). Например, если для столбца используются параметры сортировки для китайского языка (указанные в определении возвращающей табличное значение функции или полученные из параметров сортировки базы данных), то возвращаемые результаты должны быть отсортированы в соответствии с правилами сортировки, принятыми в китайском языке.

  • При использовании предложение ORDER всегда проверяется SQL Server при возврате результатов, независимо от его использования обработчиком запросов для выполнения оптимизации. Рекомендуется использовать предложение ORDER только при уверенности в его пользе для обработчика запросов.

  • Обработчик запросов SQL Server автоматически использует преимущества предложения ORDER в следующих случаях.

    • Запросы Insert, в которых предложение ORDER совместимо с индексом.

    • Предложения ORDER BY, совместимые с предложением ORDER.

    • Статистические выражения, в которых предложения GROUP BY и ORDER совместимы.

    • Статистические выражения с ключевым словом DISTINCT, в которых уникальные столбцы совместимы с предложением ORDER.

Предложение ORDER не гарантирует упорядочивания результатов при выполнении запроса SELECT, если оно не указано в самом запросе. Сведения о запросе столбцов, включенных в порядок сортировки для возвращающих табличное значение функций, см. в разделе sys.function_order_columns (Transact-SQL).

Метаданные

В следующей таблице приводятся системные представления каталога, возвращающие метаданные об определяемых пользователем функциях.

Системное представление

Описание

sys.sql_modules

Отображает определение определяемых пользователем функций Transact-SQL. Например:

Определения функций, созданных с помощью параметра ENCRYPTION, не отображаются представлением sys.sql_modules, однако все остальные сведения о них доступны.

sys.assembly_modules

Выводит сведения об определяемых пользователем функциях CLR.

sys.parameters

Выводит сведения о параметрах, определенных в определяемых пользователем функциях.

sys.sql_expression_dependencies

Отображает базовые объекты, на которые ссылается функция.

Разрешения

Требуется разрешение CREATE FUNCTION на базу данных и разрешение ALTER на схему, в которой создается функция. Если в функции указан пользовательский тип, требуется разрешение EXECUTE на этот тип.

Примеры

А. Применение скалярной пользовательской функции, вычисляющей неделю по ISO

В следующем примере показано создание пользовательской функции ISOweek, которая получает в качестве аргумента дату и вычисляет номер недели по ISO. Для правильной работы этой функции перед ее вызовом должна быть выполнена инструкция SET DATEFIRST 1.

Следующий пример также показывает использование предложения EXECUTE AS для указания контекста безопасности, в котором может быть выполнена хранимая процедура. В этом примере параметр CALLER указывает, что процедура будет выполнена в контексте пользователя, который ее вызывает. Также могут быть указаны параметры SELF, OWNER и user_name.

Показан вызов функции. Обратите внимание, что DATEFIRST устанавливается в значение 1.

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
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');
--Special cases: Jan 1-3 may belong to the previous year
     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;
--Special case: Dec 29-31 may belong to the next year
     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';

Ниже приводится результирующий набор.

ISO Week

----------------

52

Б. Создание встроенной функции, возвращающей табличное значение

В следующем примере продемонстрировано создание встроенной функции, возвращающей табличное значение. Для каждого из товаров, которые продаются в магазине, она возвращает три столбца ProductID, Name и статистическое вычисление с нарастающим итогом с начала года по магазину — YTD Total .

USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD 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
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

При вызове этой функции выполняется следующий запрос.

SELECT * FROM Sales.ufn_SalesByStore (602);

В. Создание функции, возвращающей табличное значение, из нескольких инструкций

В следующем примере производится создание функции, возвращающей табличное значение, fn_FindReports(InEmpID). Если ей передать допустимый идентификатор сотрудника, она вернет таблицу, в которой содержатся все сотрудники, которые прямо или опосредованно перед ним отчитываются. В функции для построения иерархического списка сотрудников используется рекурсивное обобщенное табличное выражение (CTE). Дополнительные сведения о рекурсивных обобщенных табличных выражениях см. в разделе WITH обобщенное_табличное_выражение (Transact-SQL).

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO

Г. Создание функции CLR

В следующем примере предполагается, что Образцы компонента SQL Server Database Engine установлены в каталог по умолчанию на локальном компьютере и произведена компиляция образца приложения StringManipulate.csproj. Дополнительные сведения см. в разделе Supplementary-Aware String Manipulation.

В следующем примере создается функция CLR len_s. Перед ее созданием сборка SurrogateStringFunction.dll регистрируется в локальной базе данных.

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

Пример создания функции CLR, возвращающей табличное значение, см. в разделе Возвращающие табличное значение функции среды CLR.

Журнал изменений

Обновления

В рекомендации добавлены сведения о методах, которые можно использовать, чтобы обеспечить, что функция не устареет из-за изменения ее базовых объектов.