SQLデータ型

PL/SQLデータ型にはSQLデータ型が含まれます。

SQLデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください(記載されているデータ型およびサブタイプ、データ型の比較のルール、データ変換、リテラル、および書式モデルに関する情報はすべて、次の項目を除き、SQLとPL/SQLの両方に適用されます)。

• 最大サイズの相違

• BINARY_FLOATおよびBINARY_DOUBLEの追加のPL/SQL定数

• BINARY_FLOATおよびBINARY_DOUBLEの追加のPL/SQLサブタイプ

SQLとは異なり、PL/SQLでは変数を宣言できます。変数には次のトピックが適用されます。

• BOOLEANデータ型

• JSONデータ型

• VECTORデータ型

• CHARおよびVARCHAR2変数

• LONGおよびLONG RAW変数

• ROWIDおよびUROWID変数

最大サイズの相違

表4-1にリストしたSQLのデータ型は、PL/SQLとSQLで最大サイズが異なります。

表4-1 PL/SQLとSQLで最大サイズが異なるデータ型

データ型	PL/SQLでの最大サイズ	SQLでの最大サイズ
CHAR脚注1	32,767バイト	2,000バイト
NCHAR脚注1	32,767バイト	2,000バイト
RAW脚注1	32,767バイト	2,000バイト脚注2
VARCHAR2脚注1	32,767バイト	4,000バイト脚注2
NVARCHAR2脚注1	32,767バイト	4,000バイト脚注2
LONG脚注3	32,760バイト	2ギガバイト(GB) - 1
LONG RAW脚注3	32,760バイト	2 GB
BLOB	128テラバイト(TB)	(4GB - 1) * database_block_size
CLOB	128 TB	(4GB - 1) * database_block_size
NCLOB	128 TB	(4GB - 1) * database_block_size

^脚注1

このデータ型の値の最大サイズをPL/SQLで指定する場合は、定数または変数ではなく、1から32,767の間の値を持つ整数リテラルを使用します。

^脚注2

このサイズの違いをなくすには、『Oracle Database SQL言語リファレンス』の手順に従ってください。

^脚注3

既存のアプリケーションとの下位互換性を保つためにのみサポートされています。

BINARY_FLOATおよびBINARY_DOUBLEの追加のPL/SQL定数

SQLデータ型のBINARY_FLOATおよびBINARY_DOUBLEは、それぞれ単精度および倍精度のIEEE 754形式の浮動小数点数を表します。

BINARY_FLOATおよびBINARY_DOUBLEの計算では、例外は発生しないため、これらの計算によって生成された値がオーバーフローやアンダーフローなどの状態になっていないかどうか、事前定義の定数と比較して確認する必要があります(例は、『Oracle Database SQL言語リファレンス』を参照してください)。PL/SQLには、この型の定数がSQLよりも多数あります。

表4-2に、BINARY_FLOATおよびBINARY_DOUBLEとして事前定義されているPL/SQL定数を示します。SQLでも定義されているものには注を付けてあります。

表4-2 事前定義のPL/SQLのBINARY_FLOATおよびBINARY_DOUBLEの定数

定数	説明
BINARY_FLOAT_NAN (*)	条件IS NAN(非数値)がTRUEになっているBINARY_FLOAT値。
BINARY_FLOAT_INFINITY (*)	単精度の正の無限大
BINARY_FLOAT_MAX_NORMAL	最大正規BINARY_FLOAT値。
BINARY_FLOAT_MIN_NORMAL	最小正規BINARY_FLOAT値。
BINARY_FLOAT_MAX_SUBNORMAL	最大非正規BINARY_FLOAT値。
BINARY_FLOAT_MIN_SUBNORMAL	最小非正規BINARY_FLOAT値。
BINARY_DOUBLE_NAN (*)	条件IS NAN(非数値)がTRUEになっているBINARY_DOUBLE値。
BINARY_DOUBLE_INFINITY (*)	倍精度の正の無限大
BINARY_DOUBLE_MAX_NORMAL	最大正規BINARY_DOUBLE値。
BINARY_DOUBLE_MIN_NORMAL	最小正規BINARY_DOUBLE値。
BINARY_DOUBLE_MAX_SUBNORMAL	最大非正規BINARY_DOUBLE値。
BINARY_DOUBLE_MIN_SUBNORMAL	最小非正規BINARY_DOUBLE値。

(*) この定数は、SQLでも事前定義されています。

BINARY_FLOATおよびBINARY_DOUBLEの追加のPL/SQLサブタイプ

PL/SQLでは、次のサブタイプが事前定義されています。

• SIMPLE_FLOAT(SQLデータ型BINARY_FLOATのサブタイプ)

• SIMPLE_DOUBLE(SQLデータ型BINARY_DOUBLEのサブタイプ)

各サブタイプはそのベース型と同じ範囲を取り、NOT NULL制約(「NOT NULL制約」を参照)を含んでいます。

値NULLを取らないとわかっている変数は、BINARY_FLOATまたはBINARY_DOUBLEではなく、SIMPLE_FLOATまたはSIMPLE_DOUBLEとして宣言します。これらのサブタイプを使用すると、NULLかどうかのチェックのためのオーバーヘッドが発生せず、そのベース型を使用するよりパフォーマンスが大幅に向上します。PLSQL_CODE_TYPE='INTERPRETED'よりもPLSQL_CODE_TYPE='NATIVE'を使用する方が、パフォーマンスの向上幅が大きくなります(詳細は、「ハードウェア算術を使用するデータ型の使用」を参照してください)。

BOOLEANデータ型

BOOLEANデータ型には、論理値(ブール値のTRUEとFALSE、およびNULL値)が格納されます。NULLは、不明な値を表します。

BOOLEAN変数を宣言するための構文は、次のとおりです。

variable_name BOOLEAN

デフォルトでは、DBMS_OUTPUT.PUTまたはDBMS_OUTPUT.PUT_LINEサブプログラムなど、プロシージャやファンクションのNUMBERまたはVARCHAR2パラメータにBOOLEAN値を渡すことはできません。これらのプロシージャにBOOLEAN値を渡すには、初期化パラメータPLSQL_IMPLICIT_CONVERSION_BOOLをTRUEに設定します。このパラメータをTRUEに設定すると、変数の割当てで暗黙的な変換もできるようになります(たとえば、NUMBERまたはVARCHAR2の値をBOOLEAN変数に割り当てる場合)。また、値をTRUEにすると、BOOLEAN変数の割当てで文字列リテラルを使用できるようになります。このパラメータは、CASTやファンクションTO_NUMBER、TO_CHAR、TO_BOOLEANなど、明示的な変換には影響しません。

サブプログラムがBOOLEAN型と数値型または文字型でオーバーロードされている場合、PLSQL_IMPLICIT_CONVERSION_BOOLをTRUEに設定すると、コンパイル時エラーが発生する可能性があります。このパラメータの使用時に発生する可能性があるオーバーロード・エラーの詳細は、「サブプログラムのオーバーロード・エラー」を参照してください。

PLSQL_IMPLICIT_CONVERSION_BOOLパラメータは存続可能、つまりこのパラメータを使用して作成されたすべてのPL/SQLユニットは、そのユニットがREUSE SETTINGS句でコンパイルされて作成された時点で指定された値を使用します。

BOOLEAN式をBOOLEAN変数に割り当てる(PLSQL_IMPLICIT_CONVERSION_BOOLパラメータの値に関係なく)ことも可能です。静的なBOOLEAN式の詳細は、「BOOLEAN式」を参照してください。

関連項目:

• PLSQL_IMPLICIT_CONVERSION_BOOLパラメータの詳細は、『Oracle Databaseリファレンス』を参照してください

• SQLのBOOLEANデータ型の詳細およびTRUEとFALSEを表すために使用可能な文字列リテラルのリストは、『Oracle Database SQL言語リファレンス』を参照してください

例4-1 BOOLEAN値の出力

この例では、プロシージャDBMS_OUTPUT.PUT_LINEに直接値を渡すことで、BOOLEAN値が出力されます。このコードの実行は、TRUEに設定されている初期化パラメータPLSQL_IMPLICIT_CONVERSION_BOOLによって異なります。

DECLARE
  t_b boolean := TRUE;
  f_b boolean := FALSE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('My bool is: ' || t_b);
  DBMS_OUTPUT.PUT_LINE('My bool is: ' || f_b);
END;

結果:

My bool is: TRUE
My bool is: FALSE

JSONデータ型

JSONデータ型インスタンスは、PL/SQLサブプログラムで使用できます。PL/SQL JSONデータ型は、ネストしたJSON値への高速アクセスのために、バイナリ形式でデータベースに格納されます。

JSONデータ型とそのインスタンスは、次のようなSQLデータ型が使用可能なほとんどの場所で使用できます。

• 表やビューDDLの列の型として

• PL/SQLサブプログラムのパラメータの型として

• レコード内の要素またはフィールド・タイプ、PL/SQLコレクションおよび%ROWTYPE属性として

• SQL/JSON関数または条件が許可される式。

JSONデータ型は、現在SQLコレクションまたはオブジェクトではサポートされていません。

ここでのトピック

• PL/SQLとJSONの型変換

関連項目:

• JSONスキーマの詳細は、json-schema.orgを参照してください

• JSONデータを使用してPL/SQLを使用する方法の詳細は、Oracle Database JSON開発者ガイドを参照してください

• JSON用のPL/SQLオブジェクト・タイプの詳細は、『Oracle Database JSON開発者ガイド』を参照してください

PL/SQLとJSONの型変換

組込み関数json_valueは、スカラー・データ型マッピングおよびJSONオブジェクトからユーザー定義PL/SQLタイプへのマッピングをサポートしています。ユーザー定義PL/SQLまたはSQL集計タイプのインスタンスの場合、PL/SQL JSONコンストラクタは対応するJSONオブジェクトまたはJSON配列型インスタンスを返します。

json_valueでは、返される集計データ型としてPL/SQLユーザー定義サブタイプの使用がサポートされています。これには、返される集計データ型でフィールドまたは要素データ型として使用されるサブタイプによって使用される制約または初期化子のサポートが含まれます。

すべてのPL/SQLレコード・フィールドとコレクション・データ要素のデータ型制約は、PL/SQL json_valueによって適用されます。制約には、文字の最大長、数値スケールと精度、時間、タイム・スタンプ、間隔の制約、整数範囲チェック、NOT NULL制約が含まれます。

これらのタイプは、トップレベルのSQL (SQLオブジェクトおよびコレクション用)、パッケージ・レベルのPL/SQL、またはPL/SQLファンクション、プロシージャまたは匿名コール・ブロック内のローカルなど、json_valueコール・サイトに表示される任意のプログラム・スコープで宣言できます。

PL/SQL固有のユーザー定義集計タイプには、次のものがあります。

• レコード

• INDEX BY PLS_INTEGERコレクション

• 連想配列

• ネストした表

• 可変長配列

• オブジェクト

PL/SQL集計タイプは、PL/SQL組込み関数のINおよびRETURNデータ型として使用できます。すべてのPL/SQL %ROWTYPEは、json_valueのRETURNING句でサポートされます。

ON MISMATCH句をjson_valueとともに使用して、タイプ一致の例外を処理できます。指定された戻り型にターゲットのJSON値を変換できない場合に、必要な動作を指定するために使用します。PL/SQLレコード、index by PLS_INTEGERコレクションおよびindex by VARCHAR2コレクションはアトミックnullにできません。したがって、これらのタイプのいずれかが戻り型として指定されている場合、NULL ON MISMATCH句でコンパイル時にエラーが発生します。ON MISMATCH句の詳細は、Oracle Database JSON開発者ガイドを参照してください。

タイプ名の解決および範囲指定

json_valueで使用されるタイプ名は、標準のPL/SQL名前解決ルールを使用して解決されます。PL/SQLでは、名前が参照されるPL/SQLコードの最も内側の範囲から名前を探し始め、名前が解決されるまで、検索を外部の範囲に拡げます。

PL/SQL組込み関数json_valueは、次の形式を含む最大3つのパート名を解決します。

• <schema name>.<package name>.<type name>

• <package name>.<type name>

• <schema name>.<type name>

• <type name>

これは、1つまたは2つのパート・タイプ名のみを解決するSQLのjson_value組込み関数とは異なります。

シノニムは、フル・タイプ名の文字列で適切に使用でき、これらのシノニムがタイプ名の解決中に解決されます。

ここでのトピック

• JSONオブジェクトおよびPL/SQLレコード

• JSONオブジェクトおよびIndex by PLS_INTEGERおよびネストした表のコレクション

• JSON配列とネストした表、Index by PLS_INTEGERおよびVarrayコレクション

• JSONオブジェクトと連想配列

関連項目:

• json_value組込み関数の詳細は、『Oracle Database JSON開発者ガイド』を参照してください

JSONオブジェクトとPL/SQLレコード

PL/SQLレコードは、名前/値のペアを使用してデータを保持し、JSONコンストラクタおよび組込み関数json_valueを介してJSONオブジェクトとの間でそれぞれマップできます。

ここでのトピック

• JSONオブジェクトからPL/SQLレコードへ

• PL/SQLレコードからJSONオブジェクトへ

JSONオブジェクトからPL/SQLレコードへ

RETURNING句でPL/SQLレコード名が指定されている場合、json_valueは入力JSONオブジェクトをPL/SQLレコードにマップし、PL/SQLレコードのインスタンスを返します。入力JSONがJSONオブジェクトでない場合は、ON MISMATCH句が適用されます。

マッピングを実行するには、各JSONキー名をPL/SQLレコードの一意の属性にマップする必要があります。この比較では、名前を囲む二重引用符を無視するデフォルトの大/小文字を区別しない比較と、マップするいずれかのタイプのキーまたは属性名の配置を使用する必要があります。

次に示すように、大/小文字を区別するマッピング構文を使用して、大/小文字を区別するマッピングがサポートされています。

DECLARE
    TYPE personrecord IS RECORD(first VARCHAR2(10), last VARCHAR2(10));
    p personrecord;
BEGIN
    p := JSON_VALUE(JSON('{"FIRST":"Jane", "LAST":"Cooper"}'), '$'
    RETURNING personrecord USING CASE_SENSITIVE MAPPING);
    DBMS_OUTPUT.PUT_LINE(p.first ||' '|| p.last);
END;
/

キー名がマップされると、キー名のJSON値がPL/SQLレコード属性にコピーされます。JSON値は、マップされたフィールドのPL/SQLデータ型に変換可能である必要があります。値タイプが変換可能でない場合、MISMATCHエラーが発生します。

JSONフィールドを含むレコード・タイプは、json_valueへのコールでサポートされます。JSONフィールドは、JSONオブジェクトやJSON配列を含む任意のJSONタイプにマップされます。つまり、JSON属性名がレコード・フィールド名にマップされ、レコード・フィールドがJSONタイプである場合、PL/SQLはJSON属性のJSON値をレコード・フィールドのJSONタイプにコピーします。

JSON値は有効なJSONである必要があります。JSONドキュメントがテキストの場合は、JSONフィールドにコピーされたときにJSON値が解析され、有効なJSONであることが検証されます。コピーが完了すると、その属性に対してそれ以上の再帰的マッピングは実行されません。

例4-2 JSONオブジェクトからPL/SQLレコードへの変換

この例では、同じJSONオブジェクトを2つの異なるPL/SQLレコードにマップする方法を示します。

DECLARE
    TYPE theRec1 IS RECORD (field1 NUMBER, field2 VARCHAR2(10));
    TYPE theRec2 IS RECORD ("fIeLd2" VARCHAR2(20), "FielD1" NUMBER);

    Rec1 theRec1;
    Rec2 theRec2;
BEGIN
    Rec1 := JSON_VALUE(JSON('{"FIELD1":10, "field2":"hello"}'), '$' RETURNING theRec1);
    Rec2 := JSON_VALUE(JSON('{"FIELD1":10, "field2":"hello"}'), '$' RETURNING theRec2);
END;
/

PL/SQLブロックを実行すると、Rec1およびRec2にそれぞれ次の値が含まれます。

theRec1(field1=>10, field2=>'hello')
theRec2("fIeLd2"=>'hello', "FielD1"=>10)

PL/SQLレコードからJSONオブジェクトへ

SQLオブジェクトとPL/SQLレコード・タイプ・インスタンス(<table | view | cursor>%ROWTYPE属性によって作成された暗黙的なレコードを含む)は、JSONコンストラクタへの有効な入力として許可されます。

PL/SQLオブジェクト属性名がJSONキー名になります。二重引用符で囲まれた属性名は大/小文字が区別されるJSONキー名になり、二重引用符で囲まれていない属性名は大文字のJSONキー名になります。PL/SQLオブジェクト属性値は、最も近いJSON値タイプにマップされます。

例4-3 PL/SQLレコードからJSONオブジェクトへの変換

DECLARE
    TYPE theRec IS RECORD(field1 NUMBER, "Field2" NUMBER);
    myRec theRec := theRec(10, 20);
    myJson JSON;
BEGIN
    myJson := JSON(myRec);
    DBMS_OUTPUT.PUT_LINE(JSON_SERIALIZE(myJson));
END;
/

結果:

{"FIELD1":10, "Field2":20}

JSONオブジェクトおよびIndex by PLS_INTEGERおよびネストした表のコレクション

Index by PLS_INTEGERコレクションおよびネストした表コレクションは、それぞれ組込みのjson_value関数およびJSONコンストラクタを使用してJSONオブジェクトとの間で双方向に変換できます。

ここでのトピック

• JSONオブジェクトからIndex by PLS_INTEGERおよびネストした表のコレクション

• Index by PLS_INTEGERコレクションおよびネストしたタイプからJSONオブジェクト

JSONオブジェクトからIndex by PLS_INTEGERおよびネストした表のコレクション

Index by PLS_INTEGERおよびネストした表コレクションの両方に、整数索引付き要素に依存する疎コレクション型を指定できます。これらのタイプは、オブジェクトの文字列キー属性がコレクションの整数索引の文字列表現であるJSONオブジェクトにマップされます。

JSONオブジェクトからいずれかのコレクション型への変換時に、JSONオブジェクト文字列キー属性が整数値に正しく変換されない場合、エラーが発生します。ネストした表コレクションでは、キー属性が正の整数である必要があります。それ以外の場合はエラーが発生します。また、最大キー値は、JSONオブジェクトの要素数を超えることはできません。キー値をさらに大きくする必要がある場合は、Index by PLS_INTEGERコレクションを使用できます。

オブジェクト内の索引値の間にギャップがある場合、これらのギャップは両方のコレクション型で再作成されます。つまり、JSONオブジェクトの最小値と最大値の索引の間に要素がない場合、それらの要素もコレクションにありません。欠落している要素はNULL要素と同じではないことに注意してください。

JSONオブジェクト索引キー属性はソート順である必要はありません。コレクションに挿入されるとソートされます。

例4-4 JSONオブジェクトからIndex by PLS_INTEGERコレクションへの変換

この例では、組込み関数json_valueを使用した、JSONオブジェクトからIndex by PLS_INTEGERコレクションへの変換を示します。

DECLARE
    TYPE theIBPLS IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    myIBPLS theIBPLS;
BEGIN
    myIBPLS := JSON_VALUE(JSON('{"-10":10, "-1":1, "100":-100}'), '$' RETURNING theIBPLS);
END;
/

PL/SQLブロックを実行すると、次の要素値を使用してIndex by PLS_INTEGERコレクションが作成されます。

theIBPLS(-10=>10, -1=>1, 100=>-100)

例4-5 JSONオブジェクトからネストした表コレクションへの変換

この例では、組込み関数json_valueを使用したJSONオブジェクトのネストした表コレクションへの変換を示します。

DECLARE
    TYPE theNSTTAB IS TABLE OF NUMBER;
    myNSTTAB theNSTTAB;
BEGIN
    myNSTTAB := JSON_VALUE(JSON('{"1":10, "2":20, "3":30, "4":40}'), '$' RETURNING theNSTTAB);
END;
/

PL/SQLブロックを実行すると、次の値を使用してネストした表コレクションが作成されます。

theNSTTAB(1=>10, 2=>20, 3=>30, 4=>40)

Index by PLS_INTEGERコレクションおよびネストしたタイプからJSONオブジェクト

Index by PLS_INTEGERコレクションは、JSONコンストラクタに渡されたときに索引値が保持されるJSONオブジェクトに変換されます。JSONオブジェクトとして表される場合、このコレクションの索引は、索引整数値のJSON文字列表現として表示されます。

PL/SQLとJSONの往復と、PL/SQLに戻るときの疎を保持するために、ネストした表コレクションがJSONコンストラクタに渡されるときに、JSONオブジェクトに変換されます。JSONオブジェクトとして表される場合、ネストした表索引は、索引の整数値のJSON文字列表現として表示されます。

例4-6 Index by PLS_INTEGERコレクションからJSONオブジェクトへの変換

この例では、JSONコンストラクタを使用した、Index by PLS_INTEGERコレクションからJSONオブジェクトへの変換を示します。

DECLARE
    TYPE theIBPLS IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    myIBPLS theIBPLS := theIBPLS(-1=>1, 2=>2, -3=>3);
    myJSON JSON;
BEGIN
    myJSON := JSON(myIBPLS);
    DBMS_OUTPUT.PUT_LINE(JSON_SERIALIZE(myJSON));
END;
/

結果:

{ "-3":3, "-1":1, "2":2 }

例4-7 ネストした表からJSONオブジェクトへの変換

この例では、JSONコンストラクタを使用したスパース・ネストした表のJSONオブジェクトへの変換を示します。

DECLARE
    TYPE theNSTTAB IS TABLE OF NUMBER;
    myNSTTAB theNSTTAB := theNSTTAB(1=>1, 2=>2, 3=>3);
    myJSON JSON;
BEGIN
    myNSTTAB.delete(2); --myNSTTAB becomes sparse when elements are deleted
    myJSON := JSON(myNSTTAB);
    DBMS_OUTPUT.PUT_LINE(JSON_SERIALIZE(myJSON));
END;
/

結果:

{ "1":1, "3":3 }

JSON配列とネストした表、Index by PLS_INTEGERおよびVarrayコレクション

JSON配列は組込みのjson_value関数を使用して、ネストした表、Index by PLS_INTEGERまたはVarrayコレクションに変換されます。VARRAYは、JSONコンストラクタを通過するとJSON配列に変換され、Index by PLS_INTEGERコレクションおよびネストした表はJSONオブジェクトに変換されます。

ここでのトピック

• JSON配列からネストした表、Index by PLS_INTEGERおよびVarrayコレクション

• JSON配列への配列

JSON配列からネストした表、Index by PLS_INTEGERおよびVarrayコレクション

RETURNING句でIndex by PLS_INTEGER、ネストした表またはVARRAYコレクションが指定されている場合、json_valueは入力JSON配列をPL/SQLコレクション型に変換し、PL/SQLコレクションのインスタンスを返します。入力JSONがJSON配列でない場合は、MISMATCHエラーが発生します。

JSON配列をPL/SQLコレクションに変換するには、JSON配列要素が1つずつコレクションに挿入されます。挿入は、PL/SQLコレクションの索引1に挿入されたJSON配列の最初の要素から始まり、最後のJSON配列要素がコレクションに挿入されると終了します。挿入された要素ごとに、コレクション索引が1ずつ増分されます。

• JSONのNULL要素により、PL/SQLのNULL要素がコレクションに挿入されます。

• JSON配列の要素数が対応するVARRAYのサイズを超えると、MISMATCHエラーが発生します。

• JSON要素タイプがPL/SQLコレクション要素タイプに変換できない場合、MISMATCHエラーが発生します。

例4-8 JSON配列からIndex by PLS_INTEGERコレクションへの変換

この例では、組込み関数json_valueを使用して、JSON配列をIndex by PLS_INTEGERコレクションに変換します。

DECLARE
    TYPE theIBPLS IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    myIBPLS theIBPLS;
BEGIN
    myIBPLS := JSON_VALUE(JSON('[1, 2, 3, 4, 5]'), '$' RETURNING theIBPLS);
END;
/

このPL/SQLブロックを実行すると、myIBPLSに次の値が設定されます:

theIBPLS(1=>1, 2=>2, 3=>3, 4=>4, 5=>5)

例4-9 JSON配列からVARRAYへの変換

この例では、組込み関数json_valueを使用して、JSON配列をVARRAYに変換します。

DECLARE
    TYPE theVARRAY IS VARRAY(5) OF NUMBER;
    myVARRAY theVARRAY;
BEGIN
    myVARRAY := JSON_VALUE(JSON('[1, 2, 3, 4, 5]'), '$' RETURNING theVARRAY);
END;
/

このPL/SQLブロックを実行すると、myVARRAYに次の値が設定されます。

theVARRAY(1=>1, 2=>2, 3=>3, 4=>4, 5=>5)

例4-10 JSON配列からネストした表への変換

この例では、組込み関数json_valueを使用して、JSON配列をネストした表に変換します。

DECLARE
    TYPE theNESTEDTABLE IS TABLE OF NUMBER;
    myNESTEDTABLE theNESTEDTABLE;
BEGIN
    myNESTEDTABLE := JSON_VALUE(JSON('[1, 2, 3, 4, 5]'), '$' RETURNING theNESTEDTABLE);
END;
/

このPL/SQLブロックを実行すると、myNESTEDTABLEに次の値が設定されます。

theNESTEDTABLE(1=>1, 2=>2, 3=>3, 4=>4, 5=>5)

JSON配列への配列

VARRAYは、JSONコンストラクタに渡されると、JSON配列に変換されます。

VARRAYがJSON配列に変換されると、コレクションの各要素が、最小のコレクション索引の要素から始まり、最大のコレクション索引の要素で終わるJSON配列に挿入されます。索引はJSON配列には転送されず、要素値のみが転送されます。

JSONコンストラクタに渡されると、Index by PLS_INTEGERコレクションおよびネストしたタイプはJSON配列ではなくJSONオブジェクトに変換されます。

例4-11 VARRAYからJSON配列への変換

DECLARE
    TYPE theVarray IS VARRAY(4) OF NUMBER;
    myVarray theVarray := theVarray(1, 2, 3, null);
    myJSON JSON;
BEGIN
    myJSON := JSON(myVarray);
    DBMS_OUTPUT.PUT_LINE(JSON_SERIALIZE(myJSON));
END;
/

結果:

[1, 2, 3, null]

JSONオブジェクトと連想配列

連想配列は、それぞれJSONコンストラクタと組込み関数json_valueを使用して、JSONオブジェクトとの間で変換できます。

ここでのトピック

• 連想配列へのJSONオブジェクト

• JSONオブジェクトへの連想配列

連想配列へのJSONオブジェクト

JSONオブジェクトが連想配列にマップされると、各JSONキー名と値のペアは、連想配列の順序またはコレクション(あるいはその両方)に基づいて連想配列に挿入されます。

連想配列キー名は大/小文字が区別され、挿入ではJSONキー名の大/小文字が保持されます。キーのJSON値は必要に応じて連想配列要素タイプに変換され、キーの名前/値ペアが連想配列に挿入されます。

SQLオブジェクトおよびPL/SQLレコードと同様に、JSON値はネストしたオブジェクトまたは配列であり、連想配列要素タイプに変換可能である必要があります。値タイプが変換可能でない場合、MISMATCHエラーが発生します。

例4-12 JSONオブジェクトから連想配列への変換

この例では、組込み関数json_valueを使用して、JSONオブジェクトを連想配列に変換します。

DECLARE
    TYPE theASCARRAY IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
    myAscArray theASCARRAY;
BEGIN
    myAscArray := JSON_VALUE(JSON('{"Key1":10, "Key2":20}'), '$' RETURNING theASCARRAY);
END;
/

このPL/SQLブロックを実行すると、次の2つの要素に関連付けられます。

theASCARRAY('Key1'=>10, 'Key2'=>20)

JSONオブジェクトへの連想配列

連想配列をJSONオブジェクトに変換するプロセスは、すべての連想配列キーと値を名前/値のペアとしてJSONオブジェクトに挿入することです。PL/SQL連想配列内のすべてのキー名が一意であり、JSON属性の順序がJSON標準で指定されていないため、挿入の順序は重要ではない可能性があります。ただし、キー値は多くの場合、連想配列の内部ソート順または照合に基づいて挿入されます。

連想配列にはvarchar2キーがあるため、JSONオブジェクトに挿入されるキー・タイプはJSON文字列です。連想配列内のキーの場合、JSONオブジェクトへのコピーに保持されます。

連想配列要素の値は、キーの後にJSONオブジェクトにコピーされます。連想配列の要素タイプがネストした集計タイプである場合、集計タイプに一致するJSONオブジェクトまたは配列がJSON値として作成されます。

例4-13 連想配列からJSONオブジェクトへの変換

この例では、JSONコンストラクタを使用して、連想配列をJSONオブジェクトに変換します。

DECLARE
    TYPE AsscArray IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
    myAsscArray AsscArray := AsscArray('FIRST_NAME' => 'Bob', 'LAST_NAME' => 'Jones');
    myJson JSON;
BEGIN
    myJson := JSON(myAsscArray);
    DBMS_OUTPUT.PUT_LINE(JSON_SERIALIZE(myJson));
END;
/

このPL/SQLブロックを実行すると、次の値を持つJSONオブジェクトが作成されます。

{"FIRST_NAME":"Bob", "LAST_NAME":"Jones"}

VECTORデータ型

ベクトル値は、NULL以外の数値の配列で、すべて同じ数値型です。PL/SQLのベクトルには2つの記憶域属性があります。ベクトルを構成する値の数はそのディメンションで、値の数値型はその書式です。

PL/SQLのベクトル変数はベクトル値を保持し、ディメンション、ディメンション形式および格納形式に関して柔軟性があるか、部分的に柔軟性があるか、完全に柔軟性がありません。柔軟なPL/SQLベクトル変数へのベクトル値の割当ては常に成功し、変換は行われません。値と変数が柔軟性のない属性で異なる場合、代入時にエラーが発生します。すべての場合に、ベクトルに格納される要素は同じ数値型である必要があります。

指定する場合、ベクトルの形式は次のいずれかである必要があります: FLOAT64、FLOAT32、INT8またはBINARY。BINARYベクトルのディメンションは8の倍数である必要があります。BINARYベクトルの使用の詳細は、Oracle Database AI Vector Searchユーザーズ・ガイドを参照してください。

ベクトル宣言には、オプションでSPARSEまたはDENSEのいずれかの格納形式属性を含めることができます。指定しない場合、ベクトルはデフォルトでDENSEになります。

疎ベクトルは、ディメンション値の大部分がゼロであるベクトルです。ほとんどのディメンション値がゼロになると想定される場合に疎ベクトルを使用すると、ゼロ以外の索引値のみが格納されるため、記憶域を節約できます。SPARSEベクトルの文字列表現は、ディメンション値、ゼロ以外のディメンション値を表す索引(非負の整数)のJSON配列、およびそれらのゼロ以外の値のJSON配列の3つの部分で構成されるJSON配列です。たとえば、次の疎表現では、4番目と6番目のディメンションで値が8および24の128個のディメンションのベクトルを示します。他のすべてのディメンション値はゼロと見なされます。

[128, [4, 6], [8, 24]]

SPARSEベクトルの詳細は、Oracle Database AI Vector Search ユーザーズ・ガイドを参照してください。

ノート:

ディメンション、ディメンション形式および格納形式のチェックは実行時に完了します。

PL/SQLのVECTORデータ型は、独自のスカラー型ファミリとして示され、PL/SQL演算子で使用したり、PL/SQLプロシージャおよび関数に渡したり、NULLに設定したり、PL/SQLの他のデータ型と同じ方法で使用できます。ベクトル変数はNULLベクトルを保持できますが、ベクトルの値をNULLにすることはできません。

PL/SQLでの代入のセマンティクスおよび暗黙的な変換の処理は、SQLと異なります。SQLでは次元が完全一致することのみが求められますが、PL/SQLでは正常な代入のためには形式と次元の両方が一致することが求められます。また、SQLではVECTORと文字列型の暗黙的な変換が許可されますが、PL/SQLではベクトルと他の型の暗黙的な変換はサポートされません。SQLおよびPL/SQLでは、ベクトルの等価比較はサポートされません。

変数がベクトル変数またはベクトル列で%TYPEを使用して宣言されている場合、宣言された変数は、参照されるベクトル変数または列の格納属性を継承したベクトルになります。次の例は、この概念を示しています。

CREATE TABLE PLS_VEC_TAB(
    v1 vector, 
    v2 vector(100), 
    v3 vector(*, INT8),
    v4 vector(100, INT8), 
    v5 vector(1024, BINARY),
    v6 vector(100, FLOAT32, DENSE),
    v7 vector(100, FLOAT32, SPARSE)
);

DECLARE
    vec0 vector;                 -- dimension and format are flexible, storage format is DENSE
    vec1 PLS_VEC_TAB.v1%TYPE;    -- dimension and format are flexible, storage format is DENSE
    vec2 PLS_VEC_TAB.v2%TYPE;    -- dimension is 100, format is flexible, storage format is DENSE
    vec3 PLS_VEC_TAB.v3%TYPE;    -- dimension is flexible, format is INT8, storage format is DENSE
    vec4 PLS_VEC_TAB.v4%TYPE;    -- dimension is 100, format is INT8, storage format is DENSE
    vec5 PLS_VEC_TAB.v5%TYPE;    -- dimension is 1024, format is BINARY, storage format is DENSE
    vec6 PLS_VEC_TAB.v6%TYPE;    -- dimension is 100, format is FLOAT32, storage format is DENSE
    vec7 PLS_VEC_TAB.v7%TYPE;    -- dimension is 100, format is FLOAT32, storage format is SPARSE
	
    vec_0 vec0%TYPE;    -- dimension and format are flexible, storage format is DENSE
    vec_1 vec1%TYPE;    -- dimension and format are flexible, storage format is DENSE
    vec_2 vec2%TYPE;    -- dimension is 100, format is flexible, storage format is DENSE
    vec_3 vec3%TYPE;    -- dimension is flexible, format is INT8, storage format is DENSE
    vec_4 vec4%TYPE;    -- dimension is 100, format is INT8, storage format is DENSE
    vec_5 vec5%TYPE;    -- dimension is 1024, format is BINARY, storage format is DENSE
    vec_6 vec6%TYPE;    -- dimension is 100, format is FLOAT32, storage format is DENSE
    vec_7 vec7%TYPE;    -- dimension is 100, format is FLOAT32, storage format is SPARSE
BEGIN
    NULL;
END;
/

VECTORデータ型とそのインスタンスは、次のようなSQLデータ型が使用可能なほとんどの場所で使用できます:

• レコード内の要素またはフィールド・タイプ、PL/SQLコレクションおよび%ROWTYPE属性として%ROWTYPE属性は、基礎となるベクトル列のディメンションと形式の両方を継承することに注意してください。
• 疑似レコードOLDおよびNEWを含むPL/SQLトリガー、および条件付きトリガーのWHEN句。
• USING句では、3つのバインド方向すべて(IN、IN OUTおよびOUT)のベクトルがサポートされます。
• FORALL句、RETURNING INTO句およびBULK COLLECT INTO句。
• 加算、減算または乗算の引数として。

VECTORデータ型は、現在SQLコレクションまたはオブジェクトではサポートされていません。

VECTOR型の変数は、静的SQL、動的SQLの形式で使用するか、DBMS_SQLを使用できます。これらのすべての場合に、ベクトルは列の型、バインドの型またはその両方として使用できます。列とバインドの型のいずれかがVECTORではない場合、そうでない方は文字列型である必要があります。VECTORデータ型を正式な引数として使用するPL/SQLファンクションは、現在、SQL SELECT文のWITH句ではサポートされません。

PL/SQLでは、最も内側のスコープに現れる識別子VECTORの定義が使用されます。ユーザー定義型がVECTORという名前で作成され、名前接頭辞なしで参照される場合、PL/SQLはローカル定義を使用して変数を解釈します。ローカル定義が存在しない場合、PL/SQLでは、名前が解決されるまで検索を外側のスコープに広げていきます(最終的にはパッケージのSTANDARD定義まで)。

ここでのトピック

• PL/SQLでサポートされているVECTOR操作
• VECTORデータ型のPL/SQLコードの例

関連項目:

• Oracle AI Vector Searchの詳細は、『Oracle Database AI Vector Searchユーザーズ・ガイド』を参照してください
• VECTORデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください

PL/SQLでサポートされているVECTOR操作

PL/SQLでは、VECTORデータ型で使用する次の基本操作がネイティブにサポートされます。

• VECTOR
• TO_VECTOR
• FROM_VECTOR (およびVECTOR_SERIALIZE)^脚注4
• TO_CHAR
• TO_CLOB
• VECTOR_DIMENSION_COUNT (およびVECTOR_DIMS)
• VECTOR_DIMENSION_FORMAT
• VECTOR_NORM
• VECTOR_DISTANCEは、次のメトリック・オプションでサポートされています(メトリックが指定されていない場合、コサイン距離がデフォルトです)。
  • COSINE
  • MANHATTAN
  • EUCLIDEAN
  • EUCLIDEAN_SQUARED
  • DOT
  • HAMMING
  • JACCARD
• 次のベクトル距離関数は、PL/SQLのスタンドアロン関数としてもネイティブにサポートされています。
  • COSINE_DISTANCE
  • L1_DISTANCE (マンハッタン距離)
  • L2_DISTANCE (ユークリッド距離)
  • INNER_PRODUCT
• さらに、対応するベクトル距離には次の短縮距離演算子を使用できます。
  • コサイン距離: <=>
  • ユークリッド距離: <->
  • ドット積: <#>

対応するベクトル距離メトリック、スタンドアロン・ファンクションおよび短縮距離演算子は、同等の結果になります。たとえば、VECTOR_DISTANCE(v1, v2, COSINE)はCOSINE_DISTANCE(v1, v2)と等しく、v1 <=> v2と等しくなります。

PL/SQLでベクトルを作成するには、VECTORまたはTO_VECTORを使用します。たとえば、次の変数の代入を見てください。

v VECTOR := VECTOR('[1, 2, 3]');
v VECTOR := TO_VECTOR('[1, 2, 3]');

変換が失敗した場合にデフォルト値を決定するために、明示的な変換のSQLで使用されるON CONVERSION ERROR句は、PL/SQLでサポートされません。かわりに、例外ハンドラのコード・ブロックを使用してデフォルト値を設定できます。

PL/SQLでメトリック・キーワードをネイティブに使用してVECTOR_DISTANCE関数を使用するか、前にリストした距離関数を使用するか、静的SQLからVECTOR_DISTANCEをコールできます。この距離は、BINARY_DOUBLEとして返されます。次に、有効な代入を示します。

dist := COSINE_DISTANCE(v1, v2);
dist := VECTOR_DISTANCE(v1, v2, COSINE);
dist := v1 <=> v2;
SELECT VECTOR_DISTANCE(v1, v2, COSINE) INTO dist;
SELECT v1 <=> v2 INTO dist;

加算、減算および乗算の算術演算子は、ベクトルのディメンション単位に適用できます。演算の両側は、ディメンションが一致するベクトルに評価される必要があり、BINARYまたはSPARSEベクトルにできません。結果のベクトルは、オペランドと同じ数のディメンションを持ち、形式は入力の形式に基づいて決定されます。演算の片側がベクトルでない場合は、値をベクトルに変換する試みが自動的に行われます。変換に失敗すると、エラーが発生します。

結果に使用される形式は、次の順にランク付けされます: 柔軟、FLOAT64、FLOAT32、INT8。たとえば、演算のいずれかの側に柔軟な形式がある場合、結果は柔軟になります。それ以外の場合、いずれかの側がFLOAT64という形式の場合は、結果がFLOAT64のようになります。

算術演算子の構文は次のとおりです:

• 加算: expr1 + expr2
• 減算: expr1 - expr2
• 乗算: expr1 * expr2

算術演算のいずれかの側がNULLの場合、結果はNULLになります。ディメンション・オーバーフローの場合、エラーが発生します。たとえば、VECTOR('[1, 127]', 2, INT8)をVECTOR('[1, 1]', 2, INT8)に追加すると、127+1=128がINT8形式をオーバーフローするため、エラーが発生します。

関連項目:

• ベクトルSQLファンクションの構文およびセマンティクスの詳細は、『Oracle Database SQL言語リファレンス』を参照してください
• ベクトルを使用した算術演算の実行の詳細は、Oracle Database AI Vector Searchユーザーズ・ガイドを参照してください

VECTORデータ型のPL/SQLコードの例

ここに示すPL/SQLコードの例は、VECTORデータ型の使用方法を示しています。

例4-14 PL/SQLでのVECTORデータ型の使用

この例の最初の部分では、ベクトルを選択してPL/SQLのベクトル変数に代入する方法を示しています。この場合、ベクトル列に%TYPEを使用しています。

DROP TABLE theVectorTable;
CREATE TABLE theVectorTable (embedding VECTOR(3, float32), id NUMBER);
INSERT INTO theVectorTable VALUES ('[1.11, 2.22, 3.33]', 1);
INSERT INTO theVectorTable VALUES ('[4.44, 5.55, 6.66]', 2);
INSERT INTO theVectorTable VALUES ('[7.77, 8.88, 9.99]', 3);

SET SERVEROUTPUT ON;

DECLARE
  v_embedding theVectorTable.embedding%TYPE;
BEGIN
  SELECT embedding INTO v_embedding FROM theVectorTable WHERE id=3;
  DBMS_OUTPUT.PUT_LINE('Embedding is ' || FROM_VECTOR(v_embedding));
END;
/

結果:

Embedding is [7.76999998E+000,8.88000011E+000,9.98999977E+000]

次の無名ブロックは、バルク・フェッチを行うカーソルを使用して、theVectorTableのベクトルおよびIDデータをtable%ROWTYPE索引表に取得します。

DECLARE
  TYPE vecTabT IS TABLE OF theVectorTable%ROWTYPE INDEX BY BINARY_INTEGER;
  v_vecTabT vecTabT;
  CURSOR c IS SELECT * FROM theVectorTable;
BEGIN
  OPEN c;
  FETCH c BULK COLLECT INTO v_vecTabT;
  CLOSE c;

  -- display the contents of the vector index table
  FOR i IN 1..v_vecTabT.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Embedding ID ' || v_vecTabT(i).id || ': ' ||
            FROM_VECTOR(v_vecTabT(i).embedding));
  END LOOP;
END;
/

結果:

Embedding ID 1: [1.11000001E+000,2.22000003E+000,3.32999992E+000]
Embedding ID 2: [4.44000006E+000,5.55000019E+000,6.65999985E+000]
Embedding ID 3: [7.76999998E+000,8.88000011E+000,9.98999977E+000]

例4-15 PL/SQLトリガーでのVECTORデータ型の使用

この例では、vecLogTableにベクトル値を挿入するtheVectorTableにBEFORE UPDATEトリガーを作成します。

DROP TABLE vecLogTable;
DROP SEQUENCE vecTrgSeq;
CREATE TABLE vecLogTable (embedding VECTOR(3, float32), 
        describe VARCHAR2(25), seq NUMBER);
CREATE SEQUENCE vecTrgSeq;

CREATE OR REPLACE TRIGGER vecTrg 
BEFORE UPDATE ON theVectorTable
FOR EACH ROW
BEGIN
  INSERT INTO vecLogTable VALUES (:old.embedding, 'OLD.VECTRG',
          vecTrgSeq.NEXTVAL);
  INSERT INTO vecLogTable VALUES (:new.embedding, 'NEW.VECTRG',
          vecTrgSeq.NEXTVAL);
END;
/

UPDATE theVectorTable SET embedding='[2.22, 4.44, 6.66]' WHERE id=2;
SELECT * FROM vecLogTable ORDER BY seq;

結果:

EMBEDDING                                          DESCRIBE         SEQ
-------------------------------------------------- ---------------- ---
[4.44000006E+000,5.55000019E+000,6.65999985E+000]  OLD.VECTRG         1
[2.22000003E+000,4.44000006E+000,6.65999985E+000]  NEW.VECTRG         2

例4-16 PL/SQLでのベクトル距離関数の使用

この例は、ベクトル距離関数のPL/SQLサポートを示しています。

DECLARE
  v1 VECTOR := TO_VECTOR('[1, 2, 3]');
  v2 VECTOR := TO_VECTOR('[4, 5, 6]');
  v3 VECTOR := TO_VECTOR('[1, 2, 0, 6]', *, BINARY);
  v4 VECTOR := TO_VECTOR('[0, 6, 0, 3]', *, BINARY);
  man_dist NUMBER;
  euc_dist NUMBER;
  cos_dist NUMBER;
  inn_dist NUMBER;
  ham_dist NUMBER;
  dot_dist NUMBER;
  jac_dist NUMBER;
BEGIN
  man_dist := L1_DISTANCE(v1, v2); --Manhattan Distance
  euc_dist := L2_DISTANCE(v1, v2); --Euclidean Distance
  cos_dist := COSINE_DISTANCE(v1, v2); --Cosine Distance
  inn_dist := INNER_PRODUCT(v1, v2); --Inner Product
    
  --The Hamming Distance has no standalone function in PL/SQL
  ham_dist := VECTOR_DISTANCE(v1, v2, HAMMING);

  --The Negative Inner (Dot) Product has no standalone function in PL/SQL
  dot_dist := VECTOR_DISTANCE(v1, v2, DOT);

  --The Jaccard Distance has no standalone function in PL/SQL
  jac_dist := VECTOR_DISTANCE(v3, v4, JACCARD);

  DBMS_OUTPUT.PUT_LINE('The Manhattan distance is: ' || man_dist);
  DBMS_OUTPUT.PUT_LINE('The Euclidean distance is: ' || euc_dist);
  DBMS_OUTPUT.PUT_LINE('The Cosine distance is: ' || cos_dist);
  DBMS_OUTPUT.PUT_LINE('The Inner Product is: ' || inn_dist);
  DBMS_OUTPUT.PUT_LINE('The Hamming distance is: ' || ham_dist);
  DBMS_OUTPUT.PUT_LINE('The Dot Product is: ' || dot_dist);
  DBMS_OUTPUT.PUT_LINE('The Jaccard Distance between the BINARY vectors v3 and v4 is: ' || jac_dist);
END;
/

結果:

The Manhattan distance is: 9
The Euclidean distance is: 5.1961524227066329
The Cosine distance is: .025368153802923787
The Inner Product is: 32
The Hamming distance is: 3
The Dot Product is: -32
The Jaccard Distance between the BINARY vectors v3 and v4 is: .66666666666666674

例4-17短縮距離演算子の使用

PL/SQLではベクトルを使用した暗黙的な変換がサポートされていないため、変数代入の前または同じ行にベクトルを作成する必要があります。これは、PL/SQLの他の距離関数と同じ動作です。

DECLARE
  v1 VECTOR := VECTOR('[1, 2, 3]');
  v2 VECTOR := VECTOR('[4, 5, 6]');
  cos_dist BINARY_DOUBLE;
  euc_dist BINARY_DOUBLE;
  dot_dist BINARY_DOUBLE;
BEGIN
  cos_dist := v1 <=> v2;
  euc_dist := v1 <-> v2;
  dot_dist := v1 <#> v2;

  DBMS_OUTPUT.PUT_LINE(cos_dist);
  DBMS_OUTPUT.PUT_LINE(euc_dist);
  DBMS_OUTPUT.PUT_LINE(dot_dist);
END;
/

結果:

2.5368153802923787E-002
5.1961524227066329E+000
-3.2E+001

例4-18 ベクトルに対する算術演算の実行

DECLARE
  v1 VECTOR := VECTOR('[10, 20, 30]', 3, INT8);
  v2 VECTOR := VECTOR('[6, 4, 2]', 3, INT8);
BEGIN
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(v1 + v2));
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(v1 - v2));
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(v1 * v2));
END;  
/

結果:

[16,24,32]
[4,16,28]
[60,80,60]

例4-19 PL/SQLでのDENSEおよびSPARSEベクトルの宣言

DECLARE
  vs1 VECTOR(*, *, SPARSE) := VECTOR('[10, [0, 3, 4, 6, 7], [1.9, 4, 7.2, 30, 60]]', *, *, SPARSE);
  vs2 VECTOR(*, *, SPARSE) := VECTOR('[10, [1, 3, 4, 8, 9], [4.5, 7.6, 4, 8.1, 5]]', *, *, SPARSE);

  vd1 VECTOR(*, *, DENSE) := VECTOR('[1.9, 0, 0, 4, 7.2, 0, 30, 60, 0, 0]', *, *, DENSE);
  vd2 VECTOR(*, *, DENSE) := VECTOR('[0, 4.5, 0, 7.6, 4, 0, 0, 0, 8.1, 5]', *, *, DENSE);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Vector Distance Sparse: ' || TRUNC(VECTOR_DISTANCE(vs1, vs2), 5));
  DBMS_OUTPUT.PUT_LINE('Vector Distance Dense:  ' || TRUNC(VECTOR_DISTANCE(vd1, vd2), 5));
END;
/

結果:

Vector Distance Sparse: .93556
Vector Distance Dense:  .93556

CHARおよびVARCHAR2変数

ここでのトピック

• 長すぎる値の代入または挿入

• マルチバイト文字の変数の宣言

• CHARとVARCHAR2のデータ型の違い

長すぎる値の代入または挿入

文字変数に変数の最大サイズより長い値を代入すると、エラーが発生します。たとえば:

DECLARE
  c VARCHAR2(3 CHAR);
BEGIN
  c := 'abc  ';
END;
/

結果:

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: value or conversion error: character string buffer too small
ORA-06512: at line 4

同様に、列に挿入した文字変数の値が、定義されている列幅より長い場合も、エラーが発生します。たとえば:

DROP TABLE t;
CREATE TABLE t (c CHAR(3 CHAR));
 
DECLARE
  s VARCHAR2(5 CHAR) := 'abc  ';
BEGIN
  INSERT INTO t(c) VALUES(s);
END;
/

結果:

BEGIN
*
ERROR at line 1:
ORA-12899: value too large for column "HR"."T"."C" (actual: 5, maximum: 3)
ORA-06512: at line 4

文字値を変数に代入したり、列に挿入する前に、その文字値から後続する空白を削除するには、RTRIMファンクション(『Oracle Database SQL言語リファレンス』を参照)を使用します。たとえば:

DECLARE
  c VARCHAR2(3 CHAR);
BEGIN
  c := RTRIM('abc  ');
  INSERT INTO t(c) VALUES(RTRIM('abc  '));
END;
/

結果:

PL/SQL procedure successfully completed.

マルチバイト文字の変数の宣言

CHARまたはVARCHAR2変数の最大サイズは、最大サイズを文字数で指定するかバイト数で指定するかに関係なく、32,767バイトです。変数の最大文字数は、文字セットのタイプによって、場合によっては文字自体によって異なります。

文字セットのタイプ	最大文字数
シングルバイト文字セット	32,767
nバイトの固定幅マルチバイト文字セット(AL16UTF16など)	FLOOR(32,767/n)
文字幅が1とnバイトの間である、nバイトの可変幅マルチバイト文字セット(JA16SJISやAL32UTF8など)	文字自体によって異なり、32,767(1バイト文字のみを含む文字列の場合)からFLOOR(32,767/n)(nバイト文字のみを含む文字列の場合)を指定できます。

任意のマルチバイト文字セットでn個の文字を常に保持できるように、CHARまたはVARCHAR2変数を宣言する場合は、CHAR(n CHAR)またはVARCHAR2(n CHAR)を使用して、文字数の長さを宣言します(nはFLOOR(32767/4) = 8191を超えることはできません)。

関連項目:

Oracle Databaseの文字セットのサポートの詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。

CHARとVARCHAR2のデータ型の違い

CHARとVARCHAR2のデータ型は、次の点で異なっています。

• 事前定義のサブタイプ

• 空白埋めの動作方法

• 値の比較

事前定義のサブタイプ

CHARデータ型には、PL/SQLとSQLの両方にCHARACTERというサブタイプが1つ事前定義されています。

VARCHAR2データ型には、PL/SQLとSQLの両方にVARCHARというサブタイプが1つ事前定義されており、PL/SQLには、STRINGというサブタイプがもう1つ事前定義されています。

各サブタイプの値の範囲はそのベース型と同じです。

ノート:

PL/SQLの今後のリリースでのVARCHARは、新しいSQL標準に従うために別のデータ型になり、VARCHAR2と同義ではなくなる可能性があります。

空白埋めの動作方法

これは、空白埋めを使用する場合のCHARとVARCHAR2での相違点および考慮事項を説明しています。

次の状況について考えてみます:

• 変数に代入した値がこの変数の最大サイズより短い。

• 列に挿入した値が、定義されている列幅より短い。

• 列から取り出して変数に入れた値が、この変数の最大サイズより短い。

受信者のデータ型がCHARの場合、PL/SQLは最大サイズになるまで値を空白で埋めます。元の値の後続する空白に関する情報は失われます。

受信者のデータ型がVARCHAR2の場合、PL/SQLは値の空白埋めも、後続する空白の削除もしません。文字値はそのまま代入され、情報は失われません。

例4-20 CHARおよびVARCHAR2の空白埋めの相違点

この例では、CHAR変数とVARCHAR2変数は、どちらも最大サイズが10文字です。それぞれの変数に、1つの空白が後続している5文字の値を代入します。CHAR変数に代入した値は空白が埋められて10文字になり、結果の値に含まれる後続の空白6つのうち、1つは元の値に含まれていたものだとは見分けられません。VARCHAR2変数に代入された値は変化しないため、後続の空白が1つあることを確認できます。

DECLARE
  first_name  CHAR(10 CHAR);
  last_name   VARCHAR2(10 CHAR);
BEGIN
  first_name := 'John ';
  last_name  := 'Chen ';
 
  DBMS_OUTPUT.PUT_LINE('*' || first_name || '*');
  DBMS_OUTPUT.PUT_LINE('*' || last_name || '*');
END;
/

結果:

*John      *
*Chen *

値の比較

SQLの文字値の比較ルールが、PL/SQLの文字変数に適用されます。

比較する値の1つまたは両方のデータ型がVARCHAR2またはNVARCHAR2の場合は、非空白埋め比較セマンティクスが適用されますが、それ以外の場合は、空白埋めセマンティクスが適用されます。詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

LONGおよびLONG RAW変数

ノート:

Oracleは、既存アプリケーションとの下位互換性のためにのみ、LONGおよびLONG RAWデータ型をサポートしています。新しいアプリケーションでは次のようにしてください:

• LONGのかわりに、VARCHAR2(32760)、BLOB、CLOBまたはNCLOBを使用する。

• LONG RAWのかわりに、RAW(32760)またはBLOBを使用する。

LONGデータ型からLOBデータ型に列を移行する方法の詳細は、Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイドを参照してください。

LONG列には、任意のLONG値を挿入できます。LONG RAW列には、任意のLONG RAW値を挿入できます。LONGまたはLONG RAW列からは、32760バイトを超える長さの値を取り出して、LONGまたはLONG RAW変数に入れることはできません。

LONG列には、任意のCHARまたはVARCHAR2値を挿入できます。LONG列からは、32,767バイトを超える長さの値を取り出してCHAR変数またはVARCHAR2変数に入れることはできません。

LONG RAW列には、任意のRAW値を挿入できます。LONG RAW列からは、32767バイトを超える長さの値を取り出してRAW変数に入れることはできません。

関連項目:

トリガー内のLONGデータ型およびLONG RAWデータ型の制約については、「トリガーのLONGおよびLONG RAWデータ型の制約」を参照してください

ROWIDおよびUROWID変数

ROWIDを取り出してROWID変数に入れる場合は、バイナリ値を文字値に変換するROWIDTOCHARファンクションを使用します。このファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

ROWID変数の値をROWIDに変換するには、CHARTOROWIDファンクション(『Oracle Database SQL言語リファレンス』を参照)を使用します。値が有効なROWIDを表していない場合は、PL/SQLにより事前定義の例外SYS_INVALID_ROWIDが呼び出されます。

ROWIDを取り出してUROWID変数に入れる場合、またはUROWID変数の値をROWIDに変換する場合は、代入文を使用します。変換は暗黙的に実行されます。

ノート:

• UROWIDは論理ROWIDと物理ROWIDの両方と互換性があるため、ROWIDより汎用性の高いデータ型です。

• ハイブリッド列圧縮(HCC)で圧縮された表の行を更新すると、行のROWIDが変更されます。特定のOracleストレージ・システムの機能であるHCCの詳細は、『Oracle Database概要』を参照してください。

関連項目:

DBMS_ROWIDパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。このパッケージのサブプログラムを使用すると、ROWID値に関する情報を作成したり戻すことができます(ただし、UROWID値に関してはできません)。

---

脚注の説明

脚注4: オプションのRETURNING句は、TO_VECTORおよびVECTOR_SERIALIZEではサポートされていません。