13.1 JSON_TRANSFORMでのバインド変数の使用

一般的には、パフォーマンスと柔軟性を高めるためにOracle SQLファンクションjson_transformとともにバインド変数を使用します。

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

バインド変数は、よくプログラミング言語で使用されます。たとえば、Java JDBCのsetterファンクションでは、プリコンパイルされた文にバインドされる変数を使用できます。ここでは、SQLバインド変数を使用してSQLのUPDATE文に値を転送する例を示します。

ノート:

SQL/JSON条件json_existsとともにバインド変数を使用する場合も、バインド変数を使用する理由は同じであり、同じPASSING句構文が適用されます。

例13-5 JSON_TRANSFORMでのSQLバインド変数の使用

CREATE JSON COLLECTION TABLE customers;

INSERT INTO customers VALUES ('{"_id"    : 1234,
                                "name"   : "Jane Doe",
                                "email"  : "[e-mail]",
                                "tags"   : [ "newsletter" ],
                                "status" : "gold",
                                "joined" : "2024"}');

このUPDATE文では、2025年より前に参加したすべてのゴールドステータス顧客にタグ"free shipping"を追加しています。

UPDATE customers c
  SET c.data =
      json_transform(c.data,
                     APPEND '$?(@status == "gold").tags' = 'free shipping')
  WHERE json_exists(c.data, '$?(@joined < 2025)');

この操作では、指定されたステータス("gold")を持つ、指定された年(2025)より前に参加した顧客に、特定のタグ("free shipping")が追加されます。これらの指定された値(タグ、ステータスおよび年)は、変更される可能性が高い値ですが、基本的な更新操作は変わりません。

しかしながら、非常によく似たUPDATEを作成して、別のステータス("silver")を持つ、別の年(2026)より前に参加した顧客に別のタグ("loyalty discount")を追加するようにした場合は、基本的には同じコードであっても、そのテキスト表現が変更されているため、再コンパイルする必要があります。

このような固有のUPDATE文を使用するのではなく、基本操作をテンプレートとして使用し、変更される可能性がある単純な部分を抽象化して、それらをバインド変数に置き換えるようにしています。SQL Developerでは、バインド変数を定義するコードは次のようになります:

VAR status VARCHAR2(20);
exec :status = 'gold';

VAR tag VARCHAR2(20);
exec :tag = 'free shipping';

VAR year NUMBER;
exec :year = 2025;

これらのバインド変数を使用するUPDATE文は、リテラル値を使用する文よりも汎用的です — このテキスト・テンプレートから生成されたコンパイル済コードでは、これらの変数の値が異なっていても対応できます。

UPDATE customers c
           SET c.data =
               json_transform(c.data,
                              APPEND '$?(@status == $statusVar).tags' = ':tag'
                              PASSING :status AS 'statusVar')
           WHERE json_exists(c.data, '$?(@joined < $yearVar)
                             PASSING :year AS 'yearVar')');

SQLバインド変数:tagは、APPEND代入操作のRHS (右辺)として、SQL式(リテラル文字列'tag')で使用されています。このSQL式が評価されると、結果となる値は、そのバインド変数の値になります。(その後、代入によってSQL文字列からJSON文字列に暗黙的に変換されます。)

SQLバインド変数:statusおよび:yearは、それぞれSQL/JSON変数statusVarおよびyearVarとしてjson_transformおよびjson_existsに渡されて、SQL/JSONパス式で使用されます。