建立、改變和移除使用者定義函數

發行項
06/18/2013

UserDefinedFunction 物件所提供的功能可以讓使用者藉由程式設計的方式，管理 Microsoft SQL Server 中的使用者定義函數。使用者定義函數支援輸入和輸出參數，也支援資料表資料行的直接參考。

SQL Server 需要先在資料庫內註冊組件，然後才能在預存程序、使用者定義函數、觸發程序和使用者定義資料類型中使用組件。 SMO 以 SqlAssembly 物件支援此功能。

UserDefinedFunction 物件會以 AssemblyName、ClassName 和 MethodName 屬性參考 .NET 組件。

當 UserDefinedFunction 物件參考 .NET 組件時，您必須藉由建立 SqlAssembly 物件並將其加入至 SqlAssemblyCollection 物件 (後者屬於 Database 物件) 來註冊該組件。

範例

如果要使用所提供的任何程式碼範例，您必須選擇建立應用程式用的程式設計環境、程式設計範本，及程式設計語言。如需詳細資訊，請參閱＜在 Visual Studio .NET 中建立 Visual Basic SMO 專案＞或＜在 Visual Studio .NET 中建立 Visual C# SMO 專案＞。

在 Visual Basic 中建立純量使用者定義函數

這個程式碼範例示範如何在 Visual Basic 中，建立和移除具有輸入 DateTime 物件參數和整數傳回類型的純量使用者定義函數。使用者定義函數會建立在 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 物件參數和整數傳回類型的純量使用者定義函數。使用者定義函數會建立在 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 中建立純量使用者定義函數

# 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

共用方式為

建立、改變和移除使用者定義函數

範例

在 Visual Basic 中建立純量使用者定義函數

在 Visual C# 中建立純量使用者定義函數

在 PowerShell 中建立純量使用者定義函數

請參閱

參考

其他資源