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

CREATE FUNCTION (Transact-SQL)

  • Статья

Изменения: 14 апреля 2006 г.

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

Пользовательские функции могут быть изменены инструкцией ALTER FUNCTION и удалены инструкцией DROP FUNCTION.

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

Синтаксис

Scalar Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...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 ] } 
    [ ,...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 ] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE < table_type_definition >
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN
    END
[ ; ]

CLR Functions
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
        [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Method Specifier
<method_specifier>::=
    assembly_name.class_name.method_name

Function 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
    Имя пользовательской функции. Имена функций должны удовлетворять правилам построения идентификаторов, и должны быть уникальны в пределах базы данных и схемы.

    ms186755.note(ru-ru,SQL.90).gifПримечание.
    Скобки после имени функции обязательны даже при отсутствии параметров.

  • **@**parameter_name
    Параметр пользовательской функции. Может быть объявлен один или несколько параметров.

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

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

    ms186755.note(ru-ru,SQL.90).gifПримечание.
    Параметры 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 не указан, SQL Server 2005 Database Engine производит поиск scalar_parameter_data_type в следующем порядке:

    • в схеме, содержащей имена системных типов данных SQL Server;
    • в схеме по умолчанию текущего пользователя в текущей базе данных;
    • в схеме dbo текущей базы данных.

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

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

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

  • 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, определяющая возвращаемое значение встроенной функции, возвращающей табличное значение.

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

    ms186755.note(ru-ru,SQL.90).gifПримечание.
    По умолчанию 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>::= и <clr_function_option>::=

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

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

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

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

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

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

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

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

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

  • 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 использует при определении разрешений на объекты базы данных, на которые ссылается функция.

    ms186755.note(ru-ru,SQL.90).gifПримечание.
    Предложение 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 (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 >::= и < 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 используются кластеризованные, а в ограничениях UNIQUE — некластеризованные индексы.

    Предложение 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 }
    Определяет реакцию на ошибку вследствие дублирования значений ключа при транзакции вставки нескольких строк для уникального кластеризованного или уникального некластеризованного индекса. Значение по умолчанию — OFF.
  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Указывает, пересчитывается ли статистика распределения. Значение по умолчанию — OFF.
  • ALLOW_ROW_LOCKS = { ON | OFF }
    Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.
  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.

Замечания

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

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

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

  • инструкции присваивания;
  • инструкции управления потоком за исключением инструкций TRY...CATCH;
  • инструкции DECLARE, объявляющие локальные переменные и локальные курсоры;
  • инструкции SELECT, которые содержат списки выбора с выражениями, присваивающими значения локальным переменным.
  • Операции над локальными курсорами, которые объявляются, открываются, закрываются и освобождаются в теле функции. Допустимы только те инструкции FETCH, которые предложением INTO присваивают значения локальным переменным. Инструкции FETCH, возвращающие данные клиенту, недопустимы.
  • Инструкции INSERT, UPDATE и DELETE, которые изменяют данные в локальных переменных ().
  • Инструкции EXECUTE, вызывающие расширенные хранимые процедуры.
  • Дополнительные сведения см. в разделе Создание пользовательских функций (Database Engine).

Вложенность пользовательских функций

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

ms186755.note(ru-ru,SQL.90).gifПримечание.
   Каждый вызов управляемого кода из пользовательской функции Transact-SQL считается за один уровень вложенности из 32 возможных. Методы, вызываемые из управляемого кода, под это ограничение не подпадают.

Свойства функции

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

Свойство Описание Примечания

IsDeterministic

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

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

IsPrecise

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

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

IsSystemVerified

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

 

SystemDataAccess

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

 

UserDataAccess

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

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

Для функций Transact-SQL свойства точности и детерминизма SQL Server определяет автоматически. Дополнительные сведения см. в разделе Правила написания пользовательских функций. Свойства доступа к данным и детерминированности функций CLR могут быть указаны пользователем. Дополнительные сведения см. в разделе Overview of CLR Integration Custom Attributes.

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

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

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

  • IsDeterministic = True
  • IsSystemVerified = True (если вычисляемый столбец не материализован)
  • UserDataAccess = False
  • SystemDataAccess = False

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

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

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

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

Вызов функций

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

Указание параметров и возвращаемых значений в функциях CLR

Если в функции CLR указаны параметры, они должны иметь тип данных SQL Server, как было ранее определено для scalar_parameter_data_type. Дополнительные сведения о системных типах данных SQL Server в сравнении с типами данных среды CLR и типами данных .NET Framework Common Language Runtime см. в разделе SQL Server Data Types and Their .NET Framework Equivalents.

Чтобы 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 User-Defined Functions.

Запрещенные инструкции SQL

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

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

Просмотр сведений о функциях

Для отображения определения пользовательской функции Transact-SQL воспользуйтесь представлением каталога sys.sql_modules базы данных, в которой находится функция.

Например:

USE AdventureWorks;
GO
SELECT definition, type 
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id 
    AND type IN ('FN', 'IF', 'TF');
GO
ms186755.note(ru-ru,SQL.90).gifПримечание.
Определения зашифрованных функций, созданных с помощью параметра ENCRYPTION, в sys.sql_modules не отображаются, но все остальные сведения о них доступны.

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

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

Отчет об объектах, на которые имеются ссылки из функции, выдается через sys.sql_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. Дополнительные сведения см. в разделе Операции над строками с учетом дополнений.

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

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of the this variable if you have
--installed the sample someplace other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL.1\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 Table-Valued Functions.

См. также

Справочник

ALTER FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_references (Transact-SQL)
EXECUTE (Transact-SQL)
EVENTDATA (Transact-SQL)

Другие ресурсы

Пользовательские функции (компонент Database Engine)
CLR User-Defined Functions

Справка и поддержка

Получение помощи по SQL Server 2005

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

Версия Журнал

14 апреля 2006 г.
Новое содержимое:
  • В подразделе «Вызов функций» раздела «Примечания» добавлено, что скалярные функции должны вызываться с помощью как минимум двусоставного имени.

5 декабря 2005 г.
Измененное содержимое:
  • Уточнено определение параметра ENCRYPTION.