二面性ビューの表の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 AI 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-12 二面性ビュー定義での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-13 二面性ビュー定義でのWHERE句の使用(GraphQL)
この例では、GraphQLを使用して二面性ビューrace_dv_medalを定義します。例3-12に示すように、SQLを使用してビューを作成するのと同じです。
このビューは、ビューrace_dv (例3-11)に似ています。(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}}};
GraphQLディレクティブ@whereの2回目の出現を使用して、含めるdriver_race_map_id行をフィルタするかわりに、次のコードを使用できます:
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 (check: {position: {_lte: 3}})
@insert @update @delete
{driverRaceMapId : driver_race_map_id,
position : position,
driver @noupdate @nodelete @noinsert
@unnest
{driverId : driver_id,
name : name}}};
これは、標準のGraphQL構文の拡張であるOracle GraphQL query-by-example (QBE)構文(check ...)を使用します。フィルタリングに@whereを多用する場合の代替として役立ちます。ただし、ここでの@whereの残りの使用は、checkを使用して表すには複雑すぎます(SQL関数to_dateを使用しているため)。二面性ビュー固有の例は、「JSONリレーショナル二面性ビューのQBE」を参照してください。Oracle AI DatabaseでサポートされているすべてのGraphQL QBE演算子は、OracleのGraphQL QBEを参照してください。表列の単純な等価述語の場合は、@WHEREディレクティブのかわりにGraphQL引数を使用できます。詳細な例は、「Oracleでサポートされている二面性ビュー作成用GraphQL引数」を参照してください。