UDT データの操作

[アーティクル]
05/07/2013

Transact-SQL には、UDT (ユーザー定義型) 列のデータを変更する際に特別な INSERT、UPDATE、または DELETE ステートメント構文は用意されていません。 Transact-SQL の CAST 関数または CONVERT 関数を使用して、ネイティブデータ型を UDT 型にキャストします。

UDT 列へのデータの挿入

次の Transact-SQL ステートメントでは、3 列のサンプルデータを Points テーブルに挿入します。 Point データ型は、UDT のプロパティとして公開されている整数値 X と Y で構成されます。コンマ区切りの X と Y の値を Point 型にキャストするには、CAST 関数または CONVERT 関数のいずれかを使用する必要があります。最初の 2 つのステートメントでは、CONVERT 関数を使用し、3 つ目のステートメントでは CAST 関数を使用して文字列値を Point 型に変換しています。

INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));

データの選択

次の SELECT ステートメントでは、UDT のバイナリ値を選択します。

SELECT ID, PointValue FROM dbo.Points

出力を判読可能な形式で表示するには、Point UDT の ToString メソッドを呼び出します。このメソッドでは、値を対応する文字列表記に変換します。

SELECT ID, PointValue.ToString() AS PointValue 
FROM dbo.Points;

結果は、次のようになります。

IDPointValue
----------
13,4
21,5
31,99

また、Transact-SQL の CAST 関数や CONVERT 関数を使用して、同じ結果を得ることもできます。

SELECT ID, CAST(PointValue AS varchar) 
FROM dbo.Points;

SELECT ID, CONVERT(varchar, PointValue) 
FROM dbo.Points;

Point UDT では、X 座標と Y 座標をプロパティとして公開しているので、座標を個別に選択することができます。次の、Transact-SQL ステートメントでは、X 座標と Y 座標を個別に選択します。

SELECT ID, PointValue.X AS xVal, PointValue.Y AS yVal 
FROM dbo.Points;

X プロパティと Y プロパティから整数値が返され、結果セットに表示されます。

IDxValyVal
----------
134
215
3199

変数を使用した作業

変数を使用するには、DECLARE ステートメントを使用して、UDT 型にその変数を割り当てます。次のステートメントでは、Transact-SQL の SET ステートメントを使用して値を代入し、変数に対して UDT の ToString メソッドを呼び出すことで結果を表示しています。

DECLARE @PointValue Point;
SET @PointValue = (SELECT PointValue FROM dbo.Points
    WHERE ID = 2);
SELECT @PointValue.ToString() AS PointValue;

結果セットには、次のように変数の値が表示されます。

PointValue
----------
-1,5

次の Transact-SQL ステートメントでは、変数の代入で SET ステートメントの代わりに SELECT ステートメントを使用して、同じ結果を得ています。

DECLARE @PointValue Point;
SELECT @PointValue = PointValue FROM dbo.Points
    WHERE ID = 2;
SELECT @PointValue.ToString() AS PointValue;

変数の代入に SELECT ステートメントを使用した場合と SET ステートメントを使用した場合には異なる点が 1 つあります。SELECT ステートメントでは 1 つのステートメントで複数の変数に代入できますが、SET 構文では、1 つ変数に代入するごとに 1 つの SET ステートメントが必要になります。

データの比較

クラスを定義する際に、IsByteOrdered プロパティに true を設定すると、比較演算子を使用して、UDT の値を比較できます。詳細については、「ユーザー定義型の作成」を参照してください。

SELECT ID, PointValue.ToString() AS Points 
FROM dbo.Points
WHERE PointValue > CONVERT(Point, '2,2');

値自体が比較できる場合は、IsByteOrdered の設定に関係なく、UDT の内部値を比較できます。次の Transact-SQL ステートメントでは、X の値が Y の値よりも大きい行を選択します。

SELECT ID, PointValue.ToString() AS PointValue 
FROM dbo.Points
WHERE PointValue.X < PointValue.Y;

また、一致する PointValue を検索する次のクエリのように、変数と比較演算子を組み合わせて使用することもできます。

DECLARE @ComparePoint Point;
SET @ComparePoint = CONVERT(Point, '3,4');
SELECT ID, PointValue.ToString() AS MatchingPoint 
FROM dbo.Points
WHERE PointValue = @ComparePoint;

UDT メソッドの呼び出し

Transact-SQL でも、UDT で定義されているメソッドを呼び出すことができます。 Point クラスには、Distance、DistanceFrom、および DistanceFromXY という 3 つのメソッドが定義されています。これらの 3 つのメソッドを定義しているコードリストについては、「ユーザー定義型のコーディング」を参照してください。

次の Transact-SQL ステートメントでは、PointValue.Distance メソッドを呼び出します。

SELECT ID, PointValue.X AS [Point.X], 
    PointValue.Y AS [Point.Y],
    PointValue.Distance() AS DistanceFromZero 
FROM dbo.Points;

結果は、次のように Distance 列に表示されます。

IDXYDistance
------------------------
1345
2155.09901951359278
319999.0050503762308

DistanceFrom メソッドでは、Point データ型の引数を受け取り、指定した点から PointValue までの距離を表示します。

SELECT ID, PointValue.ToString() AS Pnt,
   PointValue.DistanceFrom(CONVERT(Point, '1,99')) AS DistanceFromPoint
FROM dbo.Points;

結果には、テーブルの行ごとに DistanceFrom メソッドの結果が表示されます。

ID PntDistanceFromPoint
---------------------
13,495.0210502993942
21,594
31,990

DistanceFromXY メソッドでは、Points を引数として個別に受け取ります。

SELECT ID, PointValue.X as X, PointValue.Y as Y, 
PointValue.DistanceFromXY(1, 99) AS DistanceFromXY 
FROM dbo.Points

結果セットは、DistanceFrom メソッドの場合と同様になります。

UDT 列のデータの更新

UDT 列のデータを更新するには、Transact-SQL の UPDATE ステートメントを使用します。また、UDT のメソッドを使用して、オブジェクトの状態を更新することもできます。次の Transact-SQL ステートメントでは、テーブルの単一行を更新します。

UPDATE dbo.Points
SET PointValue = CAST('1,88' AS Point)
WHERE ID = 3

また、UDT の要素を個別に更新することもできます。次の Transact-SQL ステートメントでは、Y 座標の値のみを更新します。

UPDATE dbo.Points
SET PointValue.Y = 99
WHERE ID = 3

バイト順序を true に設定して UDT が定義されている場合、Transact-SQL は WHERE 句でこの UDT 列を評価することができます。

UPDATE dbo.Points
SET PointValue = '4,5'
WHERE PointValue = '3,4';

更新の制限事項

Transact-SQL を使用して、複数のプロパティを一度に更新することはできません。たとえば、1 つの UPDATE ステートメントでは同じ列名を 2 回使用することができないため、次のステートメントは失敗します。

UPDATE dbo.Points
SET PointValue.X = 5, PointValue.Y = 99
WHERE ID = 3

座標の値を個別に更新するには、Point UDT のアセンブリにミューテーターメソッドを作成する必要があります。ミューテーターメソッドを作成すると、Transact-SQL の UPDATE ステートメントでミューテーターメソッドを呼び出して、オブジェクトを更新することができます。次に、例を示します。

UPDATE dbo.Points
SET PointValue.SetXY(5, 99)
WHERE ID = 3

UDT 列のデータの削除

UDT のデータを削除するには、Transact-SQL の DELETE ステートメントを使用します。次のステートメントでは、WHERE 句で指定した条件に一致するテーブル内のすべての行が削除されます。 DELETE ステートメントで WHERE 句を省略すると、テーブル内のすべての行が削除されます。

DELETE FROM dbo.Points
WHERE PointValue = CAST('1,99' AS Point)

UDT 列の値を削除し、それ以外の行の値については現状維持する場合は、UPDATE ステートメントを使用します。次の例では、PointValue に NULL を設定します。

UPDATE dbo.Points
SET PointValue = null
WHERE ID = 2