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

適用于:SQL Server (所有支援的版本) Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics

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

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

UserDefinedFunction 物件會以 AssemblyNameClassNameMethodName 屬性參考 .NET 組件。

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

範例

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

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

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