CREATE FUNCTION (Transact-SQL)

Синтаксис

--Transact-SQL Scalar Function Syntax
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
[ ; ]

--Transact-SQL Inline Table-Valued Function Syntax 
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 [ ) ]
[ ; ]

--Transact-SQL Multistatement Table-valued Function Syntax
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
[ ; ]

--Transact-SQL Function Clauses 
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
    [ <table_constraint> ] [ ,...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 } 
}

--CLR Scalar Function Syntax
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 Function Syntax
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>
[ ; ]

--CLR Function Clauses
<order_clause> ::= 
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ] 
} [ ,...n] 

<method_specifier>::=
    assembly_name.class_name.method_name

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...n ] )

Аргументы

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

  Внимание!

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

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

Рекомендации

Если определяемая пользователем функция создан без применения предложения 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).

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

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

Ограничения

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

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

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

• BEGIN DIALOG CONVERSATION

• END CONVERSATION

• GET CONVERSATION GROUP

• MOVE CONVERSATION

• RECEIVE

• SEND

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

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

Метаданные

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

USE AdventureWorks2008R2;
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

Разрешения

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

Примеры

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

USE AdventureWorks2008R2;
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';

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

USE AdventureWorks2008R2;
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 '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);

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

USE AdventureWorks2008R2;
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,
    FirstName nvarchar(255) NOT NULL,
    LastName nvarchar(255) NOT NULL,
    JobTitle nvarchar(50) NOT NULL,
    RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e 
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        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 the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
   FROM EMP_cte 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1); 

GO

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

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

См. также

Справочник

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

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