4 PL/SQLのデータ型
PL/SQLの定数、変数、パラメータ、ファンクションの戻り値はすべて、記憶形式および有効な値と演算を決定するデータ型を持っています。
この章では、スカラー・データ型という、内部コンポーネントのない値を格納するデータ型について説明します。
スカラー・データ型にはサブタイプを持たせることができます。サブタイプとは、別のデータ型のサブセットとなるデータ型のことで、その別のデータ型は、そのサブタイプのベース型となります。サブタイプには、そのベース型で有効な演算と同じ演算が含まれています。データ型とそのサブタイプでデータ型ファミリが構成されます。
PL/SQLには、多くの型やサブタイプがパッケージSTANDARD
に事前定義されています。また、PL/SQLを使用して、独自のサブタイプを定義することもできます。
PL/SQLのスカラー・データ型は次のとおりです。
-
SQLデータ型
-
PLS_INTEGER
-
BINARY_INTEGER
-
REF
CURSOR
-
ユーザー定義のサブタイプ
ここでのトピック
関連項目:
-
コンポジット・データ型の詳細は、「PL/SQLのコレクションおよびレコード」を参照してください
-
REF
CURSOR
の詳細は、「カーソル変数」を参照してください -
スキーマ・レベルのユーザー定義データ型の作成の詳細は、「CREATE TYPE文」を参照してください
-
データ型ファミリごとにグループ化された、事前定義のPL/SQLデータ型とサブタイプは、「PL/SQLの事前定義のデータ型」を参照してください
4.1 SQLデータ型
PL/SQLデータ型にはSQLデータ型が含まれます。
SQLデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください(記載されているデータ型およびサブタイプ、データ型の比較のルール、データ変換、リテラル、および書式モデルに関する情報はすべて、次の項目を除き、SQLとPL/SQLの両方に適用されます)。
SQLとは異なり、PL/SQLでは変数を宣言できます。変数には次のトピックが適用されます。
4.1.1 最大サイズの相違
表4-1にリストしたSQLのデータ型は、PL/SQLとSQLで最大サイズが異なります。
表4-1 PL/SQLとSQLで最大サイズが異なるデータ型
データ型 | PL/SQLでの最大サイズ | SQLでの最大サイズ |
---|---|---|
|
32,767バイト |
2,000バイト |
|
32,767バイト |
2,000バイト |
|
32,767バイト |
2,000バイト脚注2 |
|
32,767バイト |
4,000バイト脚注2 |
|
32,767バイト |
4,000バイト脚注2 |
|
32,760バイト |
2ギガバイト(GB) - 1 |
|
32,760バイト |
2GB |
|
128テラバイト(TB) |
(4GB - 1) * |
|
128TB |
(4GB - 1) * |
|
128TB |
(4GB - 1) * |
脚注1
このデータ型の値の最大サイズをPL/SQLで指定する場合は、定数または変数ではなく、1から32,767の間の値を持つ整数リテラルを使用します。
脚注2
このサイズの違いをなくすには、『Oracle Database SQL言語リファレンス』の手順に従ってください。
脚注3
既存のアプリケーションとの下位互換性を保つためにのみサポートされています。
4.1.2 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の定数
定数 | 説明 |
---|---|
|
条件 |
|
単精度の正の無限大 |
|
最大正規 |
|
最小正規 |
|
最大非正規 |
|
最小非正規 |
|
条件 |
|
倍精度の正の無限大 |
|
最大正規 |
|
最小正規 |
|
最大非正規 |
|
最小非正規 |
(*) この定数は、SQLでも事前定義されています。
4.1.3 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'
を使用する方が、パフォーマンスの向上幅が大きくなります(詳細は、「ハードウェア算術を使用するデータ型の使用」を参照してください)。
4.1.4 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
4.1.5 JSONデータ型
JSON
データ型インスタンスは、PL/SQLサブプログラムで使用できます。PL/SQL JSON
データ型は、ネストしたJSON
値への高速アクセスのために、バイナリ形式でデータベースに格納されます。
JSONデータ型とそのインスタンスは、次のようなSQLデータ型が使用可能なほとんどの場所で使用できます。
-
表やビューDDLの列の型として
-
PL/SQLサブプログラムのパラメータの型として
-
SQL/JSON関数または条件が許可される式。
ここでのトピック
関連項目:
-
JSONスキーマの詳細は、json-schema.orgを参照してください
-
JSONデータを使用してPL/SQLを使用する方法の詳細は、Oracle Database JSON開発者ガイドを参照してください
-
JSON用のPL/SQLオブジェクト・タイプの詳細は、『Oracle Database JSON開発者ガイド』を参照してください
4.1.5.1 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_value
組込み関数の詳細は、『Oracle Database JSON開発者ガイド』を参照してください
4.1.5.1.1 JSONオブジェクトとPL/SQLレコード
PL/SQLレコードは、名前/値のペアを使用してデータを保持し、JSONコンストラクタおよび組込み関数json_value
を介して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}
4.1.5.1.2 JSONオブジェクトおよびIndex by PLS_INTEGERおよびネストした表のコレクション
Index by PLS_INTEGER
コレクションおよびネストした表コレクションは、それぞれ組込みのjson_value
関数およびJSONコンストラクタを使用して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 }
4.1.5.1.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コレクション
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]
4.1.5.1.4 JSONオブジェクトと連想配列
連想配列は、それぞれJSONコンストラクタと組込み関数json_value
を使用して、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"}
4.1.6 CHARおよびVARCHAR2変数
4.1.6.1 長すぎる値の代入または挿入
文字変数に変数の最大サイズより長い値を代入すると、エラーが発生します。たとえば:
DECLARE c VARCHAR2(3 CHAR); BEGIN c := 'abc '; END; /
結果:
DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value 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.
4.1.6.2 マルチバイト文字の変数の宣言
CHAR
またはVARCHAR2
変数の最大サイズは、最大サイズを文字数で指定するかバイト数で指定するかに関係なく、32,767バイトです。変数の最大文字数は、文字セットのタイプによって、場合によっては文字自体によって異なります。
文字セットのタイプ | 最大文字数 |
---|---|
シングルバイト文字セット |
32,767 |
nバイトの固定幅マルチバイト文字セット(AL16UTF16など) |
|
文字幅が1とnバイトの間である、nバイトの可変幅マルチバイト文字セット(JA16SJISやAL32UTF8など) |
文字自体によって異なり、32,767(1バイト文字のみを含む文字列の場合)から |
任意のマルチバイト文字セットでn個の文字を常に保持できるように、CHAR
またはVARCHAR2
変数を宣言する場合は、CHAR(
n
CHAR)
またはVARCHAR2(
n
CHAR)
を使用して、文字数の長さを宣言します(nはFLOOR(32767/4)
= 8191を超えることはできません)。
関連項目:
Oracle Databaseの文字セットのサポートの詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。
4.1.6.3 CHARとVARCHAR2のデータ型の違い
CHAR
とVARCHAR2
のデータ型は、次の点で異なっています。
4.1.6.3.1 事前定義のサブタイプ
CHAR
データ型には、PL/SQLとSQLの両方にCHARACTER
というサブタイプが1つ事前定義されています。
VARCHAR2
データ型には、PL/SQLとSQLの両方にVARCHAR
というサブタイプが1つ事前定義されており、PL/SQLには、STRING
というサブタイプがもう1つ事前定義されています。
各サブタイプの値の範囲はそのベース型と同じです。
ノート:
PL/SQLの今後のリリースでのVARCHAR
は、新しいSQL標準に従うために別のデータ型になり、VARCHAR2
と同義ではなくなる可能性があります。
4.1.6.3.2 空白埋めの動作方法
これは、空白埋めを使用する場合のCHARとVARCHAR2での相違点および考慮事項を説明しています。
次の状況について考えてみます。
-
変数に代入した値がこの変数の最大サイズより短い。
-
列に挿入した値が、定義されている列幅より短い。
-
列から取り出して変数に入れた値が、この変数の最大サイズより短い。
受信者のデータ型がCHAR
の場合、PL/SQLは最大サイズになるまで値を空白で埋めます。元の値の後続する空白に関する情報は失われます。
受信者のデータ型がVARCHAR2
の場合、PL/SQLは値の空白埋めも、後続する空白の削除もしません。文字値はそのまま代入され、情報は失われません。
例4-14 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 *
4.1.6.3.3 値の比較
SQLの文字値の比較ルールが、PL/SQLの文字変数に適用されます。
比較する値の1つまたは両方のデータ型がVARCHAR2
またはNVARCHAR2
の場合は、非空白埋め比較セマンティクスが適用されますが、それ以外の場合は、空白埋めセマンティクスが適用されます。詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
4.1.7 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データ型の制約」を参照してください
4.1.8 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概要』を参照してください。
4.2 PLS_INTEGERおよびBINARY_INTEGERデータ型
PL/SQLのデータ型であるPLS_INTEGER
とBINARY_INTEGER
は同じです。
わかりやすくするために、このマニュアルでは、PLS_INTEGER
とBINARY_INTEGER
の両方を表すためにPLS_INTEGER
を使用します。
PLS_INTEGER
データ型は、32ビットで表される-2147483648から2147483647の範囲の符号付き整数を格納します。
PLS_INTEGER
データ型には、NUMBER
データ型およびNUMBER
サブタイプと比べて、次のようなメリットがあります。
-
PLS_INTEGER
値の方が、必要な記憶域が少なくなります。 -
PLS_INTEGER
演算はハードウェア算術計算を使用するため、ライブラリ算術計算を使用するNUMBER
演算より処理速度が速くなります。
効率のために、PLS_INTEGERの範囲内でのすべての計算にPLS_INTEGER
値を使用してください。
ここでのトピック
4.2.1 PLS_INTEGERのオーバーフローの回避
PLS_INTEGER
範囲をオーバーフローする2つのPLS_INTEGER
値を使用した計算では、オーバーフロー例外が発生します。
PLS_INTEGER
の範囲外の計算には、NUMBER
データ型の事前定義のサブタイプであるINTEGER
を使用します。
例4-15 オーバーフロー例外が発生するPLS_INTEGERの計算
この例では、2つのPLS_INTEGER
値を使用する計算がPLS_INTEGER
の範囲をオーバーフローした場合は、結果をNUMBER
データ型に代入しても、オーバーフロー例外が発生することを示します。
DECLARE p1 PLS_INTEGER := 2147483647; p2 PLS_INTEGER := 1; n NUMBER; BEGIN n := p1 + p2; END; /
結果:
DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 6
例4-16 例4-15のオーバーフローの防止
この例では、PLS_INTEGER
範囲外の計算での事前定義のサブタイプINTEGER
の正しい使用を示しています。
DECLARE
p1 PLS_INTEGER := 2147483647;
p2 INTEGER := 1;
n NUMBER;
BEGIN
n := p1 + p2;
END;
/
結果:
PL/SQL procedure successfully completed.
4.2.2 PLS_INTEGERの事前定義のサブタイプ
このサマリーは、PLS_INTEGER
データ型の事前定義済のサブタイプ、およびそれらが格納するデータを示します。
表4-3 PLS_INTEGERデータ型の事前定義のサブタイプ
データ型 | データの説明 |
---|---|
|
負でない |
|
|
|
正の |
|
|
|
-1、0または1の |
|
|
PLS_INTEGER
およびそのサブタイプは、次のデータ型に暗黙的に変換できます。
-
CHAR
-
VARCHAR2
-
NUMBER
-
LONG
LONG
を除く前述のすべてのデータ型、およびすべてのPLS_INTEGER
サブタイプは、PLS_INTEGER
に暗黙的に変換できます。
PLS_INTEGER
値は、サブタイプの制約に違反していない場合のみ、PLS_INTEGER
サブタイプに暗黙的に変換できます。
関連項目:
-
NOT
NULL
制約の詳細は、「NOT NULL制約」を参照してください -
SIMPLE_INTEGER
の詳細は、「PLS_INTEGERのSIMPLE_INTEGERサブタイプ」を参照してください
例4-17 SIMPLE_INTEGERサブタイプの制約違反
この例は、PLS_INTEGER
値のNULL
をSIMPLE_INTEGER
サブタイプにキャストすると例外が発生することを示しています。
DECLARE a SIMPLE_INTEGER := 1; b PLS_INTEGER := NULL; BEGIN a := b; END; /
結果:
DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 5
4.2.3 PLS_INTEGERのSIMPLE_INTEGERサブタイプ
SIMPLE_INTEGER
は、PLS_INTEGER
データ型の事前定義済のサブタイプです。
SIMPLE_INTEGER
はPLS_INTEGER
と同じ範囲を持ち、NOT
NULL
制約があります。PLS_INTEGER
との大きな違いはオーバーフローの方法です。
変数の値がNULL
になることがなく、オーバーフロー・チェックも不要であるとわかっている場合は、PLS_INTEGER
ではなくSIMPLE_INTEGER
として値を宣言します。SIMPLE_INTEGER
の場合は、NULLかどうかのチェックおよびオーバーフロー・チェックのためのオーバーヘッドが発生しないため、PLS_INTEGER
よりもパフォーマンスが大幅に向上します。
ここでのトピック
4.2.3.1 SIMPLE_INTEGERのオーバーフローの方法
式に含まれるすべてのオペランドのデータ型がSIMPLE_INTEGER
の場合にのみ、PL/SQLで2の補数算術が使用され、オーバーフローが無視されます。
オーバーフローが無視されるため、値を正から負、または負から正にラップできます。たとえば:
230 + 230 = 0x40000000 + 0x40000000 = 0x80000000 = -231
-231 + -231 = 0x80000000 + 0x80000000 = 0x00000000 = 0
たとえば、次のブロックはエラーなしで実行されます。
DECLARE
n SIMPLE_INTEGER := 2147483645;
BEGIN
FOR j IN 1..4 LOOP
n := n + 1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));
END LOOP;
FOR j IN 1..4 LOOP
n := n - 1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));
END LOOP;
END;
/
結果:
+2147483646 +2147483647 -2147483648 -2147483647 -2147483648 +2147483647 +2147483646 +2147483645 PL/SQL procedure successfully completed.
4.2.3.2 SIMPLE_INTEGERと他のオペランドの両方を使用する式
SIMPLE_INTEGER
と他のオペランドの両方が式に含まれている場合は、PL/SQLによりSIMPLE_INTEGER
値がPLS_INTEGER
NOT
NULL
に暗黙的に変換されます。
一部の最適化が抑制されてパフォーマンスに悪影響を与えるような状態でSIMPLE_INTEGER
値と他の値が混在している場合は、PL/SQLコンパイラから警告が発行されます。
4.3 ユーザー定義のPL/SQLサブタイプ
PL/SQLではユーザー独自のサブタイプを定義できます。
ベース型は、CHAR
、DATE
またはRECORD
などのスカラーまたはユーザー定義のPL/SQLデータ型指定子です(事前に定義したユーザー定義のサブタイプが含まれます)。
ノート:
このトピック内の情報は、ユーザー定義のサブタイプおよび「PL/SQLの事前定義のデータ型」にリストされている事前定義のサブタイプの両方に適用されます。
サブタイプの役割は次のとおりです。
-
ANSI/ISOデータ型との互換性の提供
-
その型のデータ項目の使用意図の提示
-
範囲外の値の検出
ここでのトピック
4.3.1 無制約のサブタイプ
無制約のサブタイプには、そのベース型と同じ値セットが含まれているため、ベース型の別名にすぎません。
したがって、同じベース型の無制約のサブタイプは互換性があり、そのベース型との互換性もあります。データ型の変換は発生しません。
無制約のサブタイプを定義するには、次の構文を使用します。
SUBTYPE subtype_name IS base_type
subtype_name
およびbase_type
の詳細は、subtypeを参照してください。
無制約のサブタイプの例を示します。これは、ANSIとの互換性のためにPL/SQLで事前定義されているものです。
SUBTYPE "DOUBLE PRECISION" IS FLOAT
例4-18 使用意図を示すユーザー定義の無制約のサブタイプ
この例では、制約なしのサブタイプであるBalance
およびCounter
型それぞれのデータ項目の使用意図を示しています。
DECLARE SUBTYPE Balance IS NUMBER; checking_account Balance(6,2); savings_account Balance(8,2); certificate_of_deposit Balance(8,2); max_insured CONSTANT Balance(8,2) := 250000.00; SUBTYPE Counter IS NATURAL; accounts Counter := 1; deposits Counter := 0; withdrawals Counter := 0; overdrafts Counter := 0; PROCEDURE deposit ( account IN OUT Balance, amount IN Balance ) IS BEGIN account := account + amount; deposits := deposits + 1; END; BEGIN NULL; END; /
4.3.2 制約付きサブタイプ
制約付きサブタイプには、そのベース型の値のサブセットのみが含まれています。
サイズ、精度と位取り、または値の範囲を指定できるベース型の場合は、そのサブタイプにもこれらを指定できます。次に、サブタイプを定義する構文を示します。
SUBTYPE subtype_name IS base_type { precision [, scale ] | RANGE low_value .. high_value } [ NOT NULL ]
この構文を使用しない場合、サブタイプに指定されるのはNOT
NULL
制約のみです。
SUBTYPE subtype_name IS base_type [ NOT NULL ]
ノート:
値の範囲を指定できるベース型は、PLS_INTEGER
とそのサブタイプ(事前定義とユーザー定義の両方)のみです。
制約付きサブタイプは暗黙的にベース型に変換されますが、ベース型を制約付きサブタイプに暗黙的に変換できるのは、値がサブタイプの制約に違反していない場合のみです。
制約付きサブタイプは、変換元の値が変換先のサブタイプの制約に違反していない場合のみ、同じベース型を使用する別の制約付きサブタイプに暗黙的に変換できます。
例4-19 範囲外の値を検出するユーザー定義の制約付きサブタイプ
この例では、制約付きサブタイプBalance
を使用して範囲外の値を検出します。
DECLARE SUBTYPE Balance IS NUMBER(8,2); checking_account Balance; savings_account Balance; BEGIN checking_account := 2000.00; savings_account := 1000000.00; END; /
結果:
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 9
例4-20 同じベース型を使用する制約付きサブタイプ間の暗黙的な変換
この例では、3つの制約付きサブタイプに同じベース型が使用されています。最初の2つのサブタイプは、3番目のサブタイプに暗黙的に変換できますが、相互には変換できません。
DECLARE SUBTYPE Digit IS PLS_INTEGER RANGE 0..9; SUBTYPE Double_digit IS PLS_INTEGER RANGE 10..99; SUBTYPE Under_100 IS PLS_INTEGER RANGE 0..99; d Digit := 4; dd Double_digit := 35; u Under_100; BEGIN u := d; -- Succeeds; Under_100 range includes Digit range u := dd; -- Succeeds; Under_100 range includes Double_digit range dd := d; -- Raises error; Double_digit range does not include Digit range END; /
結果:
DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 12
4.3.3 同じデータ型ファミリ内のベース型を使用したサブタイプ
同じデータ型ファミリ内の異なるベース型が2つのサブタイプに使用されている場合は、変換元の値が変換先のサブタイプの制約に違反していない場合のみ、一方から他方へ暗黙的に変換できます。
データ型ファミリでグループ化された事前定義のPL/SQLのデータ型およびサブタイプは、「PL/SQLの事前定義のデータ型」を参照してください。
例4-21 同じファミリ内のベース型を使用するサブタイプ間の暗黙的な変換
この例では、サブタイプのWord
とText
に、同じデータ型ファミリ内の異なるベース型が使用されています。最初の代入文では、Word
の値が暗黙的にText
に変換されます。2番目の代入文では、Text
の値が暗黙的にWord
に変換されます。3番目の代入文では、Text
の値が長すぎるため、値を暗黙的にWord
に変換することができません。
DECLARE SUBTYPE Word IS CHAR(6); SUBTYPE Text IS VARCHAR2(15); verb Word := 'run'; sentence1 Text; sentence2 Text := 'Hurry!'; sentence3 Text := 'See Tom run.'; BEGIN sentence1 := verb; -- 3-character value, 15-character limit verb := sentence2; -- 6-character value, 6-character limit verb := sentence3; -- 12-character value, 6-character limit END; /
結果:
DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 13