18.6 SQL/JSON問合せファンクションで使用されるON MISMATCH句
ON MISMATCH句をSQL/JSONファンクションjson_value、json_queryおよびjson_tableで使用して、型照合の例外を処理できます。対象のJSON値が指定のSQL戻り値と一致しない場合に使用する処理を指定します。この句についてと、デフォルト動作(ON MISMATCH句なし)について説明します。
ノート:
ON MISMATCH句は、ON EMPTY句とON ERROR句のいずれも適用されない場合にのみ適用されます。これは一般に、対象となるJSONデータがパス式と一致するが、その対象となるデータの型が指定された戻り型と一致しない場合に適用されます。より正確には、対象となるデータが戻り型に変換できない場合にON MISMATCHが適用されます。たとえば、対象値"cat" (JSON文字列)は、SQL NUMBER値に変換できません。
ON EMPTY句は、パス式で対象とされたフィールドが問合せ対象データに存在しない場合に適用されます。
ON ERROR句は、問合せの処理中にエラーが発生した場合に適用されます。これには、問合せ構文が無効な場合と、配列ラッパーが指定されていないjson_value問合せまたはjson_query問合せで複数の値を対象とする場合が含まれます。
問合せで、ファンクションjson_value、json_queryまたはjson_tableによる対象となったJSONデータが反映されたSQL値が返される場合は、対象となったデータと返される値の型が一致する必要があります。そうでない場合は、エラーが発生します。
ON ERRORハンドラが指定されている場合は、その動作がON MISMATCHのデフォルト動作として適用されます: これは、ON MISMATCH句が指定されていない場合の型不一致での動作です。
型不一致の動作の指定には、次の方法で1つ以上のON MISMATCH句を使用できます。
-
IGNORE ON MISMATCH: デフォルトの動作(不一致を無視)を明示的に指定します。返されるオブジェクトまたはコレクションには、対象となるJSONデータに対する不一致のため、1つ以上のSQLNULL値が含まれることがあります。この値は、問合せがユーザー定義オブジェクトまたはコレクション型のインスタンスを対象とする場合のみ使用できます。これは、
json_value(またはjson_valueセマンティクスが含まれるjson_table列)が使用されている場合にのみ当てはまる可能性があります。別の型のデータが対象とされると、エラーが発生します。 -
NULL ON MISMATCH— 値としてSQLNULLを返します。ノート:
SQL/JSONファンクション
json_valueが、record型または索引付き表型のRETURNING型が含まれているPL/SQLコードで使用されていると、NULLが返せなくなります。これらの型は不可分的にNULLにできないためです。そのため、
NULL ON MISMATCH句とNULL ON EMPTY句では、こうしたコレクション型に対してNULL値を返せません。NULLが返されることはなく、コンパイル時エラーが発生します。(SQLオブジェクト、VARRAYまたはネストされた表に対するRETURNING型が含まれたPL/SQLコードの場合、それらの型の値は不可分的にNULLにできるため、このような例外は存在しません)。 -
ERROR ON MISMATCH— 不一致の場合にエラーが発生します。
ファンクションjson_value (またはjson_valueセマンティクスが含まれるjson_table)がユーザー定義のオブジェクト型またはコレクション型のインスタンスを返す場合、ON MISMATCH句の各型の後に、それぞれが処理する不一致の種類を示す1つ以上の句をカンマ(,)で区切り、カッコで囲んで((…))続けることができます。不一致の種類は次のとおりです。
-
MISSING DATA—オブジェクト型またはコレクション型のデータに一致するために必要だったJSONデータが欠落していました。 -
EXTRA DATA—1つ以上のJSONフィールドに、対応するオブジェクト型またはコレクション型のデータがありません。たとえば、JSONフィールドaddressについて、同じ名前のオブジェクト型属性がありません(デフォルトでは大文字と小文字を区別しません)。 -
TYPE ERROR: JSONスカラー値のデータ型に、対応する戻りSQLスカラー・データ型との互換性がありません。これは、表18-2で示されている一般的な型の非互換性、またはSQLデータ型の制約が厳しすぎること(VARCHAR(2)がJSON文字列"hello"には短すぎるなど)が原因である可能性があります。
指定されたハンドラ(NULL ON MISMATCHなど)に対してこのような種類の不一致句(EXTRA DATAなど)が存在しない場合、そのハンドラはすべての種類の不一致に適用されます。
様々な種類のON MISMATCH句を必要な数だけ使用できますが、2つ以上が互いに矛盾する場合は、問合せのコンパイル時エラーが発生します。
ノート:
"only"項目メソッド(名前に"only"が含まれている項目メソッド)を使用する場合は、指定したタイプの入力型の値のみ互換性があります。たとえば、項目メソッドbooleanOnly()を使用する場合は、JSON言語型booleanの入力値のみ、リストされた変換先SQL型(BOOLEAN、VARCHAR2およびCLOB)と互換性があります(変換可能)。
このコンテキストにおいては、項目メソッドidOnly()は例外です。これは、その入力が、JSON言語型ファミリのバイナリであるだけでなく識別子として適している必要もあるためです。「JSONデータ型の値の比較とソート」も参照してください。
表18-2 互換性のあるスカラー・データ型: JSONからSQLへの変換
| JSON言語型(ソース) | SQL型(宛先) | ノート |
|---|---|---|
binary |
RAW |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
binary |
BLOB |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
binary |
CLOB |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
boolean |
BOOLEAN |
インスタンス値は、SQLブール値TRUEまたはFALSEです。
|
boolean |
VARCHAR2 |
インスタンス値は、SQL文字列"true"または"false"です。
|
boolean |
CLOB |
インスタンス値は、SQL文字列"true"または"false"です。
|
date |
DATE (場合によってはゼロの)時間コンポーネントを含みます脚注1
|
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
date |
TIMESTAMP |
時間コンポーネントにはゼロが埋め込まれます。SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
daysecondInterval |
INTERVAL DAY TO SECOND |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
double |
BINARY_DOUBLE |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
double |
BINARY_FLOAT |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
double |
NUMBER |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
double |
VARCHAR2 |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
double |
CLOB |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
float |
BINARY_FLOAT |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
float |
BINARY_DOUBLE |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
float |
NUMBER |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
float |
VARCHAR2 |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
float |
CLOB |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
null |
任意のSQLデータ型。 | インスタンス値はSQL NULLです。
|
number |
NUMBER |
なし。 |
number |
BINARY_DOUBLE |
なし。 |
number |
BINARY_FLOAT |
なし。 |
number |
VARCHAR2 |
なし。 |
number |
CLOB |
なし。 |
string |
VARCHAR2 |
なし。 |
string |
CLOB |
なし。 |
string |
NUMBER |
JSON文字列は数値である必要があります。 |
string |
BINARY_DOUBLE |
JSON文字列は数値である必要があります。 |
string |
BINARY_FLOAT |
JSON文字列は数値である必要があります。 |
string |
DATE (場合によってはゼロの)時間コンポーネントを含みます脚注1 |
JSON文字列は、サポートされているISO8601形式である必要があります。 |
string |
TIMESTAMP |
JSON文字列は、サポートされているISO8601形式である必要があります。 |
string |
INTERVAL YEAR TO MONTH |
JSON文字列は、サポートされているISO 8601継続時間形式である必要があります。 |
string |
INTERVAL DAY TO SECOND |
JSON文字列は、サポートされているISO 8601継続時間形式である必要があります。 |
timestamp |
TIMESTAMP
|
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
timestamp |
DATE (場合によってはゼロの)時間コンポーネントを含みます脚注1 |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
yearmonthInterval |
INTERVAL YEAR TO MONTH |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
脚注1 たとえば、時間コンポーネントがゼロのDATEインスタンスが、時間コンポーネントの保持が指定されていないjson_value RETURNING DATE句によって返されます。
例18-2 ON MISMATCH句の使用
この例では、次のオブジェクト・リレーショナル・データを様々な問合せで使用します。型不一致の動作を除き、問合せは同一です。各問合せは、存在しないJSONフィールドmiddleを対象とします。
CREATE TYPE person_T AS OBJECT (
first VARCHAR2(30),
last VARCHAR2(30),
birthyear NUMBER);この問合せでは、オブジェクトperson_t('Grace', 'Hopper', 1906)を返します。デフォルトのエラー・ハンドラはNULL ON ERRORであるため、フィールドmiddleは無視されます。
SELECT json_value('{"first": "Grace",
"middle": "Brewster",
"last": "Hopper",
"birthyear": "1906"}',
'$'
RETURNING person_t);この問合せでは、余計なデータの不一致のためにエラーが発生します。フィールドmiddleは余計です。
SELECT json_value('{"first": "Grace",
"middle": "Brewster",
"last": "Hopper",
"birthyear": "1906"}',
'$'
RETURNING person_t
ERROR ON MISMATCH (EXTRA DATA));
ORA-40602: extra data for object type conversionこの問合せでは、3つのON MISMATCH句を使用します。オブジェクトperson_t('Grace', 'Hopper', NULL)を返します。ERROR ON MISMATCH (EXTRA DATA)句は、それ自体でエラーが発生しますが、IGNORE ON MISMATCH (TYPE ERROR)によってそのエラーは無視されます。
SELECT json_value('{"first": "Grace",
"middle": "Brewster",
"last": "Hopper",
"birthyear": "1906"}',
'$'
RETURNING person_t
ERROR ON MISMATCH (EXTRA DATA)
ERROR ON MISMATCH (MISSING DATA)
IGNORE ON MISMATCH (TYPE ERROR));