16 SQL問合せファンクションおよび条件で使用される句
RETURNING
、ラッパー、エラーおよび空白フィールドの句について説明します。これらは、SQLファンクションおよび条件のjson_value
、json_query
、json_table
、json_serialize
、json_mergepatch
、is json
、is not json
、json_exists
およびjson_equal
の1つ以上で使用されます。
- SQL問合せファンクションで使用されるRETURNING句
SQLファンクションjson_value
、json_query
、json_serialize
およびjson_mergepatch
は、オプションでRETURNING
句を受け入れます。この句は、ファンクションから返される値のデータ型を指定します。この句についてと、デフォルト動作(RETURNING
句なし)について説明します。 - SQL/JSON問合せファンクションJSON_QUERYおよびJSON_TABLEで使用されるラッパー句
SQL/JSON問合せファンクションのjson_query
およびjson_table
は、オプションのラッパー句を受け入れます。これには、json_query
によって返される値、またはjson_table
列のデータに使用される値の形式を指定します。この句についてと、デフォルト動作(ラッパー句なし)について説明します。例を示します。 - SQL問合せファンクションおよび条件で使用されるエラー句
一部のSQL問合せファンクションおよび条件は、オプションでエラー句を受け入れます。これにより、ファンクションまたは条件によって発生した実行時エラーの処理方法を指定します。この句についてと、デフォルト動作(エラー句なし)についてまとめます。 - SQL/JSON問合せファンクションで使用される空白フィールド句
SQL/JSON問合せファンクションのjson_value
、json_query
およびjson_table
は、オプションでON EMPTY
句を受け入れます。これは、対象となるJSONフィールドが問合せデータ内にない場合の処理を指定します。この句についてと、デフォルト動作(ON EMPTY
句なし)について説明します。 - JSON_VALUEのON MISMATCH句
RETURNING
句でユーザー定義のオブジェクト型またはコレクション型インスタンスを指定する場合、ファンクションjson_value
には、オプションでON MISMATCH
句を指定できます。この句では、対象のJSON値が指定したSQL戻り値と一致しない場合に使用する処理を指定します。この句についてと、デフォルト動作(ON MISMATCH
句なし)について説明します。
親トピック: JSONデータの問合せ
16.1 SQL問合せファンクションで使用されるRETURNING句
SQLファンクションjson_value
、json_query
、json_serialize
およびjson_mergepatch
は、オプションでRETURNING
句を受け入れます。この句は、ファンクションから返される値のデータ型を指定します。この句についてと、デフォルト動作(RETURNING
句なし)について説明します。
json_value
の場合、VARCHAR2
、NUMBER
、DATE
、TIMESTAMP
、TIMESTAMP WITH TIME ZONE
、SDO_GEOMETRY
およびCLOB
の事前定義済SQLデータ型から任意のものをRETURNING
句で使用できます。ユーザー定義のオブジェクト型またはコレクション型を使用することもできます。
ノート:
Oracle SQLデータ型DATE
のインスタンスには、時間コンポーネントが含まれます。JSONデータでは、ISO 8601日時値を表す文字列を使用できます。つまり、時間コンポーネントを使用できます。
デフォルトで、RETURNING DATE
を含むjson_value
は、時間コンポーネントがゼロ(ゼロ時間、分および秒)のSQL DATE
値を返します。デフォルトでは、問い合せたJSONスカラー値の時間コンポーネントは、返されたSQL DATE
インスタンスで切り捨てられます。ただし、時間の切捨てが行われる前に、ISO 8601の日時文字列で表される値にタイムゾーン・コンポーネントがある場合は、値は最初にUTCに変換され、タイムゾーン情報が考慮されます。
RETURNING DATE PRESERVE TIME
を使用すると、このデフォルトの切捨て動作をオーバーライドし、問い合せたJSONスカラー値の時間コンポーネント(存在する場合)を保持できます。(RETURNING DATE TRUNCATE TIME
の使用は、デフォルトの動作であるRETURNING DATE
とまったく同じ効果があります。)
(同じ考慮事項が、TRUNCATE TIME
に対応する項目メソッドdate()
と、PRESERVE TIME
に対応するdateWithTime()
に適用されます。)
json_query
、json_serialize
およびjson_mergepatch
の場合、VARCHAR2
、CLOB
またはBLOB
を使用できます。BLOB
の結果はAL32UTF8文字セットです。json_serialize
によって返されるデータ型に関係なく、返されるデータはテキストのJSONデータを表します。
必要に応じて、VARCHAR2
の長さ(デフォルト: 4000
)、およびNUMBER
の精度とスケールを指定できます。
デフォルトの動作(RETURNING
句なし)では、VARCHAR2(4000)
を使用します。
データ型SDO_GEOMETRY
は、Oracle Spatial and Graphデータ向けです。特に、これは、json_value
をGeoJSONデータで使用できることを意味します。このデータは、JSONで地理データをエンコーディングするための形式です。
また、RETURNING
句は、PRETTY
およびASCII
の2つのオプションのキーワードも受け入れます。両方を使用する場合は、PRETTY
がASCII
の前にくる必要があります。キーワードPRETTY
はjson_value
には指定できません。
キーワードPRETTY
を使用すると、改行文字の挿入およびインデントにより、戻されたデータがわかりやすいように出力されます。デフォルトの動作では、わかりやすいようには出力されません。
キーワードASCII
を使用すると、標準のASCII Unicodeエスケープ・シーケンスを使用することにより、戻されるデータ内の非ASCII Unicode文字がすべて自動的にエスケープされます。デフォルトの動作では、非ASCII Unicode文字はエスケープされません。
ヒント:
パス式として$
のみを使用することにより、コンテキスト項目全体をわかりやすいように出力できます。
RETURNING
句でVARCHAR2
を指定した場合、値のスカラーは次のように表されます。
-
ブール値は、小文字の文字列
"true"
および"false"
で表されます。 -
null
値は、SQLNULL
で表されます。 -
JSONの数値は、正準な形式で表されます。このため、JSONの数値では、出力の文字列がテキストの入力データの表現と異なる可能性があります。正準な形式で表された場合、数値は次のようになります。
-
SQL
NUMBER
の精度と範囲の制限の対象となる場合があります。 -
SQL
NUMBER
の制限事項の対象とならない場合には、次のようになります。-
精度は40桁に制限されます。
-
オプションの指数は、9桁と1つの符号(
+
または-
)に制限されます。 -
使用できる符号(
-
、+
)、小数点(.
)および指数のインジケータ(E
)を含めて、テキスト全体が48文字に制限されます。
-
正準な形式のJSONの数値は、次のようなものです。
-
JSONの数値です。(JSONデータで数値として解析されます。)
-
先行するプラス(
+
)符号はありません。 -
必要な場合にのみ小数点(
.
)が使用されます。 -
その数値が小数(0と1の間)の場合は、小数点の前に単一のゼロ(
0
)が置かれます。 -
必要な場合にのみ指数表記法(
E
)が使用されます。これが当てはまるのは、特に、出力文字数が制限されすぎている場合です(VARCHAR2(N)
のN
が小さいことによる)。
-
OracleではSQL/JSON標準を拡張し、戻りデータ型がVARCHAR2(N)
の場合は、データ型の直後にオプションのキーワードTRUNCATE
を指定できるようにしました。TRUNCATE
が存在し、かつ戻り値がN
よりも長い場合は、値が切り捨てられ、先頭N
文字のみが戻されます。TRUNCATE
がない場合、このケースはエラーとして扱われ、エラー句またはデフォルトのエラー処理動作によって通常どおり処理されます。
関連項目:
-
SQLデータ型
DATE
およびTIMESTAMP
の詳細は、『Oracle Database SQL言語リファレンス』を参照してください -
SQLデータ型
NUMBER
の詳細は、『Oracle Database SQL言語リファレンス』を参照してください -
Oracle Spatial and Graphデータの使用の詳細は、『Oracle Spatial and Graph開発者ガイド』を参照してください。
親トピック: SQL問合せファンクションおよび条件で使用される句
16.2 SQL/JSON問合せファンクションJSON_QUERYおよびJSON_TABLEで使用されるラッパー句
SQL/JSON問合せファンクションのjson_query
およびjson_table
は、オプションのラッパー句を受け入れます。これには、json_query
によって返される値、またはjson_table
列のデータに使用される値の形式を指定します。この句についてと、デフォルト動作(ラッパー句なし)について説明します。例を示します。
ラッパー句は、次のいずれかの形式をとります。
-
WITH WRAPPER
- パス式と一致するすべてのJSON値が含まれるJSON配列を表す文字列値を使用します。配列要素の順序は指定されません。 -
WITHOUT WRAPPER
- パス式と一致する単一のJSONオブジェクトまたは配列を表す文字列値を使用します。パス式がスカラー値(オブジェクトまたは配列ではありません)または複数の値と一致する場合、エラーが発生します。 -
WITH CONDITIONAL WRAPPER
- パス式と一致するすべてのJSON値を表す文字列値を使用します。0個の値、単一のスカラー値、または複数の値の場合、WITH CONDITIONAL WRAPPER
はWITH WRAPPER
と同じです。単一のJSONオブジェクトまたは配列値の場合は、WITHOUT WRAPPER
と同じです。
デフォルトの動作はWITHOUT WRAPPER
です。
オプションのキーワードUNCONDITIONAL
は、これを使用する方が明確になる場合はキーワードWITH
の直後に追加できます。WITH WRAPPER
とWITH UNCONDITIONAL WRAPPER
は同じことを意味します。
オプションのキーワードARRAY
は、これを使用する方が明確になる場合はキーワードWRAPPER
の直前に追加できます。WRAPPER
とARRAY WRAPPER
は同じことを意味します。
表16-1は、ラッパー句の使用可能性を示しています。配列ラッパーはbold
で示されています。
表16-1 JSON_QUERYラッパー句の例
パス式と一致するJSON値 | WITH WRAPPER | WITHOUT WRAPPER | WITH CONDITIONAL WRAPPER |
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
エラー(スカラー) |
|
|
|
エラー(複数の値) |
|
なし |
|
エラー(値なし) |
|
たとえば、JSONオブジェクトを取得するためのjson_query
問合せを検討してみます。パス式がオブジェクトではなくJSONスカラー値と一致した場合、または複数のJSON値(任意の種類)と一致した場合はどうなるでしょうか。エラーを発生させずに、一致した値を取得したい場合があります。たとえば、オブジェクトである値の1つを選択し、さらに処理したい場合があります。配列ラッパーを使用すると、このような処理を行うことができます。
ラッパーを使用する理由がエラーの発生を回避することのみであり、これらのエラー事例をエラーでない事例と区別する必要がない場合、条件付きラッパーが役立ちます。アプリケーションが探しているものが単一のオブジェクトまたは配列であり、パス式によって一致したデータがそのものである場合、予期した値を単一の配列にラップする必要はありません。
一方、無条件ラッパーの場合、結果として生成される配列は常にラッパーであることが判明しています。アプリケーションはこの事実に頼ることができます。条件付きラッパーを使用する場合、アプリケーションでは、戻された配列を解析するために追加の処理を行う必要がある場合があります。たとえば、表16-1では、同じ配列([42, "a", true]
)が、この配列と一致するパス式、およびその各要素と一致するパス式という、まったく異なる事例に対して戻されています。
親トピック: SQL問合せファンクションおよび条件で使用される句
16.3 SQL問合せファンクションおよび条件で使用されるエラー句
一部のSQL問合せファンクションおよび条件は、オプションでエラー句を受け入れます。これにより、ファンクションまたは条件によって発生した実行時エラーの処理方法を指定します。この句についてと、デフォルト動作(エラー句なし)についてまとめます。
デフォルトでは、JSON用のSQLファンクションおよび条件により、実行時エラーの発生が回避されます。たとえば、JSONデータが構文的に無効な場合、json_exists
およびjson_equal
はfalseを戻し、json_value
はNULL
を戻します。
ただし、場合によってはデフォルト動作をオーバーライドするエラー句を指定することもできます。指定できるエラー処理は異なりますが、エラー処理を指定可能なJSON用の各SQLファンクションおよび条件では、少なくともエラー発生のERROR ON ERROR
動作がサポートされます。
オプションのエラー句は次の形式を使用できます。
-
ERROR ON ERROR
- エラーを発生させます(特別な処理なし)。 -
NULL ON ERROR
- エラーを発生させるかわりにNULL
を戻します。json_exists
に使用できません。 -
FALSE ON ERROR
- エラーを発生させるかわりにfalseを戻します。デフォルトである
json_exists
およびjson_equal
にのみ使用できます。 -
TRUE ON ERROR
- エラーを発生させるかわりにtrueを戻します。json_exists
およびjson_equal
にのみ使用できます。 -
EMPTY OBJECT ON ERROR
- エラーを発生させるかわりに空のオブジェクト({}
)を戻します。json_query
にのみ使用できます。 -
EMPTY ARRAY ON ERROR
- エラーを発生させるかわりに空の配列([]
)を戻します。json_query
にのみ使用できます。 -
EMPTY ON ERROR
–EMPTY ARRAY ON ERROR
と同じ。 -
DEFAULT '
literal_return_value
' ON ERROR
- エラーを発生させるかわりに指定した値を戻します。値は、問合せのコンパイル時に定数である必要があります。使用不可:
-
json_exists
動作を含むjson_exists
、json_equal
、json_serialize
、json_mergepatch
またはjson_table
列値句の場合 -
json_query
、またはjson_query
動作を含むjson_table
列値句 -
json_table
の低レベル・エラー処理 -
SDO_GEOMETRY
がjson_value
のRETURNING
句データ・タイプとして、またはjson_table
列データ・タイプとして指定されている場合
-
デフォルト動作はNULL ON ERROR
(条件json_exists
およびjson_equal
を除く)です。
ノート:
json_table
のエラー処理には2つのレベルがありますが、これらは、パス式の2つのレベルである行と列に対応しています。存在する場合、列のエラー・ハンドラにより、行レベルのエラー処理がオーバーライドされます。両レベルのデフォルトのエラー・ハンドラはNULL ON ERROR
です。
ノート:
欠落フィールドの照合を試行したことによるエラーの場合、ON EMPTY
句はON ERROR
で指定された動作より優先されます。
ノート:
ON ERROR
句は、構文的に正しいSQL/JSONパス式がJSONデータに対して照合されたときに発生する実行時エラーに対してのみ有効です。構文的に正しくないパス式の場合、コンパイル時エラーが発生します。これは、ON ERROR
句によっては処理されません。
関連項目
関連項目:
-
JSON用のSQLファンクションのエラー句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
-
JSON用のSQL条件のエラー句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
親トピック: SQL問合せファンクションおよび条件で使用される句
16.4 SQL/JSON問合せファンクションで使用される空白フィールド句
SQL/JSON問合せファンクションのjson_value
、json_query
およびjson_table
は、オプションでON EMPTY
句を受け入れます。これは、対象となるJSONフィールドが問合せデータ内にない場合の処理を指定します。この句についてと、デフォルト動作(ON EMPTY
句なし)について説明します。
通常、SQL/JSONファンクションおよび条件のエラーへの対処は、エラー句(ON ERROR
)を使用して行います。しかし、データ内に見つからない特定のJSONフィールドと照合するために問合せるときのように、この通常のエラー処理とは異なる対処が必要な特別なケースがあります。照合するフィールドがないという理由だけでエラーを発生させたくない場合があります。(欠落フィールドは、通常エラーとして扱われます。)
通常は、ON ERROR
句と一緒にNULL ON EMPTY
句を使用します。このように組み合せることにより、通常のエラーはON ERROR
句に従って処理されますが、欠落フィールドの照合を試行した場合のエラーにはNULL
が戻されるだけになります。このときに、ON EMPTY
句がない場合は、ON ERROR
句で欠落フィールドのケースも処理されます。
NULL ON EMPTY
の他に、ERROR ON EMPTY
とDEFAULT
... ON EMPTY
も使用できます。これらは、同様の名前を持つON ERROR
句と類似しています。
ON EMPTY
句のみがある(ON ERROR
句がない)場合、欠落フィールドの動作はON EMPTY
句によって指定され、その他のエラーは、NULL ON ERROR
句がある場合と同じ方法で処理されます(これがON ERROR
のデフォルト)。どちらの句もない場合には、NULL ON ERROR
のみが使用されます。
JSON_VALUEで作成された索引に対するNULL ON EMPTYの使用
NULL ON EMPTY
は、json_value
式で作成される関数索引の場合に特に便利です。この句は、索引の選択を行うかどうか、またはいつ行うかへの影響は持ちませんが、json_value
式の対象であるフィールドが欠落しているために索引付けが行われない一部のデータでインデックス付けが行われるようにすることを許可する際に有効です。
索引を移入する問合せには、通常ERROR ON ERROR
を使用することによって、問合せパス式の結果が複数の値や複雑な値になったときはエラーが発生するようにします。しかし、パス式の対象フィールドが欠落しているだけはエラーを発生させずに、そのデータをインデックス付けする場合があります。例28-5は、json_value
式で索引を作成するときのNULL ON EMPTY
の使用方法を示しています。
16.5 JSON_VALUEのON MISMATCH句
RETURNING
句でユーザー定義のオブジェクト型またはコレクション型インスタンスを指定する場合、ファンクションjson_value
には、オプションでON MISMATCH
句を指定できます。この句では、対象のJSON値が指定したSQL戻り値と一致しない場合に使用する処理を指定します。この句についてと、デフォルト動作(ON MISMATCH
句なし)について説明します。
ノート:
ON ERROR
句およびON EMPTY
句は、パス式に一致するJSONデータがない場合にのみ適用されます。パス式に一致するJSONデータが、指定されたオブジェクトまたはコレクションの戻り型と一致しない場合は、ON MISMATCHが
句が適用されます。戻り型がユーザー定義のオブジェクト型またはコレクション型のインスタンスである場合にのみ、ON MISMATCH
句を使用できます。別の戻り型で使用する場合は、問合せのコンパイル時エラーが発生します。
json_value
ファンクションが対象とするJSONデータを反映するSQLオブジェクト型またはコレクション型のインスタンスを返す場合、その対象とするデータと、返されるオブジェクトまたはコレクションの定義が一致する必要があります。一致しない場合、問合せのコンパイル時エラーが適用されます。
このようなエラーのデフォルト処理は、無視するだけです。ただし、次のように1つ以上のON MISMATCH
句を指定して、このようなエラーを様々な方法で処理できます。
-
IGNORE ON MISMATCH
: デフォルトの動作(不一致を無視)を明示的に指定します。返されるオブジェクトまたはコレクションのインスタンスには、対象のJSONデータに対する不一致のため、1つ以上のSQLNULL
値を含めることができます。 -
NULL ON MISMATCH
—オブジェクト型またはコレクション型の値としてSQLNULL
を返します。 -
ERROR ON MISMATCH
—不一致の場合に問合せのコンパイル時エラーを発生させます。
また、各タイプのON MISMATCH
句の後には、それぞれが処理する不一致の種類を示す1つ以上の句をカンマ(,
)で区切り、カッコ((
…)
)で囲んで指定できます。不一致の種類は次のとおりです。
-
MISSING DATA
—オブジェクト型またはコレクション型のデータに一致するために必要だったJSONデータが欠落していました。 -
EXTRA DATA
—1つ以上のJSONフィールドに、対応するオブジェクト型またはコレクション型のデータがありません。たとえば、JSONフィールドaddress
について、同じ名前のオブジェクト型属性がありません(デフォルトでは大文字と小文字を区別しません)。 -
TYPE ERROR
—JSONスカラー値のデータ型に、対応する戻りSQLスカラー・データ型との互換性がありません。これは、表18-1で示されている非互換性、またはSQLデータ型の制約が厳しすぎる(VARCHAR(2)
がJSON文字列"hello"
には短すぎるなど)が原因である可能性があります。
指定されたハンドラ(NULL ON MISMATCH
など)に対してこのような種類の不一致句(EXTRA DATA
など)が存在しない場合、そのハンドラはすべての種類の不一致に適用されます。
様々な種類のON MISMATCH
句を必要な数だけ使用できますが、2つ以上が互いに矛盾する場合は、問合せのコンパイル時エラーが発生します。