在 SQL Server 中註冊用戶定義型別

文章
07/04/2024

適用於：SQL Server

若要在 Microsoft SQL Server 中使用使用者定義的類型（UDT），您必須註冊它。註冊 UDT 牽涉到註冊元件，並在您想要使用該元件的資料庫中建立類型。 UDT 的範圍是單一資料庫，而且除非每個資料庫都註冊相同的元件和 UDT，否則無法在多個資料庫中使用。註冊 UDT 元件並建立類型之後，您可以在 Transact-SQL 和用戶端程式代碼中使用 UDT。如需詳細資訊，請參閱 CLR 使用者定義型別。

使用 Visual Studio 部署 UDT

部署 UDT 最簡單的方式是使用 Microsoft Visual Studio。不過，如需更複雜的部署案例和最大的彈性，請使用 Transact-SQL，如本主題稍後所述。

請遵循下列步驟，使用 Visual Studio 建立及部署 UDT：

在 Visual Basic 或 Visual C# 語言節點中建立新的 Database 專案。
新增將包含 UDT 之 SQL Server 資料庫的參考。
新增使用者定義型別類別。
撰寫程式代碼以實作 UDT。
從 [建置 ] 功能表中，選取 [ 部署]。這會註冊元件，並在 SQL Server 資料庫中建立類型。

使用 Transact-SQL 部署 UDT

Transact-SQL CREATE ASSEMBLY 語法是用來在您想要使用 UDT 的資料庫中註冊元件。它會在內部儲存在資料庫系統數據表中，而不是在檔案系統外部。如果 UDT 相依於外部元件，它們也必須載入資料庫。 CREATE TYPE 語句是用來在要使用的資料庫中建立 UDT。如需詳細資訊，請參閱 CREATE ASSEMBLY （Transact-SQL）和 CREATE TYPE （Transact-SQL）。

使用 CREATE ASSEMBLY

CREATE ASSEMBLY 語法會在您想要使用 UDT 的資料庫中註冊元件。註冊元件之後，它就沒有相依性。

不允許在指定的資料庫中建立多個相同元件的版本。不過，根據指定資料庫中的文化特性，可以建立相同元件的多個版本。 SQL Server 會以不同名稱區分元件的多個文化特性版本，如在 SQL Server 實例中註冊。如需詳細資訊，請參閱 .NET Framework SDK 中的。

當 CREATE ASSEMBLY 以 SAFE 或 EXTERNAL_ACCESS 許可權集合執行時，會檢查元件以確定它是可驗證且類型安全的。如果您省略指定許可權集合，則會假設 SAFE。未檢查具有 UNSAFE 許可權集合的程式代碼。如需有關組件權限集合的詳細資訊，請參閱設計組件。

範例

下列 Transact-SQL 語句會在 AdventureWorks 資料庫中，向 SAFE 許可權集合註冊 SQL Server 中的 Point 元件。如果省略 WITH PERMISSION_SET 子句，則會向 SAFE 許可權集合註冊元件。

USE AdventureWorks;  
CREATE ASSEMBLY Point  
FROM '\\ShareName\Projects\Point\bin\Point.dll'   
WITH PERMISSION_SET = SAFE;

下列 Transact-SQL 語句會在 FROM 子句中使用 <assembly_bits> 自變數來註冊元件。此 varbinary 值會將檔案表示為位元組數據流。

USE AdventureWorks;  
CREATE ASSEMBLY Point  
FROM 0xfeac4 ... 21ac78

使用 CREATE TYPE

將元件載入資料庫之後，您就可以使用 Transact-SQL CREATE TYPE 語句來建立類型。這會將類型加入至該資料庫的可用類型清單。此類型具有資料庫範圍，而且類型只能在建立所在的資料庫中使用。如果資料庫中已有 UDT，CREATE TYPE 語句就會失敗，並出現錯誤。

注意

CREATE TYPE 語法也用於建立原生 SQL Server 別名數據類型，並用來取代 sp_addtype 作為建立別名數據類型的方法。 CREATE TYPE 語法中的一些選擇性自變數是指建立UDT，不適用於建立別名數據類型（例如基底類型）。

如需詳細資訊，請參閱 CREATE TYPE (TRANSACT-SQL)。

範例

下列 Transact-SQL 語句會 建立 Point 類型。 EXTERNAL NAME 是使用 AssemblyName 的兩部分命名語法來指定。UDTName。

CREATE TYPE dbo.Point   
EXTERNAL NAME Point.[Point];

從資料庫移除 UDT

DROP TYPE 語句會從目前的資料庫移除 UDT。卸除 UDT 之後，您可以使用 DROP ASSEMBLY 語句從資料庫卸載元件。

DROP TYPE 語句不會在下列情況下執行：

資料庫中包含使用 UDT 所定義之資料行的數據表。
使用WITH SCHEMABINDING子句在資料庫中建立之UDT變數或參數的函數、預存程式或觸發程式。

範例

下列 Transact-SQL 必須依下列順序執行。首先，必須卸除參考 Point UDT 的數據表，然後卸除類型，最後再卸除元件。

DROP TABLE dbo.Points;  
DROP TYPE dbo.Point;  
DROP ASSEMBLY Point;

尋找UDT相依性

如果有相依物件，例如具有 UDT 資料行定義的數據表，DROP TYPE 語句就會失敗。如果有使用WITH SCHEMABINDING子句在資料庫中建立的函式、預存程式或觸發程式，如果這些例程使用使用者定義類型的變數或參數，它也會失敗。您必須先卸除所有相依物件，然後執行DROP TYPE語句。

下列 Transact-SQL 查詢會找出 AdventureWorks 資料庫中使用 UDT 的所有數據行和參數。

USE Adventureworks;  
SELECT o.name AS major_name, o.type_desc AS major_type_desc  
     , c.name AS minor_name, c.type_desc AS minor_type_desc  
     , at.assembly_class  
  FROM (  
        SELECT object_id, name, user_type_id, 'SQL_COLUMN' AS type_desc  
          FROM sys.columns  
     UNION ALL  
        SELECT object_id, name, user_type_id, 'SQL_PROCEDURE_PARAMETER'  
          FROM sys.parameters  
     ) AS c  
  JOIN sys.objects AS o  
    ON o.object_id = c.object_id  
  JOIN sys.assembly_types AS at  
    ON at.user_type_id = c.user_type_id;

維護UDT

您無法在 SQL Server 資料庫中建立 UDT 之後修改 UDT，不過您可以改變類型所依據的元件。在大部分情況下，您必須使用 Transact-SQL DROP TYPE 語句從資料庫中移除 UDT、對基礎元件進行變更，並使用 ALTER ASSEMBLY 語句重載它。然後，您必須重新建立 UDT 和任何相依物件。

範例

在您對 UDT 元件中的原始程式碼進行變更並重新編譯之後，就會使用 ALTER ASSEMBLY 語句。它會將.dll檔案複製到伺服器，並將重新系結至新的元件。如需完整的語法，請參閱 ALTER ASSEMBLY （Transact-SQL）。

下列 Transact-SQL ALTER ASSEMBLY 語句會從磁碟上的指定位置重載Point.dll元件。

ALTER ASSEMBLY Point  
FROM '\\Projects\Point\bin\Point.dll'

使用 ALTER ASSEMBLY 新增原始程式碼

ALTER ASSEMBLY 語法中的 ADD FILE 子句不存在於 CREATE ASSEMBLY 中。您可以使用它來新增原始程式碼或任何其他與元件相關聯的檔案。檔案會從其原始位置複製，並儲存在資料庫中的系統數據表中。這可確保您必須重新建立或記載目前 UDT 版本的原始程式碼或其他檔案。

下列 Transact-SQL ALTER ASSEMBLY 語句會新增 Point UDT 的Point.cs類別原始程式碼。這會複製Point.cs檔案中包含的文字，並將它儲存在資料庫的名稱「PointSource」。。

ALTER ASSEMBLY Point  
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;

元件資訊會儲存在 已安裝元件的資料庫中sys.assembly_files 數據表中。 sys.assembly_files數據表包含下列數據行。

assembly_id
為元件定義的識別碼。這個數位會指派給與相同元件相關的所有物件。

name
物件的名稱。

file_id
識別每個物件的數位，其中第一個物件與指定的 assembly_id 指定值 1 相關聯。如果有多個物件與相同的 assembly_id相關聯，則每個後續 file_id 值都會遞增 1。

內容
元件或檔案的十六進位表示法。

您可以使用 CAST 或 CONVERT 函式，將內容數據行的內容轉換成可讀取的文字。下列查詢會使用 WHERE 子句中的名稱，將Point.cs檔案的內容轉換為可讀取的文字，以將結果集限制為單一數據列。

SELECT CAST(content AS varchar(8000))   
  FROM sys.assembly_files   
  WHERE name='PointSource';

如果您將結果複製並貼到文字編輯器中，您會看到原始中存在的換行符和空格已保留。

管理UDT和元件

規劃 UDT 實作時，請考慮 UDT 元件本身需要哪些方法，以及應該在不同的元件中建立哪些方法，並實作為使用者定義函式或預存程式。將方法分成不同的元件可讓您更新程序代碼，而不會影響數據表中 UDT 資料行中可能儲存的數據。只有當新的定義可以讀取先前的值，而且型別的簽章不會變更時，您才能修改 UDT 元件，而不卸除 UDT 數據行和其他相依物件。

將程式代碼與實作 UDT 所需的程式代碼分開，可大幅簡化維護。只包含 UDT 運作所需的程式代碼，並盡可能讓 UDT 定義保持簡單，可降低 UDT 本身可能需要從資料庫卸載程式代碼修訂或 Bug 修正的風險。

貨幣 UDT 和貨幣轉換函式

AdventureWorks 範例資料庫中的 Currency UDT 提供建構 UDT 及其相關聯函式的建議方式的實用範例。貨幣 UDT 用於根據特定文化的貨幣系統處理貨幣，並允許儲存不同的貨幣類型，例如美元、歐元等。 UDT 類別會將文化特性名稱公開為字串，並將金額公開為 十進位 數據類型。定義類別的元件中包含所有必要的串行化方法。實作貨幣轉換從某個文化特性到另一個文化特性的函式會實作為名為 ConvertCurrency 的外部函式，而此函式位於不同的元件中。 ConvertCurrency 函式會從 AdventureWorks 資料庫中的數據表擷取轉換率，以執行其工作。如果轉換率的來源應該變更，或如果現有程式代碼應該有任何其他變更，則元件可以輕鬆地修改，而不會影響貨幣 UDT。

您可以藉由安裝 Common Language Runtime （CLR）範例，找到 Currency UDT 和 ConvertCurrency 函式的程式代碼清單。

跨資料庫使用UDT

UDT 的定義範圍是單一資料庫。因此，在某個資料庫中定義的 UDT 不能用於另一個資料庫中的數據行定義。若要在多個資料庫中使用 UDT，您必須在相同的元件上在每個資料庫中執行 CREATE ASSEMBLY 和 CREATE TYPE 語句。如果元件的名稱、強名稱、文化特性、版本、許可權集和二進位內容相同，則會將其視為相同。

在兩個資料庫中註冊並存取 UDT 之後，您可以從某個資料庫轉換 UDT 值，以供另一個資料庫使用。在下列案例中，可以在資料庫之間使用相同的 UDT：

呼叫在不同資料庫中定義的預存程式。
查詢不同資料庫中定義的數據表。
從一個資料庫數據表 UDT 數據行中選取 UDT 數據，並將它插入具有相同 UDT 數據行的第二個資料庫。

在這些情況下，伺服器所需的任何轉換都會自動發生。您無法使用 Transact-SQL CAST 或 CONVERT 函數明確執行轉換。

請注意，當 SQL Server 資料庫引擎 tempdb 系統資料庫中建立工作數據表時，您不需要採取任何使用 UDT 的動作。這包括處理數據指標、數據表變數，以及包含 UDT 的使用者定義資料表值函式，以及透明地使用 tempdb。不過，如果您在tempdb中明確建立定義UDT數據行的臨時表，則必須以與用戶資料庫相同的方式在tempdb中註冊UDT。

另請參閱

CLR 使用者定義類型

分享方式：