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

Статья
06/18/2013

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

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

Объект UserDefinedFunction указывает на сборку .NET со свойствами AssemblyName, ClassName и MethodName.

Когда объект UserDefinedFunction указывает на сборку .NET, необходимо ее зарегистрировать путем создания объекта SqlAssembly и его добавления в объект SqlAssemblyCollection, принадлежащий объекту Database.

Пример

Чтобы использовать какой-либо из представленных примеров кода, необходимо выбрать среду, шаблон и язык программирования, с помощью которых будет создаваться приложение. Дополнительные сведения см. в разделе Создание проекта SMO на языке Visual Basic в среде Visual Studio .NET или Создание проекта SMO на языке Visual C# в среде Visual Studio .NET.

Создание определяемой пользователем скалярной функции на языке Visual Basic

Данный пример кода показывает, как на языке Visual Basic создавать и удалять определяемую пользователем скалярную функцию, имеющую входной параметр объекта DateTime и возвращаемый тип integer. Определяемая пользователем функция создается в базе данных AdventureWorks2012. Пример создает определяемую пользователем функцию ISOweek, которая получает в качестве аргумента дату и вычисляет номер недели по ISO. Для правильной работы этой функции перед ее вызовом параметр базы данных DATEFIRST должен быть установлен в значение 1.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 2008R2 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'Define a UserDefinedFunction object variable by supplying the parent database and the name arguments in the constructor.
Dim udf As UserDefinedFunction
udf = New UserDefinedFunction(db, "IsOWeek")
'Set the TextMode property to false and then set the other properties.
udf.TextMode = False
udf.DataType = DataType.Int
udf.ExecutionContext = ExecutionContext.Caller
udf.FunctionType = UserDefinedFunctionType.Scalar
udf.ImplementationType = ImplementationType.TransactSql
'Add a parameter.
Dim par As UserDefinedFunctionParameter
par = New UserDefinedFunctionParameter(udf, "@DATE", DataType.DateTime)
udf.Parameters.Add(par)
'Set the TextBody property to define the user defined function.
udf.TextBody = "BEGIN  DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') 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 IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END;"
'Create the user defined function on the instance of SQL Server.
udf.Create()
'Remove the user defined function.
udf.Drop()

Создание определяемой пользователем скалярной функции на языке Visual C#

Данный пример кода показывает, как на языке Visual C# создавать и удалять определяемую пользователем скалярную функцию, имеющую входной параметр объекта DateTime и возвращаемый тип integer. Функция, определяемая пользователем, создается в базе данных AdventureWorks2012. В этом примере создается определяемая пользователем функция. ISOweek. С помощью этой функции по аргументу даты вычисляется номер недели в стандарте ISO. Для правильной работы этой функции перед ее вызовом параметр базы данных DATEFIRST должен быть установлен в значение 1.

{
            //Connect to the local, default instance of SQL Server. 
           Server srv = new Server();
            //Reference the AdventureWorks2012 database. 
           Database db = srv.Databases["AdventureWorks2012"];

            //Define a UserDefinedFunction object variable by supplying the parent database and the name arguments in the constructor. 
            UserDefinedFunction udf = new UserDefinedFunction(db, "IsOWeek");

            //Set the TextMode property to false and then set the other properties. 
            udf.TextMode = false;
            udf.DataType = DataType.Int;
            udf.ExecutionContext = ExecutionContext.Caller;
            udf.FunctionType = UserDefinedFunctionType.Scalar;
            udf.ImplementationType = ImplementationType.TransactSql;

            //Add a parameter. 
      
     UserDefinedFunctionParameter par = new UserDefinedFunctionParameter(udf, "@DATE", DataType.DateTime);
            udf.Parameters.Add(par);

            //Set the TextBody property to define the user-defined function. 
            udf.TextBody = "BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') 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 IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END;";

            //Create the user-defined function on the instance of SQL Server. 
            udf.Create();

            //Remove the user-defined function. 
            udf.Drop();
        }

Создание определяемой пользователем скалярной функции в PowerShell

Данный пример кода показывает, как на языке Visual C# создать и удалить определяемую пользователем скалярную функцию, имеющую входной параметр объекта DateTime и возвращаемый тип integer. Функция, определяемая пользователем, создается в базе данных AdventureWorks2012. В этом примере создается определяемая пользователем функция. ISOweek. С помощью этой функции по аргументу даты вычисляется номер недели в стандарте ISO. Для правильной работы этой функции перед ее вызовом параметр базы данных DATEFIRST должен быть установлен в значение 1.

# Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2012
CD \sql\localhost\default\databases
$db = get-item Adventureworks2012

# Define a user defined function object variable by supplying the parent database and name arguments in the constructor. 
$udf  = New-Object -TypeName Microsoft.SqlServer.Management.SMO.UserDefinedFunction `
-argumentlist $db, "IsOWeek"

# Set the TextMode property to false and then set the other properties. 
$udf.TextMode = $false
$udf.DataType = [Microsoft.SqlServer.Management.SMO.DataType]::Int 
$udf.ExecutionContext = [Microsoft.SqlServer.Management.SMO.ExecutionContext]::Caller
$udf.FunctionType = [Microsoft.SqlServer.Management.SMO.UserDefinedFunctionType]::Scalar
$udf.ImplementationType = [Microsoft.SqlServer.Management.SMO.ImplementationType]::TransactSql

# Define a Parameter object variable by supplying the parent function, name and type arguments in the constructor.
$type = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime
$par  = New-Object -TypeName Microsoft.SqlServer.Management.SMO.UserDefinedFunctionParameter `
-argumentlist $udf, "@DATE",$type

# Add the parameter to the function
$udf.Parameters.Add($par)

#Set the TextBody property to define the user-defined function. 
$udf.TextBody = "BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') 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 IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END;"

# Create the user-defined function on the instance of SQL Server. 
$udf.Create()

# Remove the user-defined function. 
$udf.Drop()

См. также

Справочник

UserDefinedFunction

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