在 SQL Server 中註冊使用者定義型別

發行項
05/07/2013

為了能夠在 Microsoft SQL Server 中使用使用者定義型別 (UDT)，您必須註冊此型別。註冊 UDT 包括註冊組件，以及在要使用該型別的資料庫中建立它。 UDT 的使用範圍為單一資料庫，而且除非已經向每個資料庫註冊相同的組件及 UDT，否則無法在多個資料庫中使用。一旦註冊 UDT 組件並建立此型別之後，您便可在 Transact-SQL 及用戶端程式碼中使用該 UDT。如需詳細資訊，請參閱＜CLR 使用者定義型別＞。

使用 Visual Studio 部署 UDT

使用 Microsoft Visual Studio 是部署 UDT 最簡單的方式。但是，若為較複雜的部署案例而且需要最大的彈性，請使用本主題稍後將要討論的 Transact-SQL。

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

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

使用 Transact-SQL 部署 UDT

您可以使用 Transact-SQL CREATE ASSEMBLY 語法，於要使用 UDT 的資料庫中註冊組件。它會儲存於資料庫系統資料表內部，而非檔案系統外部。如果 UDT 與外部組件相關，也必須將它們載入資料庫。 CREATE TYPE 陳述式可在要使用 UDT 的資料庫內建立 UDT。如需詳細資訊，請參閱＜CREATE ASSEMBLY (Transact-SQL)＞和＜CREATE TYPE (Transact-SQL)＞。

使用 CREATE ASSEMBLY

CREATE ASSEMBLY 語法會在要使用 UDT 的資料庫中註冊組件。一旦註冊此組件，它就不具有相依性。

不允許在給定資料庫內建立同一組件的多個版本。但是，根據給定資料庫中的文化特性建立相同組件的多個版本是可行的。 SQL Server 會根據 SQL Server 執行個體內所註冊的不同名稱來區分某個組件的多個文化特性版本。如需詳細資訊，請參閱 .NET Framework SDK 中的＜建立和使用強式名稱的組件＞。

當使用 SAFE 或 EXTERNAL_ACCESS 權限集合執行 CREATE ASSEMBLY 時，系統會檢查組件，以確定它可進行驗證且型別是安全的。如果您省略指定使用權限集合，則會假設為 SAFE。系統不會檢查使用 UNSAFE 權限集合的程式碼。如需有關組件權限集合的詳細資訊，請參閱＜設計組件＞。

範例

下列 Transact-SQL 陳述式會在 SQL Server 中將 Point 組件註冊到 AdventureWorks 資料庫 (使用 SAFE 權限集合)。如果省略 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，便無法對其進行修改，但可以改變此型別所依據的組件。在大多數情況下，您必須使用 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
識別每一個物件的數字，其中會將 1 的值指派給與給定 assembly_id 相關聯的第一個物件。如果有多個物件與相同 assembly_id 相關聯，則每一個後續 file_id 值都會遞增 1。
content
組件或檔案的十六進位表示法。

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

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

如果您將結果複製並貼至文字編輯器中，將會看到原始檔案中的分行符號及空格均已保留。

管理 UDT 及組件

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

將可能會發生變更的程序性程式碼與實作 UDT 所需的程式碼分開，可以大幅簡化維護作業。只包含 UDT 運作所需的程式碼並使您的 UDT 定義盡量簡單，這樣就會減少因程式碼修訂或錯誤修復而需要從資料庫卸除 UDT 本身的風險。

Currency UDT 及貨幣轉換函數

AdventureWorks 範例資料庫中的 Currency UDT 提供結構化 UDT 及其相關函數之建議方法的有用範例。 Currency UDT 可依據特定文化特性的貨幣系統來處理貨幣，並允許儲存不同貨幣類型，如美元、歐元等。 UDT 類別會將文化特性名稱顯示為字串，而將貨幣金額顯示為 decimal 資料類型。所有必要的序列化方法都包含在定義類別的組件中。實作從一種文化特性至另一種文化特性之貨幣轉換的函數，會實作成名為 ConvertCurrency 的外部函數，且此函數位於另一組件中。 ConvertCurrency 函數藉由從 AdventureWorks 資料庫中的資料表擷取轉換比率來完成其作業。如果轉換比率的來源應該變更，或應該對現有程式碼進行其他變更，則可輕鬆修改組件，而不會影響 Currency UDT。

藉由安裝 Common Language Runtime (CLR) 範例，可找到 Currency UDT 及 ConvertCurrency 函數的程式碼清單。

跨資料庫使用 UDT

根據定義，UDT 屬於單一資料庫的範圍。因此，在一個資料庫內定義的 UDT 無法用於其他資料庫中的資料行定義。為了在多個資料庫中使用 UDT，您必須在每個資料庫內的相同組件上執行 CREATE ASSEMBLY 及 CREATE TYPE 陳述式。具有相同名稱、強式名稱、文化特性、版本、使用權限集合及二進位內容的組件，會視為相同組件。

一旦在兩個資料庫中登錄 UDT 並可存取 UDT，您便可從某個資料庫轉換 UDT 值，以用於另一資料庫。在下列情況下，可以跨資料庫使用相同的 UDT：

呼叫不同資料庫中定義的預存程序。
查詢不同資料庫中定義的資料表。
從某個資料庫資料表 UDT 資料行選取 UDT 資料，並將其插入具有相同 UDT 資料行的第二個資料庫中。

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

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

請參閱

概念

CLR 使用者定義型別

共用方式為