3.3 二面性ビューの表のWHERE句

JSONリレーショナル二面性ビューを作成する際、WHERE句で単純なテストを使用して、基礎となる表を結合するだけでなく、JSONデータの生成に使用される表の行を選択することもできます。これにより、二面性ビューでサポートされているJSONドキュメントに含めるデータをきめ細かく制御できるようになります。

1つのユースケースとして、識別表の列の値に応じて、サポートされているJSONドキュメントに異なるデータが含まれる複数の二面性ビューを作成できます。

たとえば、発注書の同じ基礎となる表ORDERSを使用して、二面性ビューopen_ordersおよびshipped_ordersを定義できます。最初のビューではWHERE order_status="open"句で表から行を選択し、2番目のビューではWHERE order_status="shipped"で行を選択します。

ただし、二面性ビューのWHERE句のテストで使用される列は、サポートされているJSONドキュメントのフィールドに移入するために使用する必要はありません。たとえば、ビューopen_ordersおよびshipped_ordersについて選択された発注書ドキュメントには、列order_statusの値を使用するフィールドは必要ありません。

二面性ビュー定義で使用される各WHERE句には、キーワードWITH CHECK OPTIONが含まれている必要があります。これにより、WHERE句テストに含まれていない行を生成する表への変更は禁止されます。『Oracle Database SQL言語リファレンス』の「CREATE VIEW」を参照してください。

二面性ビュー定義で使用できるWHERE句は、比較的単純である必要があり、次の構造体のみを使用できます:

  • 列の値と、同じ基礎となる表の他の列の値またはリテラル値との直接比較。たとえば、height > width、height > 3.14です。ANSI SQL比較演算子(=、<>、<、<=、>、>=)のみ使用できます。

  • 列の値を使用した(非集計) SQL式、またはそのような式のブール組合せ。たとえば、upper(department) = 'SALES'、salary < 100 、 bonus < 15です。

  • SQL JSON構造体(json_valueやjson_existsなどのファンクションと条件、および単純なドット表記法SQL構文)の使用。

特に、二面性ビュー定義のWHERE句に次を含めることはできません(含めると、エラーが発生します)。

  • PL/SQLサブプログラムの使用。

  • 副問合せの結果との比較。たとえば、t.salary > (SELECT max_sal FROM max_sal_table WHERE jobcode=t.job)です。

  • 外部問合せブロック内の列への参照。

  • バインド変数の使用。たとえば、salary = :var1です。

  • 集計演算子の使用。たとえば、sum(salary) < 100です。

  • 複数の列操作の使用。たとえば、salary + bonus < 10000です。

  • 副問合せでの結合条件と別のテストとの間のORの使用。このような使用では、結合条件はオプションになります。たとえば、e.deptno=d.deptno OR e.job='MANAGER'です。この場合、e.deptno=d.deptnoが結合条件です。(ただし、最上位/最外部の問合せでは、ORをこのように使用できます。)

例3-9 二面性ビュー定義でのWHERE句の使用(SQL)

この例では、ビューrace_dv (例3-5)に似た二面性ビューrace_dv_medalを定義します。(1)追加のWHERE句テストを使用してフィールドresultを最初の3つのレース・ポジション(1位、2位および3位)に制限し、(2) 2019年より最近のレースのみが含まれるという点で異なります。 

CREATE JSON RELATIONAL DUALITY VIEW race_dv_medal AS
  SELECT JSON {'_id'    : r.race_id,
               'name'   : r.name,
               'laps'   : r.laps WITH NOUPDATE,
               'date'   : r.race_date,
               'podium' : r.podium WITH NOCHECK,
               'result' :
                 [ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
                                'position'        : drm.position,
                                UNNEST
                                  (SELECT JSON {'driverId' : d.driver_id,
                                                'name'     : d.name}
                                     FROM driver d WITH NOINSERT UPDATE NODELETE
                                     WHERE d.driver_id = drm.driver_id)}
                     FROM driver_race_map drm WITH INSERT UPDATE DELETE
                     WHERE drm.race_id = r.race_id
                           AND drm.position <= 3 WITH CHECK OPTION ]}
    FROM race r WITH INSERT UPDATE DELETE
    WHERE r.race_date >= to_date('01-JAN-2020') WITH CHECK OPTION;

例3-10 二面性ビュー定義でのWHERE句の使用(GraphQL)

この例では、GraphQLを使用して二面性ビューrace_dv_medalを定義します。例3-9に示すように、SQLを使用してビューを作成するのと同じです。

このビューは、ビューrace_dv (例3-8)に似ています。(1)追加のWHERE句テストを使用してフィールドresultを最初の3つのレース・ポジション(1位、2位および3位)に制限し、(2) 2019年より最近のレースのみが含まれるという点で異なります。 

CREATE JSON RELATIONAL DUALITY VIEW race_dv_medal AS
  race @insert @update @delete
       @where (sql: "race_date >= to_date('01-JAN-2020')")
  {_id    : race_id,
   name   : name,
   laps   : laps @noupdate,
   date   : race_date,
   podium : podium @nocheck,
   result : driver_race_map @insert @update @delete
                            @where (sql: "position <= 3")
     {driverRaceMapId : driver_race_map_id,
      position        : position,
      driver @noupdate @nodelete @noinsert
        @unnest
        {driverId : driver_id,
         name     : name}}};

親トピック: 二面性ビューの作成