SQLファンクションおよび条件のPASSING句

18.1 SQLファンクションおよび条件のPASSING句

Oracle SQLファンクションのjson_transform、SQL/JSONファンクションのjson_valueおよびjson_query、SQL/JSON条件のjson_existsでは、オプションのPASSING句を受け入れます。この句では、パス式で使用するためにSQL値をSQL/JSON変数にバインドします。

基本的に同じ問合せ文または更新文が繰り返されており、そのテキスト表現にある一部のリテラル値のみが異なる場合は、そのコードを再コンパイルする必要があり、それにはコストがかかる可能性があります。これは特に、同じコードが繰り返し実行されており、比較または代入での一部の値のみが変更されている場合に当てはまります。通常は、このようなリテラル値のかわりにバインド変数を使用することをお薦めします。

キーワードPASSINGの後に、カンマで区切られた1つ以上のSQL/JSON変数バインディング(42 AS "d"など)が続きます。

各バインディングは、(1)評価するSQL式、(2)キーワードAS、(3)SQL/JSON変数名で構成されます。^脚注1バインディング42 AS "d"は、式の値42をdという名前のSQL/JSON変数にバインドします。これは、$.PONumber?(@ > $d)などのパス式で使用できます。

PASSING句をTYPE (STRICT)句とともに使用すると、パス式のSQL/JSON変数と比較される各値が、関連する"only"データ型変換項目メソッドが値に適用されたかのように、JSON言語の型に関して厳密に比較されます。比較に使用される型は、SQL/JSON変数の型です。

たとえば、TYPE (STRICT)と指定した場合、変数$dの数値の値に対する$.PONumber?(@ > $d)などの比較は、$.PONumber?(@.numberOnly() > $d)であるかのように暗黙的に処理されます。そのため、これらの2つの問合せは同じ動作をします。$dの値は数値であるため、値が数値であるPONumberフィールドのみが考慮されます。

SELECT count(*) FROM j_purchaseorder
WHERE json_exists(data, '$.PONumber?(@.numberOnly() > $d)'
PASSING to_number(:1) AS "d");

SELECT count(*) FROM j_purchaseorder
WHERE json_exists(data, '$.PONumber?(@ > $d)'
PASSING to_number(:1) AS "d" TYPE(STRICT));

評価する式は、データ型がBINARY_DOUBLE、BOOLEAN、DATE、JSON、NUMBER、TIMESTAMPまたはTIMESTAMP WITH TIME ZONE、VARCHAR2、VECTORである必要があり、それ以外の場合は、エラーが発生します。

式がSQLのNULL値に評価される場合、その結果は、次のように、そのNULL値のSQL型に応じて異なります:

SQL型JSONのNULLを渡すと、エラーが発生します。
SQL型VARCHAR2のNULLを渡すと、その変数は空のJSON文字列""にバインドされます。
SQL型RAWのNULLを渡すと、その変数は長さゼロのJSONバイナリ値にバインドされます。
その他のSQL型のNULLを渡すと、その変数はJSONのnull値にバインドされます。

ノート:

SQL/JSON変数名にはSQL識別子の構文がありますが、次の制限があります。

SQL/JSON変数名には、その定義に使用されるSQL識別子に引用符文字が含まれていても、引用符文字は含まれません。

JSONファンクションおよび条件のPASSING句では、キーワードASに続くSQL識別子は、引用符で囲まれた識別子または引用符で囲まれていない識別子(AS "d"やAS dなど)にすることができます。これは、1つ目のケース(名前に引用符なし)ではdという名前の、2つ目のケース(暗黙的に大文字)ではDという名前のSQL/JSON変数を定義します。(1つ目のケースのSQL識別子はdではなく"d"で、2番目のケースではdではなくDです。)
SQL/JSON変数名に使用できるのは、ASCII英数字またはASCIIアンダースコア文字(10進コード95)のみです。さらに、名前は数字ではなく文字またはアンダースコア文字で始める必要があります。たとえば、42 AS "2d"、42 AS "d+"および42 AS "dã"ではそれぞれエラーが発生し、1つ目の理由は数字で始まるため、2つ目の理由は英数字ではないASCII文字(+)が含まれているため、3つ目の理由はASCII以外の文字(ã)が含まれているためです。

SQL/JSON変数は、$の後にSQL/JSON変数名が続きます。たとえば、$dはDという名前の変数です。

SQL/JSONパス式では、SQL識別子ではなくSQL/JSON変数が使用されます。特に、これは引用符が存在しないことを意味し、名前を直接使用します。たとえば、$.PONumber?(@ > $"d")ではエラーが発生し、$.PONumber?(@ > $d)が正しい構文です。

関連トピック