用途
SELECT
文または副問合せを使用すると、1つ以上の表、オブジェクト表、ビュー、オブジェクト・ビューまたはマテリアライズド・ビューからデータを取り出すことができます。
SELECT
文の結果(またはその一部)が既存のマテリアライズド・ビューと同じ場合、そのマテリアライズド・ビューをSELECT
文で指定した1つ以上の表のかわりに使用できます。この置き換えを、クエリー・リライトといいます。これが行われるのは、コスト最適化が有効であり、かつQUERY_REWRITE_ENABLED
パラメータがTRUE
に設定されている場合のみです。クエリー・リライトが行われたかどうかを特定するには、EXPLAIN
PLAN
文を使用します。
関連項目:
|
前提条件
表またはマテリアライズド・ビューからデータを選択する場合、表またはマテリアライズド・ビューが自分のスキーマ内にある必要があります。自分のスキーマ内にない場合は、その表またはマテリアライズド・ビューに対するREAD
またはSELECT
権限が必要です。
ビューの実表から行を選択する場合、次の条件を2つとも満たしている必要があります。
ビューが自分のスキーマ内に設定されている必要があります。設定されていないかぎり、ビューのREAD
またはSELECT
権限が必要です。
ビューを含むスキーマの所有者が、実表に対するREAD
またはSELECT
権限を持っている。
READ
ANY
TABLE
またはSELECT
ANY
TABLE
システム権限を持っている場合、任意の表、マテリアライズド・ビューまたはビューの実表からデータを選択できます。
FOR
UPDATE
句を指定するには、前述の前提条件が次の例外とともに適用されます。READ
およびREAD
ANY
TABLE
権限が示されている場合、FOR
UPDATE
句を指定できません。
flashback_query_clause
を使用してOracleフラッシュバック問合せを発行する場合は、SELECT構文のリスト内のオブジェクトに対するREAD
またはSELECT
権限が必要です。さらに、SELECT構文のリスト内のオブジェクトに対するFLASHBACK
オブジェクト権限またはFLASHBACK
ANY
TABLE
システム権限のいずれかが必要です。
構文
select::=
(subquery::=、for_update_clause ::=を参照)
(query_block::=、order_by_clause ::=、row_limiting_clause::=を参照)
query_block::=
(with_clause::=、select_list::=、table_reference::=、join_clause ::=、where_clause::=、hierarchical_query_clause ::=、group_by_clause ::=、model_clause ::=を参照)
with_clause::=
注意: WITH キーワードは、単独では指定できません。最低限、plsql_declarations またはsubquery_factoring_clause のどちらかを指定する必要があります。 |
cycle_clause::=
select_list::=
table_reference::=
(query_table_expression::=、flashback_query_clause ::=、pivot_clause::=、unpivot_clause::=、row_pattern_clause::=、containers_clause::=を参照)
(subquery_restriction_clause::=、table_collection_expression ::=を参照)
pivot_clause::=
pivot_for_clause::=
pivot_in_clause::=
unpivot_in_clause::=
sample_clause ::=
partition_extension_clause::=
subquery_restriction_clause::=
table_collection_expression::=
join_clause ::=
(inner_cross_join_clause::=、outer_join_clause::=、cross_outer_apply_clause::=を参照)
(table_reference::=を参照)
(query_partition_clause::=、outer_join_type::=、table_reference::=を参照)
(table_reference::=、query_partition_clause::=を参照)
where_clause::=
(condition
には、第6章「条件」で説明した任意の条件を使用できます)
group_by_clause ::=
(rollup_cube_clause::=、grouping_sets_clause::=を参照)
rollup_cube_clause::=
(grouping_expression_list::=を参照)
grouping_sets_clause::=
(rollup_cube_clause::=、grouping_expression_list::=を参照)
grouping_expression_list::=
expression_list::=
model_clause ::=
(cell_reference_options::=、return_rows_clause::=、reference_model::=、main_model::=を参照)
(model_column_clauses::=、cell_reference_options::=を参照)
main_model::=
(model_column_clauses::=、cell_reference_options::=、model_rules_clause::=を参照)
(model_iterate_clause::=、cell_assignment::=、order_by_clause ::=を参照)
model_iterate_clause::=
(single_column_for_loop::=、multi_column_for_loop::=を参照)
order_by_clause ::=
(row_pattern_partition_by::=、row_pattern_order_by::=、row_pattern_measures::=、row_pattern_rows_per_match::=、row_pattern_skip_to::=、row_pattern::=、row_pattern_subset_clause::=、row_pattern_definition_list::=を参照)
row_pattern::=
row_pattern_definition_list::=
(row_pattern_classifier_func::=、row_pattern_match_num_func::=、row_pattern_navigation_func::=、row_pattern_aggregate_func::=を参照)
row_pattern_classifier_func::=
row_pattern_navigation_func::=
(row_pattern_nav_logical::=、row_pattern_nav_physical::=、row_pattern_nav_compound::=を参照)
セマンティクス
with_clause
を使用すると、次の項目を定義できます。
PL/SQLプロシージャおよびファンクション(plsql_declarations
句を使用)
副問合せブロック(subquery_factoring_clause
を使用)
plsql_declarations
plsql_declarations
句を使用すると、PL/SQLファンクションとプロシージャの宣言と定義ができます。その後、この句で指定したPL/SQLファンクションは、問合せ内で参照できます。この問合せに副問合せがある場合は、その副問合せでも参照できます。これらのファンクションの名前が名前解決されるときには、スキーマ・レベルのストアド・ファンクションよりも優先されます。
この句で指定した問合せがトップレベルのSELECT
文ではない場合、その問合せを含むトップレベルのSQL文には、次のルールが適用されます。
トップレベルの文がSELECT
文の場合は、WITH
plsql_declarations
句またはWITH_PLSQL
ヒントが含まれている必要があります。
トップレベルの文がDELETE
文、MERGE
文、INSERT
文、またはUPDATE
文の場合は、WITH_PLSQL
ヒントが含まれている必要があります。
WITH_PLSQL
ヒントを使用して実行できる操作は、文へのWITH
plsql_declarations
句の指定のみです。これは、オプティマイザ・ヒントではありません。
関連項目:
|
subquery_factoring_clause
を使用すると、副問合せブロックに名前(query_name
)を割り当てることができます。query_name
を指定することによって、問合せに複数存在する副問合せブロックを参照することができます。query_name
をインライン・ビューまたは一時表として扱うことによって、問合せが最適化されます。query_name
には、データベース・スキーマ・オブジェクトと同じネーミング規則および制限が適用されます。データベース・オブジェクト名の詳細は、「データベース・オブジェクトのネーミング規則」を参照してください。
query_name
の後に続く列別名およびAS
句内にある複数の副問合せを区切る集合演算子は有効ですが、再帰的副問合せのファクタリングを必要とします。search_clause
およびcycle_clause
は、再帰的副問合せのファクタリングに対してのみ有効ですが、必須ではありません。「再帰的副問合せのファクタリング」を参照してください。
最上位のSELECT
文およびほとんどの副問合せでこの句を指定できます。問合せの名前は、主問合せおよび後続のすべての副問合せから参照できます。再帰的副問合せのファクタリングの場合、問合せの名前は、自身の問合せ名を定義する副問合せからも参照できます。
再帰的副問合せのファクタリング 自身の問合せ名を定義する副問合せからsubquery_factoring_clause
が自身のquery_name
を参照する場合、そのsubquery_factoring_clause
は再帰的であるといいます。再帰的subquery_factoring_clause
には、2つの問合せブロック(1つ目のアンカー・メンバーおよび2つ目の再帰的メンバー)が含まれている必要があります。アンカー・メンバーは、再帰的メンバーの前に指定する必要があり、このメンバーはquery_name
を参照できません。アンカー・メンバーは、集合演算子UNION
ALL
、UNION
、INTERSECT
またはMINUS
によって結合された1つ以上の問合せブロックで構成できます。再帰的メンバーは、アンカー・メンバーの後に指定し、query_name
の参照は1回のみにする必要があります。UNION
ALL
集合演算子を使用して、再帰的メンバーをアンカー・メンバーに結合する必要があります。
WITH
query_name
の後に続く列別名の数、およびアンカーのSELECT
構文のリストと再帰的問合せブロックの数は同じである必要があります。
再帰的メンバーには、次の要素を含めることができません。
DISTINCT
キーワードまたはGROUP
BY
句
model_clause
集計ファンクション。ただし、SELECT構文のリスト内には分析ファンクションを含めることができます。
query_name
を参照する副問合せ。
query_name
を右側の表として参照する外部結合。
search_clause SEARCH
句を使用すると、行の順序付けを指定できます。
子の行が戻される前に兄弟の行を戻す必要がある場合は、BREADTH
FIRST
BY
を指定します。
兄弟の行が戻される前に子の行を戻す必要がある場合は、DEPTH
FIRST
BY
を指定します。
兄弟の行は、BY
キーワードの後にリストされる列によって順序付けされます。
SEARCHキーワードの後に続く
c_aliasリストには、
query_name
に対する列別名のリストからの列名を含める必要があります。
ordering_column
は、問合せ名の列リストに自動的に追加されます。query_name
から選択する問合せは、ordering_column
に
ORDER BY
を含めて、SEARCH
句によって指定された順序で行を戻すことができます。
cycle_clause CYCLE
句を使用すると、再帰内に繰り返しのマークを付けられます。
CYCLEキーワードの後に続く
c_aliasリストには、
query_name
に対する列別名のリストからの列名を含める必要があります。Oracle Databaseは、これらの列を使用して繰返しを検出します。
cycle_value
およびno_cycle_value
は、長さが1の文字列です。
繰返しが検出されると、繰返しを引き起こしている行に対してcycle_mark_c_alias
によって指定されている繰返しマーク列に、cycle_value
として指定されている値が設定されます。その後、この行への再帰は停止します。つまり、問題の行については、その子の行の検索は行われませんが、繰返しが発生していない行については、検索が継続されます。
繰返しが検出されない場合、no_cycle_value
として指定されているデフォルト値が繰返しマーク列に設定されます。
この繰返しマーク列は、query_name
の列リストに自動的に追加されます。
祖先の行のいずれかの繰返し列に同じ値がある行は、繰返しを形成するとみなされます。
CYCLE
句を指定しない場合に、繰返しが検出されると、再帰的WITH
句はエラーを戻します。この場合、再帰的メンバーのWHERE句で参照される
query_nameの列別名リストのすべての列について、祖先の行のいずれかに同じ値がある行は繰返しを形成します。
副問合せのファクタリングの制限事項: この句には、次の制限事項があります。
subquery_factoring_clause
は、1つのSQL文内に1つのみ指定できます。subquery_factoring_clause
で定義したquery_name
は、そのsubquery_factoring_clause
内の後続の任意の名前付き問合せブロックで使用できます。
集合演算子を指定した複合問合せの場合、その問合せを構成する各問合せではquery_name
を使用できませんが、各問合せのFROM句では
query_nameを使用できます。
query_name
に対する列別名のリストには重複した名前を指定できません。
ordering_column
の名前は、cycle_mark_c_alias
とは異なる名前を使用する必要があります。
ordering_column
および繰返しマーク列には、query_name
に対する列別名のリストに存在する名前を使用することはできません。
hint
文の実行計画を選択する場合に、オプティマイザに指示を与えるためのコメントを指定します。
DISTINCT
またはUNIQUE
を指定すると、選択された重複行の1行のみを戻すことができます。これらの2つのキーワードは同義です。重複行とは、SELECT構文のリスト中のそれぞれの式で一致する値を持つ行のことです。
DISTINCTおよびUNIQUE問合せの制限事項: このタイプの問合せには、次の制限事項があります。
DISTINCT
またはUNIQUE
を指定する場合、SELECT構文のリスト中の式すべての総バイト数は、データ・ブロックのサイズからオーバーヘッド分を引いたサイズに制限されます。このサイズは、初期化パラメータDB_BLOCK_SIZE
によって指定されます。
select_list
にLOB列が含まれている場合、DISTINCT
は指定できません。
ALL
を指定すると、重複行を含め、選択されたすべての行を戻すことができます。デフォルトはALL
です。
select_list
select_list
では、データベースから取り出す列を指定できます。
*(全列ワイルド・カード)
全列ワイルド・カード(アスタリスク)を指定すると、疑似列とINVISIBLE
列を除いて、FROM
句に指定されているすべての表、ビューまたはマテリアライズド・ビューのすべての列を選択できます。列は、表、ビューまたはマテリアライズド・ビューの*_TAB_COLUMNS
データ・ディクショナリ・ビューのCOLUMN_ID
によって指定されている順序で戻されます。
ビューやマテリアライズド・ビューではなく表から選択する場合、ALTER
TABLE
SET
UNUSED
文によってUNUSED
のマークが付けられた列は選択されません。
query_name.*
query_name
の後にピリオドおよびアスタリスクを指定すると、指定した副問合せブロックのすべての列を選択できます。query_name
には、subquery_factoring_clause
ですでに指定されている副問合せブロック名を指定します。select_list
でquery_name
を指定するには、subquery_factoring_clause
を指定する必要があります。select_list
でquery_name
を指定するには、query_table_expression
(FROM句)でも
query_nameを指定する必要があります。
table.* | view.* | materialized view.*
オブジェクト名の後にピリオドおよびアスタリスクを指定すると、指定した表、ビューまたはマテリアライズド・ビューのすべての列を選択できます。オブジェクトの作成時に指定された順序で列の集合が戻されます。2つ以上の表、ビューまたはマテリアライズド・ビューの行を選択する問合せを結合といいます。
他のユーザーのスキーマの表、ビューまたはマテリアライズド・ビューから選択する場合には、スキーマ修飾子を使用します。schema
を指定しない場合、この表、ビューおよびマテリアライズド・ビューは自分のスキーマ内にあるとみなされます。
t_alias.*
相関名(別名)の後にピリオドとアスタリスクを指定すると、同じ副問合せのFROM
句にその相関名が指定されているオブジェクトからのすべての列を選択できます。オブジェクトは、表、ビュー、マテリアライズド・ビューまたは副問合せのいずれかです。オブジェクトの作成時に指定された順序で列の集合が戻されます。2つ以上のオブジェクトの行を選択する問合せを結合といいます。
expr
選択する情報を表す式を指定します。リスト中の列が含まれている表、ビューまたはマテリアライズド・ビューがFROM句で
schema名で指定されている場合のみ、その列名を
schema名で指定できます。オブジェクト型のメンバー・メソッドを指定するときは、メソッドが引数を取らない場合でも、カッコを使用するメソッド名に従う必要があります。
c_alias 列式の別名を指定します。この別名は、結果セットの列のヘッダーで使用されます。AS
キーワードはオプションです。別名によって、問合せ中にSELECT構文のリストの項目名を効果的に変更できます。問合せにおいて、別名はorder_by_clause
で使用できますが、他の句では使用できません。
SELECT構文のリストの制限事項: SELECT構文のリストには、次の制限事項があります。
この文にgroup_by_clauseも指定している場合、このSELECT構文のリストには次の式のみ指定できます。
定数
集計ファンクション、USER
ファンクション、UID
ファンクションおよびSYSDATE
ファンクション
group_by_clause
に指定されているものと同じ式。group_by_clause
が副問合せの中にある場合、その副問合せのSELECT構文のリストにあるすべての列が副問合せのGROUP
BY
列と対応する必要があります。SELECT構文のリストおよびトップレベル問合せまたは副問合せのGROUP
BY
列が対応しない場合、その文ではORA-00979が発生します。
グループ内のすべての行が同じ値に評価される前述の式を伴っている式
結合内のキー保存表が1つのみの場合、結合ビューからROWIDを選択することができます。表のROWIDがビューのROWIDになります。
関連項目: キー保存表の詳細は、『Oracle Database管理者ガイド』を参照してください。 |
複数の表に同じ名前の列があり、FROM
句で結合を指定した場合、表の名前または表の別名でその列名を修飾する必要があります。
FROM句
FROM
句を指定すると、どのオブジェクトからデータを選択するかを指定できます。
query_table_expression
query_table_expression
句を使用すると、副問合せブロック、表、ビュー、マテリアライズド・ビュー、パーティションまたはサブパーティションの識別、またはオブジェクトを識別する副問合せの指定を行うことができます。副問合せブロックを指定するには、副問合せブロック名(query_name
)をsubquery_factoring_clauseで指定している必要があります。
ONLY ONLY
句は、ビューのみに適用されます。FROM
句のビューが階層に属し、サブビューの行を含めない場合は、ONLY
句を使用します。
LATERAL LATERAL
を指定して、LATERALインライン・ビューとしてsubquery
を指定します。LATERALインライン・ビュー内では、そのLATERALインライン・ビューの左側に現れる表を、問合せのFROM
句で指定できます。この左相関はsubquery
(SELECT
句、FROM
句、WHERE
句など)内のどこにでも、任意のネスト・レベルで指定できます。
LATERALの制限事項: LATERALインライン・ビューには、次の制限事項が適用されます。
LATERAL
を指定すると、pivot_clause
句、またはunpivot_clause
句が指定できなくなります。また、table_reference
句にパターンを指定できなくなります。
LATERALインライン・ビューにquery_partition_clause
が含まれていて、このビューが結合句の右側になる場合、その結合句には左側の表への左相関を含めることができません。ただし、表への左相関は、左側の表でない場合はFROM
句の左側に含めることができます。
LATERALインライン・ビューには、右外部結合または完全外部結合の最初の表への左相関を含めることができません。
flashback_query_clause
を使用すると、データに関連付けられた時間ディメンションに基づいて、表、ビューまたはマテリアライズド・ビューからデータを取得できます。
この句によってSQL駆動のフラッシュバックが実装されるため、次の項目を指定できます。
句VERSIONS
BETWEEN
{
SCN
|
TIMESTAMP
}
またはVERSIONS
AS
OF
{
SCN
|
TIMESTAMP
}
を使用して、SELECT構文のリストに含まれるオブジェクトごとに、異なるシステム変更番号またはタイムスタンプを指定できます。また、セッション・レベルのフラッシュバックをDBMS_FLASHBACK
パッケージを使用して実装できます。
句VERSIONS
PERIOD
FOR
またはAS
OF
PERIOD
FOR
を使用して、SELECT構文のリストに含まれるオブジェクトごとに有効期間を指定できます。また、セッション・レベルの有効期間フラッシュバックをDBMS_FLASHBACK_ARCHIVE
パッケージを使用して実装できます。
フラッシュバック問合せを使用すると、行に対して行った変更の履歴を取り出すことができます。VERSIONS_XID
疑似列を使用して、変更を行ったトランザクションの対応する識別子を取り出すことができます。また、Oracle Flashback Transaction Queryを発行して、特定の行バージョンを生成したトランザクションの情報を取り出すこともできます。これを行うには、特定のトランザクションIDをFLASHBACK_TRANSACTION_QUERY
データ・ディクショナリ・ビューで問い合せます。
VERSIONS BETWEEN { SCN | TIMESTAMP }
VERSIONS
BETWEEN
を指定すると、問合せによって戻された行の複数のバージョンを取り出すことができます。2つのSCNまたは2つのタイムスタンプ値の間に存在する、行のすべてのコミット済バージョンが戻されます。最初に指定されたSCNまたはタイムスタンプは、2番目に指定されたSCNまたはタイムスタンプよりも前でなければなりません。戻された行には、削除後に再度挿入された行のバージョンが含まれます。
VERSIONS
BETWEEN
SCN
...を指定すると、2つのSCNの間に存在する行のバージョンを取り出すことができます。どちらの式も、評価結果は数値でなければならず、評価結果がNULLであってはなりません。MINVALUE
およびMAXVALUE
は、それぞれ使用可能な一番古いデータおよび最新のデータのSCNに解決されます。
VERSIONS
BETWEEN
TIMESTAMP
...を指定すると、2つのタイムスタンプの間に存在する行のバージョンを取り出すことができます。どちらの式も、評価結果はタイムスタンプ値でなければならず、評価結果がNULLであってはなりません。MINVALUE
およびMAXVALUE
は、それぞれ使用可能な一番古いデータおよび最新のデータのタイムスタンプに解決されます。
AS OF { SCN | TIMESTAMP } AS
OF
を指定すると、特定のシステム変更番号(SCN)またはタイムスタンプでの問合せによって戻された行の単一のバージョンを取り出すことができます。SCN
を指定する場合、expr
は数値に評価される必要があります。TIMESTAMP
を指定する場合、expr
はタイムスタンプ値に評価される必要があります。いずれの場合も、expr
の評価結果がNULLであってはなりません。指定されたシステム変更番号または時刻に存在した行が戻されます。
Oracle Databaseでは、バージョン問合せ疑似列のグループを使用して、様々な行のバージョンに関する追加情報を取り出すことができます。詳細は、「バージョン問合せ疑似列」を参照してください。
両方の句を同時に使用する場合、AS
OF
句によって、SCNまたはデータベースが問合せを発行した時点が判断されます。VERSIONS
句によって、AS
OF
で指定した時点を基準とした行のバージョンが判断されます。トランザクションが、BETWEEN
の最初の値より前に開始したり、AS
OF
で指定した時点より後に終了した場合は、行のバージョンとしてNULLが戻されます。
VERSIONS PERIOD FOR VERSIONS
PERIOD
FOR
を指定すると、特定の期間中に有効と見なされるかどうかに基づいて、table
から行を取り出すことができます。この句を使用するには、table
で時制有効性がサポートされている必要があります。
valid_time_column
には、table
の有効期間ディメンション列の名前を指定します。
行が有効と見なされる期間を指定するには、BETWEEN
句を使用します。どちらの式も、評価結果はタイムスタンプ値でなければならず、評価結果がNULLであってはなりません。MINVALUE
は、table
の開始時間列に含まれる最も早い日付またはタイムスタンプに解決されます。MAXVALUE
は、table
の終了時間列に含まれる最も遅い日付またはタイムスタンプに解決されます。
AS OF PERIOD FOR AS
OF
PERIOD
FOR
を指定すると、特定の時点で有効と見なされるかどうかに基づいて、table
から行を取り出すことができます。この句を使用するには、table
で時制有効性がサポートされている必要があります。
valid_time_column
には、table
の有効期間ディメンション列の名前を指定します。
行が有効と見なされる時点を指定するには、expr
を使用します。この式の評価結果はタイムスタンプ値でなければならず、評価結果がNULLであってはなりません。
関連項目:
|
フラッシュバック問合せの注意事項 Oracle Databaseは、フラッシュバック問合せの実行時、他のタイプの問合せとは異なり、問合せ最適化を使用しない場合があります。ここで問合せ最適化を使用すると、パフォーマンスが低下する可能性があります。これは特に、階層問合せに複数のフラッシュバック問合せを指定した場合に発生します。
フラッシュバック問合せの制限事項: この問合せには、次の制限事項があります。
列式や副問合せをAS
OF
句の式で指定することはできません。
AS
OF
句を指定する場合、for_update_clause
句は指定できません。
AS
OF
句は、マテリアライズド・ビューを定義する問合せの中では使用できません。
一時表、外部表またはクラスタの一部である表に対するフラッシュバック問合せでは、VERSIONS
句を使用できません。
ビューに対するフラッシュバック問合せではVERSIONS
句を使用できません。ただし、ビューを定義する問合せには、VERSIONS
構文を使用できます。
すでにquery_name
がquery_table_expression
の中で指定されている場合は、flashback_query_clause
は指定できません。
関連項目:
|
partition_extension_clause データを取り出す表
のPARTITION
またはSUBPARTITION
の名前を指定します。
レンジ・パーティション・データおよびリスト・パーティション・データでは、この句のかわりに、データの取出しをtable
の1つ以上のパーティションに制限する条件をWHERE
句に指定できます。Oracle Databaseは、この条件を解析して、そのパーティションからのデータのみをフェッチします。そのようなWHERE
条件を、ハッシュ・パーティション・データに対して形成することは不可能です。
dblink 表、ビューまたはマテリアライズド・ビューが存在するリモート・データベースのデータベース・リンクの完全名または部分名を指定します。このデータベースは、Oracle Databaseである必要はありません。
関連項目:
|
dblink
を指定しない場合、その表、ビューまたはマテリアライズド・ビューは、ローカル・データベース内にあるものとみなされます。
データベース・リンクの制限事項: データベース・リンクには、次の制限事項があります。
リモート表のユーザー定義型またはオブジェクトREF
を問い合せることはできません。
リモート表のANYTYPE
型、ANYDATA
型またはANYDATASET
型の列を問い合せることはできません。
table | view | materialized view データを選択する表、ビューまたはマテリアライズド・ビューの名前を指定します。
sample_clause
を指定すると、表全体からではなく、表のランダムなサンプル・データから選択が行われます。
BLOCK BLOCK
を指定すると、ランダムな行サンプリングのかわりに、ランダムなブロック・サンプリングを実行できます。
ブロック・サンプリングは、全表スキャン中または高速全索引スキャン中にのみ使用可能です。より効率的な実行パスが存在する場合、ブロック・サンプリングは実行されません。特定の表または索引に対するブロック・サンプリングを確実に実行する場合は、FULL
またはINDEX_FFS
のヒントを使用します。
sample_percent sample_percent
には、全体の行またはブロック数のうち、サンプルに入れる割合(%)を指定します。0.000001以上100未満の範囲の値を指定します。この割合は、各行(ブロック・サンプリングの場合は行の各クラスタ)が、サンプルの一部として選択される可能性を示します。sample_percent
に指定した割合の行が表から正確に取り出されるわけではありません。
注意: 統計的に適切でない想定値でこの機能を使用した場合、不正確な、または望ましくない結果になります。 |
SEED seed_value この句を指定すると、今回の実行と次の実行で同じサンプルが戻されます。seed_value
には、0(ゼロ)から4294967295の整数を指定します。この句を省略した場合、戻されるサンプルは実行ごとに異なります。
sample_clauseの制限事項 SAMPLE
句には、次の制限事項が適用されます。
SAMPLE
句は、DML文の副問合せの中では指定できません。
SAMPLE
句を問合せで指定できるのは、問合せの対象が実表、マテリアライズド・ビューのコンテナ表、またはキー保存であるビューである場合です。この句は、キー保存ではないビューに対しては指定できません。
subquery_restriction_clause subquery_restriction_clause
を使用すると、次のいずれかの方法で副問合せを制限できます。
WITH READ ONLY WITH READ ONLY
を指定すると、表またはビューを更新禁止にできます。
WITH CHECK OPTION WITH CHECK OPTION
を指定すると、副問合せに含まれない行を生成する表またはビューの変更を禁止できます。この句をDML文の副問合せ内で使用する場合、FROM
句内の副問合せには指定できますが、WHERE
句内の副問合せには指定できません。
CONSTRAINT constraint CHECK OPTION
制約の名前を指定します。この識別子を省略した場合は、Oracleによって自動的にSYS_C
n
という形式の制約名が割り当てられます(nはデータベース内で制約名を一意にするための整数)。
table_collection_expression
を使用すると、問合せおよびDML操作で、collection_expression
値を表として扱うことができます。collection_expression
には、副問合せ、列、ファンクションまたはコレクション・コンストラクタのいずれかを指定できます。その形式にかかわらず、集合値(ネストした表型またはVARRAY型の値)を戻す必要があります。このようなコレクションの要素抽出プロセスをコレクション・ネスト解除といいます。
TABLE
コレクション式を親表と結合する場合は、オプションのプラス(+)には大きな意味があります。+を指定すると、その2つの外部結合が作成され、コレクション式がNULLの場合でも、外部表の行が問合せで戻されるようになります。
注意: 以前のリリースのOracleでは、collection_expression が副問合せの場合、table_collection_expression をTHE subquery と表現していました。現在、このような表現方法は非推奨になっています。 |
collection_expression
は、FROM
句で左側に定義された表の列を参照できます。これを左相関といいます。左相関はtable_collection_expression
のみで行われます。その他の副問合せは、その副問合せ以外で定義された列を参照することはできません。
オプションの(+)
を使用すると、コレクションがNULLまたは空である場合、すべてのフィールドにNULLが設定された行をtable_collection_expression
が戻すように指定できます。この(+)
はcollection_expression
が左相関を使用する場合にのみ有効です。結果は、外部結合の結果と似ています。
UPDATE
またはDELETE
操作で副問合せのWHERE
句に(+)
構文を使用する場合は、副問合せのFROM
句に2つの表を指定する必要があります。副問合せに結合が存在しないかぎり、外部結合構文は無視されます。
相関名(表、ビュー、マテリアライズド・ビューまたは問合せを評価するための副問合せの別名)を指定します。SELECT構文のリストがオブジェクト型属性またはオブジェクト型メソッドを参照する場合、この別名が必要になります。相関名は、相関問合せ内で最も頻繁に使用されます。表、ビューまたはマテリアライズド・ビューを参照する問合せでは、この別名を参照する必要があります。
pivot_clause
を使用すると、行を列に変換し、変換処理中にデータを集計するクロス集計問合せを記述できます。ピボット演算の出力には、最初のデータセットよりも多くの列と少ない行が含まれています。pivot_clause
では、次の手順が実行されます。
pivot_clause
の先頭で指定されている集計ファンクションが計算されます。集計ファンクションは、複数の値を戻すようにGROUP
BY
句を指定する必要がありますが、pivot_clause
には、明示的なGROUP
BY
句が含まれていません。かわりに、暗黙的な
GROUP
BY
が実行されます。暗黙的なグループ化は、pivot_clause
で参照されていないすべての列、およびpivot_in_clause
で指定されている値セットに基づいています。複数の集計ファンクションを指定する場合、集計ファンクションの1つを除くすべてに別名を指定する必要があります。
列のグループ化および手順1で計算された集計値は、次のクロス集計出力を生成するように構成されています。
最初に、pivot_clause
で参照されていないすべての暗黙的なグループ化列が出力されます。
pivot_in_clause
の値に対応する新しい列。各集計値がクロス集計の適切な新しい列に移動します。XML
キーワードを指定した場合は、結果は新しい列1つだけとなり、データは1つのXML文字列として表現されます。データベースは、新しい列ごとに名前を生成します。集計ファンクションの別名を指定しない場合、データベースは、集計値を移動する新しい列ごとの名前としてピボット列値を使用します。集計ファンクションの別名を指定する場合、データベースは、ピボット列名、アンダースコア(_)、集計ファンクション別名を連結して集計値を移動する新しい列ごとの名前を生成します。生成された列名が列名の最大長を超える場合、ORA-00918エラーが戻されます。この問題を回避するには、ピボット列見出しまたは集計ファンクションあるいはその両方に短い別名を指定します。
pivot_clause
の副次句のセマンティクスは、次のとおりです。
XML オプションのXML
キーワードは、問合せのXML出力を生成します。XML
キーワードを指定すると、pivot_in_clause
には、副問合せまたはワイルド・カード・キーワードANY
を含めることができます。副問合せおよびANY
ワイルド・カードは、pivot_in_clause
値が事前にわかっていない場合に有効です。XML出力では、ピボット列の値が実行時に評価されます。pivot_in_clause
で式を使用して明示的なピボット値を指定する場合は、XML
を指定することができません。
XML出力が生成される際、集計ファンクションが各ピボット値に適用され、データベースによって、値とメジャーのすべてのペアのXML文字列を含むXMLType
の列が戻されます。
expr ピボット列の定数値への評価を行う式を指定します。オプションで、各ピボット列値の別名を指定できます。別名がない場合は、列ヘッダーが引用識別子となります。
subquery subqueryは、XML
キーワードとともにのみ使用されます。subqueryを指定すると、subqueryによって検出されたすべての値がピボットに使用されます。出力は、XML以外のピボット問合せによって戻されるクロス集計書式とは異なります。pivot_in_clause
で指定されている複数の列のかわりに、subqueryでは、XML文字列の列が1つ生成されます。各行のXML文字列は、その行の暗黙的なGROUP
BY
値に対応する集計データを保持します。入力データに対応する行がない場合でも、各出力行のXML文字列には、subqueryによって検出されたすべてのピボット値が含まれています。
subqueryは、ピボット問合せの実行時に、一意の値リストを戻します。subqueryが一意の値を戻さない場合、Oracle Databaseによってランタイム・エラーが生成されます。問合せが一意の値を戻すかどうかがわからない場合は、subqueryにDISTINCT
キーワードを使用します。
ANY ANY
キーワードは、XML
キーワードとともにのみ使用されます。ANY
キーワードは、ワイルド・カードとして機能し、subquery
と同様に動作します。出力は、XML以外のピボット問合せによって戻されるクロス集計書式とは異なります。pivot_in_clause
で指定されている複数の列のかわりに、ANY
キーワードでは、XML文字列の列が1つ生成されます。各行のXML文字列は、その行の暗黙的なGROUP
BY
値に対応する集計データを保持します。ただし、subquery
を指定した場合と比較すると、ANY
ワイルド・カードでは、各出力行について、行に対応する入力データで検出されたピボット値のみを含むXML文字列が生成されます。
unpivot_clause
は、列を行に変換します。
INCLUDE
| EXCLUDE
NULLS
句を使用すると、NULL値の行を含めるか除外するかを選択できます。INCLUDE
NULLS
を指定すると、NULL値の行もアンピボット操作の対象となり、EXCLUDE
NULLS
を指定するとNULL値の行は戻り値のセットから除外されます。この句を省略した場合は、アンピボット操作からNULLが除外されます。
column
には、sales_quantity
などのメジャー値を保持する各出力列の名前を指定します。
pivot_for_clause
には、四半期または製品などの記述子値を保持する各出力列の名前を指定します。
unpivot_in_clause
には、名前がpivot_for_clause
の出力列の値となる入力データ列を指定します。これらの入力データ列には、Q1、Q2、Q3、Q4など、カテゴリ値を指定する名前が含まれています。任意指定のAS
句を使用すると、入力データ列名を、出力列内の指定したliteral
値にマッピングできます。
アンピボット操作は、複数の値列を単一の列に変更します。このため、値列のすべてのデータ型は、数値、文字などの同じデータ型グループに属している必要があります。
すべての値列がCHAR
の場合、アンピボットされる列はCHAR
になります。値列がVARCHAR2
の場合、アンピボットされる列はVARCHAR2
になります。
すべての値列がNUMBER
の場合、アンピボットされる列はNUMBER
になります。値列がBINARY_DOUBLE
の場合、アンピボットされる列はBINARY_DOUBLE
になります。BINARY_DOUBLE
の値列はないが、いずれかの値列がBINARY_FLOAT
の場合、アンピボットされる列はBINARY_FLOAT
になります。
containers_clause
CONTAINERS
句は、マルチテナント・コンテナ・データベース(CDB)で有益です。この句を使用すると、CDBのすべてのコンテナ間で指定された表またはビューのデータを問い合せることができます。
この句を発行するには、ルートに接続された共通ユーザーであり、表またはビューがルートおよびすべてのPDBに存在し、表またはビューが自分のスキーマ内にある必要があります。schema
を指定する必要はありませんが、指定した場合は自分のスキーマを指定する必要があります。
問合せは、ルートおよびすべてのオープン状態になっているPDB (RESTRICTED
モードでオープン状態になっているPDBは除きます)の表またはビューのすべての行を戻します。問合せ対象の表またはビューにCON_ID
列が含まれていない場合、問合せはCON_ID
列を問合せ結果に追加し、与えられた行が表すデータがあるコンテナを識別します。
関連項目: CONTAINERS句の詳細は、『Oracle Database管理者ガイド』 を参照してください。 |
適切なjoin_clause
構文を使用すると、データが選択され、結合の一部となる表を識別できます。inner_cross_join_clause
を使用すると、内部結合またはクロス結合を指定できます。outer_join_clause
を使用すると、外部結合を指定できます。cross_outer_apply_clause
を使用すると、ANSI CROSS
JOIN
または左相関のサポートを利用したANSI LEFT
OUTER
JOIN
を指定できます。
結合する行ソースが3つ以上ある場合は、カッコを使用してデフォルトの優先順位を無効にすることができます。たとえば、次のような構文があるとします。
SELECT ... FROM a JOIN (b JOIN c) ...
この場合、b
とc
が結合され、次にその結果とa
が結合されます。
inner_cross_join_clause
内部結合は、結合条件を満たす行のみを戻します。
INNER INNER
を指定すると、内部結合を明示的に指定できます。
JOIN JOIN
キーワードを使用すると、結合の実行を明示的に示すことができます。この構文を使用すると、WHERE
句の結合で使用されている、カンマで区切られた表の式を、FROM
句の結合構文に置き換えることができます。
ON condition ON
句を使用すると、結合条件を指定できます。これによって、WHERE
句の検索またはフィルタ条件とは分離して結合条件を指定できます。
USING (column) 両方の表で同じ名前の列同士を等価結合する場合、USING
column
句に使用する列を指定します。両方の表で同じ名前の列同士を結合する場合のみ、この句を使用できます。この句の中では、列名を表の名前および別名で修飾しないでください。
CROSS CROSS
キーワードは、クロス結合を実行することを示します。クロス結合とは、2つの関係(リレーション)のクロス積を生成するものであり、実質的にはカンマ区切りのOracle Database表記法と同じです。
NATURAL NATURAL
キーワードは、自然結合を実行することを示します。この句のセマンティクスの詳細は、「NATURAL」を参照してください。
outer_join_clause
外部結合は、結合条件を満たすすべての行と、結合条件を満たす他方の表の行を除いた、一方の表のすべての行を戻します。指定可能な外部結合は、結合の両側にtable_reference
構文を使用した従来の外部結合と、いずれかの側にquery_partition_clause
を使用したパーティション化された外部結合の2種類です。パーティション化された外部結合は、内部表の各パーティションと外部表の間で結合が行われるという点を除いて、従来の外部結合と同じです。この形式の結合では、対象のディメンションに沿って、選択的に疎データをより密にできます。このプロセスはデータの稠密化といいます。
query_partition_clause query_partition_clause
を使用すると、パーティション化された外部結合を定義できます。このような結合は、問合せによって戻されたパーティションに外部結合を適用し、従来の外部結合構文を拡張します。PARTITION
BY
句で指定した各式に対する行のパーティションが作成されます。問合せの各パーティションの行は、PARTITION
BY
式に対して同じ値を持ちます。
query_partition_clause
は、外部結合のいずれかの側で使用できます。パーティション化された外部結合の結果は、パーティション化された結果セットの各パーティションと結合の反対側の表との外部結合のUNION
になります。この形式の結果は、疎データの欠損の補完に役立つため、分析計算が簡単になります。
この句を省略した場合、表の式全体(table_reference
に指定したすべてのもの)が単一のパーティションとして扱われるため、従来型の外部結合となります。
分析ファンクションでquery_partition_clause
を使用するには、構文の上位ブランチ(カッコなし)を使用します。この句をモデルの問合せ(model_column_clauses
内)またはパーティション化された外部結合(outer_join_clause
内)で使用するには、構文の下位ブランチ(カッコ付き)を使用します。
パーティション化された外部結合の制限事項: パーティション化された外部結合には、次の制限事項があります。
query_partition_clause
は、結合の右側または左側に指定できますが、両方に指定することはできません。
パーティション化された完全外部結合(FULL
)は指定できません。
ON句を使用して外部結合に
query_partition_clauseを指定した場合、
ON
条件内には副問合せを指定できません。
NATURAL NATURAL
キーワードは、自然結合を実行することを示します。自然結合は、2つの表の間で同じ名前のすべての列に基づきます。2つの表から関連する列の値が等しい行が選択されます。同じ名前の2つの列のデータ型の間に互換性がない場合は、エラーが発生します。自然結合で使用する列を指定する場合は、表の名前または別名で列名を修飾しないでください。
自然結合またはクロス結合の表の組合せが不明瞭な場合があります。たとえば、次のような結合構文があるとします。
a NATURAL LEFT JOIN b LEFT JOIN c ON b.c1 = c.c1
この例は、次のどちらにも解釈できます。
a NATURAL LEFT JOIN (b LEFT JOIN c ON b.c1 = c.c1) (a NATURAL LEFT JOIN b) LEFT JOIN c ON b.c1 = c.c1
このような不明瞭さをなくすため、カッコを使用して結合する表の組合せを明確にしてください。このようなカッコがないと、左から右へ表が組み合せられ、左の結合が使用されます。
自然結合の制限事項: LOB列、ANYTYPE
列、ANYDATA
列、ANYDATASET
列またはコレクション列は、自然結合の一部として指定できません。
outer_join_type 実行する外部結合の種類を指定します。
RIGHT
を指定すると、右側外部結合が実行されます。
LEFT
を指定すると、左側外部結合が実行されます。
FULL
を指定すると、完全な外部結合または両側外部結合が実行されます。内部結合に加え、内部結合の結果に戻されない両方の表からの行は、保持され、NULLで拡張されます。
RIGHT
、LEFT
またはFULL
の後にオプションのOUTER
キーワードを指定し、外部結合の実行を明示的に示すことができます。
ON condition ON
句を使用すると、結合条件を指定できます。これによって、WHERE
句の検索またはフィルタ条件とは分離して結合条件を指定できます。
ON condition句の制限事項: NATURAL
外部結合を使用してこの句を指定することはできません。
USING column USING
句を含む外部結合の場合、問合せによって単一列が戻されます。この単一列は、結合内の一致する2つの列が結合したものです。この結合(coalesce)は、次のように機能します。
COALESCE (a, b) = a if a NOT NULL, else b.
したがって、次のことがいえます。
左側外部結合では、FROM
句内の左側の表から共通するすべての列値が戻されます。
右側外部結合では、FROM
句内の右側の表から共通するすべての列値が戻されます。
完全な外部結合では、結合された両方の表から共通するすべての列値が戻されます。
USING column句の制限事項: USING
column
句には、次の制限事項があります。
この句の中では、列名を表の名前および別名で修飾しないでください。
LOB列またはコレクション列は、USING
column
句で指定できません。
NATURAL
外部結合を使用してこの句を指定することはできません。
関連項目:
|
cross_outer_apply_clause
この句を使用すると、ANSI CROSS
JOIN
または左相関のサポートを利用したANSI LEFT
OUTER
JOIN
を実行できます。APPLYキーワードの右側には、
table_referenceまたは
collection_expressionを指定できます。
table_reference
には、表、インライン・ビューまたはTABLE
コレクション式を指定できます。collection_expression
には、副問合せ、列、ファンクションまたはコレクション・コンストラクタのいずれかを指定できます。その形式にかかわらず、集合値(ネストした表型またはVARRAY型の値)を戻す必要があります。table_reference
またはcollection_expression
は、FROM
句でAPPLY
キーワードの左側に定義された表の列を参照できます。これを左相関といいます。
CROSS
APPLY
を指定すると、ANSI CROSS
JOIN
を実行できます。結合の左側にある表の行のうち、table_reference
またはcollection_expression
から結果セットを生成する行のみが返されます。
OUTER
APPLY
を指定すると、ANSI LEFT
OUTER
JOIN
を実行できます。結合の左側にある表の行がすべて返されます。table_reference
またはcollection_expression
から結果を生成しない行は、対応する列内でNULL値を保持します。
cross_outer_apply_clauseの制限事項 table_reference
には、LATERALインライン・ビューを指定できません。
WHERE
条件を指定すると、選択する行を1つ以上の条件を満たす行のみに制限できます。condition
には、有効なSQL条件を指定します。
この句を省略した場合、FROM
句に指定されている表、ビューまたはマテリアライズド・ビューのすべての行が戻されます。
注意: この句がパーティション表またはパーティション索引のDATE 列を参照している場合、データベースは、次の条件でのみパーティション・プルーニングを実行します。
|
hierarchical_query_clause
を使用すると、階層順序で行を選択できます。
階層問合せを含むSELECT
文では、SELECT構文のリスト内のLEVEL
疑似列を使用できます。LEVEL
は、ルート・ノードには1を、ルート・ノードの子であるノードには2を、孫であるノードには3を戻します(以下同様)。階層問合せによって戻されるレベルの数値は、使用可能なユーザー・メモリーによって制限されます。
Oracleは次のように階層問合せを処理します。
最初に、結合(指定されている場合)が、FROM
句で指定されているか、またはWHERE
句述語で指定されているかが評価されます。
CONNECT
BY
条件が評価されます。
残りのWHERE
句述語が評価されます。
この句を指定する場合、ORDER
BY
およびGROUP
BY
を指定すると、CONNECT
BY
結果の階層順序が破棄されるため、これらの句のどちらも指定しないでください。同じ親の兄弟である行を順序付ける場合は、ORDER
SIBLINGS
BY
句を使用します。
階層問合せのルートとして使用される行を識別する場合の条件を指定します。condition
には、第6章「条件」で説明した任意の条件を使用できます。Oracle Databaseでは、この条件を満たすすべての行がルートとして使用されます。この句を省略した場合、表内のすべての行がルート行として使用されます。
階層の親/子の行の関連を識別する条件を指定します。condition
には、第6章「条件」で説明した任意の条件を使用できます。ただし、親である行を参照するためのPRIOR
演算子を使用する必要があります。
GROUP
BY
句を指定すると、選択した行を各行のexpr
の値に基づいてグループ化し、各グループのサマリー情報を1行戻すことができます。この句にCUBE
またはROLLUP
拡張要素を指定した場合、標準グループ化の他に超集合グループ化が生成されます。
GROUP
BY
句の式には、SELECT構文のリストに指定されている列であるかどうかにかかわらず、FROM
句の表、ビューおよびマテリアライズド・ビューの列を指定できます。
GROUP
BY
句は行をグループ化しますが、結果セットの順序は保証しません。グループ化の順序付けを行うには、ORDER
BY
句を使用します。
NLS_SORT
パラメータがBINARY
以外に設定され、NLS_COMP
パラメータがLINGUISTIC
に設定されている場合、NLS_SORT
で指定される言語定義に従って式の値が言語的に比較されて、値が等しく同じグループに属するかどうかが判断されます。GROUP
BY
の文字値が言語的に比較される場合、文字値はまず照合キーに変換されてから、RAW
値のように比較されます。照合キーは、NLSSORT
ファンクションによって戻されるものと同じ値であり、「NLSSORT」で説明されているものと同じ制限事項があります。これらの制限事項により、照合キーの生成に使用された接頭辞に違いがない場合、その他の値に違いがあるとしても、2つの値は言語的に等しいものとしてグループ化されることがあります。
関連項目:
|
ROLLUP simple_grouping_clause
のROLLUP
操作を使用すると、選択した行をGROUP
BY
で指定した式n、n-1、n-2、... 0の最初の値に基づいてグループ化し、各グループのサマリー情報を1行戻すことができます。ROLLUP
操作をSUMファンクションとともに使用すると、小計値
を出力できます。ROLLUP
をSUM
とともに使用すると、最も詳細なレベルの小計から総計までが生成されます。COUNT
などの集計ファンクションは、他の種類の超集合の出力に使用できます。
たとえば、simple_grouping_clause
のROLLUP
句に式を3つ指定した場合(n=3)、操作の結果はn+1=3+1=4グループになります。
最初のn
式の値でグループ化した行を標準行、その他を超集合行といいます。
関連項目: マテリアライズド・ビューでROLLUPを使用する場合の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』 を参照してください。 |
CUBE CUBE
操作をsimple_grouping_clause
の中で指定すると、選択された行は、指定された式のすべての可能な組合せの値に基づいてグループ化されます。グループごとに1つのサマリー情報行が戻されます。CUBE
操作を使用すると、クロス集計値を出力できます。
たとえば、simple_grouping_clause
のCUBE
句に式を3つ指定した場合(n=3)、操作の結果は2n = 23 = 8グループになります。n
式の値でグループ化した行を標準行、その他を超集合行といいます。
GROUPING SETS GROUPING
SETS
は、データを複数にグループ化するGROUP
BY
句をさらに拡張したものです。これによって、不要な集計が排除され、効率的に集計できるようになります。必要なグループを指定すると、データベースがCUBE
またはROLLUP
によって生成された集計のすべてを実行する必要がなくなります。GROUPING
SETS
句で指定したすべてのグループ化が計算され、UNION
ALL
操作で個々のグループ化の結果が組み合されます。UNION
ALL
は、結果セットが重複行を含むことを許可します。
GROUP
BY
句では、様々な方法で式を組み合せることができます。
複合列を指定するには、カッコで列をグループ化します。データベースは、ROLLUP
操作またはCUBE
操作の計算でこれらを1つの単位として処理します。
グルーピング・セットの連結を指定するには、複数のグルーピング・セット、ROLLUP
操作およびCUBE
操作をカンマで区切って指定すると、データベースによってこれらが結合されて1つのGROUP
BY
句になります。結果は、各グルーピング・セットからのグループ化のクロス積です。
HAVING
句を使用すると、指定したcondition
がTRUE
であるグループの行のみを戻すように制限できます。この句を省略した場合、すべてのグループのサマリー行が戻されます。
where_clause
および
hierarchical_query_clause
の後に、GROUP
BYおよびHAVING
を指定します。GROUP
BY
およびHAVING
句を両方指定する場合は、どちらを先に指定してもかまいません。
GROUP BY句の制限事項: この句には、次の制限事項があります。
LOB列、ネストした表またはVARRAYをexpr
の一部として指定できません。
式には、スカラー副問合せ式を除くすべての形式が可能です。
group_by_clause
がオブジェクト型列を参照する場合、問合せはパラレル化されません。
model_clause
を使用すると、選択した行を多次元配列として表示して、その配列内のセルにランダムにアクセスできます。model_clause
を使用すると、一連のセル割当て(ルールと呼ばれます)を指定しておき、このルールによって個々のセルやセル範囲に対する計算を実行できます。このルールの操作の対象は問合せの結果であり、データベース表が更新されることはありません。
問合せでmodel_clause
を使用する場合、SELECT
句およびORDER
BY
句は、model_column_clauses
で定義された列のみを参照する必要があります。
関連項目:
|
main_model
main_model
句を使用すると、選択した行を多次元配列内で表示する方法および配列内の各セルに適用するルールを定義できます。
model_column_clauses
model_column_clauses
を使用すると、問合せの列を、パーティション列、ディメンション列およびメジャー列の3つのグループに定義して分類できます。expr
には、列、定数、ホスト変数、単一行ファンクション、集計ファンクションまたはこれらを含む任意の式を指定できます。expr
が列の場合、列の別名(c_alias
)はオプションです。expr
が列でない場合、列の別名は必須です。列の別名を指定する場合、別名を使用してmodel_rules_clause
、SELECT
リストおよび問合せORDER
BY
句の列を参照する必要があります。
PARTITION BY PARTITION
BY
句を使用すると、選択した行を列の値に基づいてパーティションに分割するために使用する列を指定できます。
DIMENSION BY DIMENSION
BY
句を使用すると、パーティション内で行を識別する列を指定できます。ディメンション列およびパーティション列の値は、行のメジャー列に対する配列の索引として使用されます。
MEASURES MEASURES
句を使用すると、計算が実行可能な列を識別できます。個々の行のメジャー列は、パーティション列およびディメンション列の値を指定することによる参照および更新が可能なセルと同様に扱われます。
cell_reference_options
cell_reference_options
句を使用すると、ルールでNULLまたは値なしを処理する方法および列の一意性を制約する方法を指定できます。
IGNORE NAV IGNORE
NAV
を指定すると、指定したデータ型のNULLまたは値なしに対して、次の値が戻されます。
数値データ型: 0(ゼロ)
日時データ型: 01-JAN-2000
文字データ型: 空の文字列
その他のデータ型: NULL
KEEP NAV KEEP
NAV
を指定すると、NULLまたは値なしのセル値に対してNULLが戻されます。KEEP
NAV
はデフォルトです。
UNIQUE SINGLE REFERENCE UNIQUE
SINGLE
REFERENCE
を指定すると、問合せの結果セット全体ではなく、ルールの右側の単一セルの参照のみが一意性をチェックされます。
UNIQUE DIMENSION UNIQUE
DIMENSION
を指定すると、PARTITION
BY
およびDIMENSION
BY
で指定した列が、問合せに対する一意キーであるかどうかが確認されます。UNIQUE
DIMENSION
はデフォルトです。
model_rules_clause
model_rules_clause
を使用すると、更新するセルおよびこれらのセルを更新するルールを指定できます。オプションで、ルールを適用および処理する方法も指定できます。
各ルールは割当てを表し、左側と右側にわかれています。ルールの左側は、ルールの右側によって更新されるセルを識別します。ルールの右側は、ルールの左側で指定されたセルに割り当てられる値を評価します。
UPSERT ALL UPSERT
ALL
を使用すると、ルールの左側に位置参照と記号参照の両方があるルールに対してUPSERT
動作が可能になります。UPSERT
ALL
ルールが評価されると、次の手順が実行され、アップサートするセル参照のリストが作成されます。
セル参照のすべてのシンボリック述語を満たす既存のセルを検索します。
記号参照があるディメンションのみを使用して、これらのセルの異なるディメンション値の組合せを検索します。
位置参照によって指定されたディメンション値を持つ、これらの値の組合せのクロス積が実行されます。
UPSERT ALLのセマンティクスの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』
を参照してください。
UPSERT UPSERT
を指定すると、ルールの左側で参照されるセルが多次元配列内に存在している場合、セルにルールが適用され、多次元配列内に存在しないセルに対しては新しい行が挿入されます。UPSERT
動作は、ルールの左側で位置参照が使用され、単一セルが参照されている場合にのみ適用されます。UPSERT
はデフォルトです。位置参照および単一セル参照の詳細は、「cell_assignment」を参照してください。
UPDATE
およびUPSERT
は、個々のルールに同様に指定できます。特定のルールにUPDATE
またはUPSERT
のいずれかを指定した場合、その指定はRULES
句に指定したその他のオプションより優先されます。
UPSERT [ALL]およびUPDATEに関する注意: UPSERT ALL 、UPSERT またはUPDATE ルールに適切な条件が含まれていない場合は、別のタイプのルールに暗黙的に変換される場合があります。
|
UPDATE UPDATE
を指定するとルールの左側で参照されるセルが多次元配列内に存在している場合、そのセルにルールが適用されます。セルが存在しない場合、割当ては無視されます。
AUTOMATIC ORDER AUTOMATIC
ORDER
を指定すると、依存順序に基づいてルールが評価されます。この場合、セルには値が1回のみ割り当てられます。
SEQUENTIAL ORDER SEQUENTIAL
ORDER
を指定すると、表示されている順序でルールが評価されます。この場合、セルには値が複数回割り当てられます。SEQUENTIAL
ORDER
はデフォルトです。
ITERATE ... [UNTIL] ITERATE
... [UNTIL
]を使用すると、ルールを繰り返す回数を指定でき、さらに早期終了条件を指定することもできます。UNTIL
を囲むカッコの使用は任意です。
ITERATE
... [UNTIL
]を指定した場合、ルールは表示されている順序で評価されます。model_rules_clause
で
AUTOMATIC
ORDER
およびITERATE ... [UNTIL]
の両方が指定されている場合、エラーが戻されます。
cell_assignment
cell_assignment
句は、ルールの左側に使用し、更新する1つ以上のセルを指定します。単一セルを参照するcell_assignment
は、単一セル参照といいます。複数のセルが参照される場合は、複数セル参照といいます。
model_clause
で定義したすべてのディメンション列は、cell_assignment
句で修飾する必要があります。ディメンションは、記号参照または位置参照を使用して修飾できます。
記号参照は、dimension_column
=
constant
などのブール条件を使用して、単一のディメンション列を修飾します。位置参照では、DIMENSION
BY
句でディメンション列の位置が示されます。記号参照と位置参照の唯一の相違点は、NULLの処理です。
a[x=null,y=2000]
のような単一セルの記号参照を使用すると、x=null
がFALSE
と評価されるため、該当するセルは存在しません。ただし、a[null,2000]
のような単一セルの位置参照を使用すると、null = nullがTRUE
と評価されるため、x
がNULL、y
が2000のセルが該当します。単一セルの位置参照を使用すると、ディメンション列がNULLのセルを参照、更新および挿入できます。
ディメンション列の値を表す条件または式を指定するときは、記号参照と位置参照のどちらも使用できます。condition
に集計ファンクションやCV
ファンクションを含めることはできず、condition
が参照するのは単一のディメンション列でなければなりません。expr
に副問合せを含めることはできません。モデル式の詳細は、「モデル式」を参照してください。
single_column_for_loop
single_column_for_loop
句を使用すると、更新するセルの範囲を単一のディメンション列内で指定できます。
IN
句を使用すると、ディメンション列の値を値のリストまたは副問合せとして指定できます。subquery
を使用するときは、次の制限事項があります。
相関問合せは使用できません。
10,000を超える行を戻すことはできません。
WITH
句で定義された問合せを使用できません。
FROM
句を使用すると、ディメンション列の値の範囲を指定できます(範囲内の増分は不連続でもかまいません)。FROM
句を使用できるのは、列のデータ型が、加算および減算をサポートするものである場合のみです。INCREMENT
およびDECREMENT
の値は、正の値である必要があります。
オプションで、FROM
句内でLIKE
句を指定することができます。LIKE
句のpattern
は、単一のパターン一致文字%
を含む文字列です。この文字は、実行時にFROM
句の現在の増分値または減分値で置き換えられます。
FOR
ループで使用されるディメンション以外のすべてのディメンションが単一セル参照に関係する場合は、式で新しい行を挿入できます。FOR
ループによって生成されたディメンション値の組合せの数は、MODEL
句の行制限(10,000)の計算に含まれます。
multi_column_for_loop
multi_column_for_loop
句を使用すると、更新するセルの範囲を複数のディメンション列にまたがって指定できます。IN
句を使用すると、ディメンション列の値を複数の値のリストまたは副問合せとして指定できます。subquery
を使用するときは、次の制限事項があります。
相関問合せは使用できません。
10,000を超える行を戻すことはできません。
WITH
句で定義された問合せを使用できません。
FOR
ループで使用されるディメンション以外のすべてのディメンションが単一セル参照に関係する場合は、式で新しい行を挿入できます。FOR
ループによって生成されたディメンション値の組合せの数は、MODEL
句の行制限(10,000)の計算に含まれます。
関連項目: MODEL句でFORループを使用する方法の詳細は、 『Oracle Databaseデータ・ウェアハウス・ガイド』 を参照してください。 |
order_by_clause
ORDER
BY
句を使用すると、ルールの左側のセルを評価する順序を指定できます。expr
は、ディメンションまたはメジャー列に変換される必要があります。ORDER
BY
句を指定しない場合、DIMENSION
BY
句で指定した列の順序がデフォルトで使用されます。詳細は、「order_by_clause」を参照してください。
order_by_clause句の制限事項: モデル・ルールでORDER
BY
句を使用する場合には、次の制限があります。
model_clause
句の
order_by_clauseには、SIBLINGS
、position
またはc_alias
を指定できません。
モデル・ルールの左側にこの句を指定し、右側にFOR
ループも指定することはできません。
expr
ルールの右側で指定されているセルの値を表す式を指定します。expr
に副問合せを含めることはできません。モデル式の詳細は、「モデル式」を参照してください。
return_rows_clause
return_rows_clause
を使用すると、選択されたすべての行を戻すか、モデル・ルールによって更新された行のみを戻すかどうかを指定できます。ALL
はデフォルトです。
reference_model
reference_model
は、model_clause
内から複数の配列にアクセスする必要がある場合に使用します。この句は、問合せの結果に基づいて、読取り専用の多次元配列を定義します。
reference_model
句の副次句は、main_model
句と同じセマンティクスを持ちます。model_column_clausesおよびcell_reference_optionsを参照してください。
reference_model句の制限事項: この句には、次の制限事項があります。
PARTITION
BY
列を参照モデルに指定することはできません。
参照モデルの副問合せから外部副問合せの列を参照することはできません。
集合演算子: UNION、UNION ALL、INTERSECTおよびMINUS
集合演算子は、2つのSELECT
文によって戻された行を1つの結果に結合します。それぞれのコンポーネント問合せで選択される列の数とデータ型は同じである必要がありますが、列の長さは異なってもかまいません。結果セット内の列の名前は、集合演算子の前にあるSELECT構文のリスト内の式の名前です。
集合演算子で3つ以上の問合せを結合する場合、隣接する問合せが左から右へ評価されます。副問合せを囲むカッコは任意指定です。この評価順序を変更する場合、カッコを使用します。
これらの演算子の詳細および使用方法の制限事項については、「UNION [ALL]、INTERSECTおよびMINUS演算子」を参照してください。
ORDER
BY
句を使用すると、文によって戻された行を順序付けることができます。order_by_clause
を指定しない場合、同じ問合せで取り出される行の順序が異なることがあります。
SIBLINGS SIBLINGS
キーワードは、hierarchical_query_clause
(CONNECT
BY
)を指定する場合のみに有効です。ORDER
SIBLINGS
BY
は階層問合せ句で指定した任意の順序を保持し、兄弟関係にある階層にorder_by_clause
を適用します。
expr expr
を使用すると、expr
の値を基準にして行を順番付けることができます。式は、SELECT構文のリストの列、あるいはFROM
句の表、ビューまたはマテリアライズド・ビューの列に基づきます。
position position
を使用すると、SELECT構文のリストの指定した位置にある式の値に基づいて行を順序付けることができます。position
には整数を指定する必要があります。
order_by_clause
には複数の式を指定できます。この場合、まず、最初の式の値に基づいて行がソートされます。次に、最初の式と同じ値を持つ行が2番目の式の値に基づいてソートされる、というように処理が行われます。NULL値は昇順では最後に、降順では先頭にソートされます。問合せ結果の順位付けの詳細は、「問合せ結果のソート」を参照してください。
ASC | DESC 昇順か降順かを指定します。デフォルトはASC
です。
NULLS FIRST | NULLS LAST NULL値を含む戻された行が順序の最初にくるか、最後にくるかを指定します。
NULLS
LAST
は昇順のデフォルトで、NULLS
FIRST
は降順のデフォルトです。
ORDER BY句の制限事項: ORDER
BY
句には次の制限事項があります。
この文中でDISTINCT
演算子を指定した場合、SELECT構文のリストに指定された列でないかぎり、この句は列を参照することはできません。
order_by_clause
には最大255個の式を指定できます。
LOB列、LONG
列、LONG
RAW
列、ネストした表またはVARRAYを使用して順位付けすることはできません。
同じ文中でgroup_by_clauseを指定する場合、このorder_by_clause
は次の式に制限されます。
定数
集計ファンクション
分析ファンクション
USER
ファンクション、UID
ファンクションおよびSYSDATE
ファンクション
group_by_clause
に指定されているものと同じ式
グループ内のすべての行が同じ値に評価されるこれらの式を導出する式
row_limiting_clause
を使用すると、問合せから返される行数を制限できます。オフセットおよび戻される行の数または割合を指定できます。この句は、上位N番のレポートを実装するために使用できます。結果の一貫性を保つには、order_by_clause
を指定して、ソート順序を決定的にします。
OFFSET
この句を使用して、行制限が開始する前にスキップする行数を指定します。offset
は、数値または数値に評価される式にする必要があります。負の数値を指定すると、offset
は、0(ゼロ)とみなされます。NULLを指定した場合や、問合せから返される行数以上の数値を指定すると、返される行数は0(ゼロ)行になります。offset
に小数部が含まれているときには、小数部分が切り捨てられます。この句を省略すると、offset
は0(ゼロ)になり、行制限の開始が最初の行になります。
ROW | ROWS これらのキーワードは、区別なしに使用できますが、意味を明確にするために用意されています。
FETCH
この句を使用すると、返される行数または行の割合を指定できます。この句を指定しないと、offset
+ 1行目から始まるすべての行が返されます。
FIRST | NEXT これらのキーワードは、区別なしに使用できますが、意味を明確にするために用意されています。
rowcount | percent PERCENT rowcount
を使用すると、返される行の行数を指定できます。rowcount
は、数値または数値に評価される式にする必要があります。負の数値を指定すると、rowcount
は、0(ゼロ)とみなされます。rowcount
が、offset
+ 1行目から始まる使用可能な行数よりも大きいときには、すべての使用可能な行が返されます。rowcount
に小数部が含まれているときには、小数部分が切り捨てられます。rowcount
がNULLの場合、返される行数は0(ゼロ)行になります。
percent
PERCENT
を使用すると、選択された行の合計行数から、返される行数の割合を指定できます。percent
は、数値または数値に評価される式にする必要があります。負の数値を指定すると、percent
は、0(ゼロ)とみなされます。percent
がNULLの場合、返される行数は0(ゼロ)行になります。
rowcount
またはpercent
PERCENT
を指定しないと、返される行数は1行になります。
ROW | ROWS これらのキーワードは、区別なしに使用できますが、意味を明確にするために用意されています。
ONLY | WITH TIES ONLY
を指定すると、指定したとおりの行数または行数の割合が返されます。
WITH
TIES
を指定すると、最後にフェッチされた行と同じソート・キーを持つ追加の行が返されます。WITH
TIES
を指定するときには、order_by_clause
を指定する必要があります。order_by_clause
を指定しないと、追加の行は返されなくなります。
row_limiting_clauseの制限事項: この句には、次の制限事項が適用されます。
この句は、for_update_clause
と同時には指定できません。
この句を指定すると、順序疑似列のCURRVAL
またはNEXTVAL
を、選択リストに含めることができなくなります。
マテリアライズド・ビューは、それを定義する問合せにrow_limiting_clause
が含まれているときには、増分リフレッシュの対象にはなりません。
選択リストに同一の名前の列が含まれる場合にrow_limiting_clause
を指定すると、ORA-00918エラーが発生します。このエラーは、同一の名前の列が同じ表または別の表内のいずれにあっても発生します。この問題は、一意の列の別名を同一の名前の列に指定することで解決できます。
FOR
UPDATE
句を使用すると、選択した行がロックされ、トランザクションが終了するまでは他のユーザーがその行をロックまたは更新することはできなくなります。この句を指定できるのは、最上位のSELECT
文の中のみであり、副問合せでは指定できません。
注意: LOB値を更新する場合、そのLOBを含む行をロックしておく必要があります。行をロックする方法の1つに、埋込みSELECT ... FOR UPDATE 文があります。この場合、プログラム言語の1つまたはDBMS_LOB パッケージを使用します。LOBの書込み前に行う行のロックの詳細は、『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』を参照してください。 |
親表の行がロックされても、ネストした表の行はロックされません。ネストした表の行をロックする場合、ネストした表を明示的にロックする必要があります。
FOR UPDATE句の制限事項: この句には、次の制限事項があります。
この句をDISTINCT
演算子またはCURSOR
式、集合演算子、group_by_clause
、または集計ファンクションの構造体とともに指定することはできません。
この句がロックした表は、同じ文で参照されたLONG
列および順序と同じデータベース内にある必要があります。
ビューでのFOR UPDATE句の使用: 一般に、この句はビューではサポートされていません。ただし、ビューでSELECT
... FOR
UPDATE
問合せを実行してもエラーが発生しない場合もあります。これは、問合せオプティマイザによってビューが問合せブロックに内部的にマージされ、内部で変換された問合せに対してSELECT
... FOR
UPDATE
が行われる場合です。この項では、ビューに対してFOR
UPDATE
句を使用できる場合とできない場合の例を示します。
マージされたビューでのFOR
UPDATE
句の使用
次の条件が両方とも満たされている場合、マージされたビューに対してFOR
UPDATE
句を使用するとエラーが発生する可能性があります。
ビューの基礎となる列が式である
FOR
UPDATE
句が列リストに適用される
次の文は、ビューの基礎となる列が式ではないため、正常に実行されます。
SELECT employee_id FROM (SELECT * FROM employees) FOR UPDATE OF employee_id;
次の文は、ビューの基礎となる列は式ですが、FOR
UPDATE
句が列リストに適用されないため、正常に実行されます。
SELECT employee_id FROM (SELECT employee_id+1 AS employee_id FROM employees) FOR UPDATE;
次の文は、ビューの基礎となる列が式であり、FOR
UPDATE
句が列リストに適用されるため、失敗します。
SELECT employee_id FROM (SELECT employee_id+1 AS employee_id FROM employees) FOR UPDATE OF employee_id; * Error at line 2: ORA-01733: virtual column not allowed here
マージされていないビューでのFOR
UPDATE
句の使用
ビューではFOR
UPDATE
句がサポートされていないため、ビューのマージを回避する句(NO_MERGE
ヒントなど)、ビューのマージを禁止するパラメータ、またはビューのマージを回避する問合せ構造を使用すると、ORA-02014
エラーが発生します。
次の例では、GROUP
BY
文によってビューのマージが回避されるため、エラーが発生します。
SELECT avgsal FROM (SELECT AVG(salary) AS avgsal FROM employees GROUP BY job_id) FOR UPDATE; FROM (SELECT AVG(salary) AS avgsal FROM employees GROUP BY job_id) * ERROR at line 2: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
注意: ビューのマージのメカニズムは複雑であるため、ビューではFOR UPDATE 句を使用しないことをお薦めします。 |
OF ... column
OF
... column
句を使用すると、結合内の特定の表またはビューで選択された行のみをロックできます。OF
句の列は、どの表またはビューの行をロックするかを識別する場合にのみ使用します。指定する列は重要ではありません。ただし、列の別名ではなく、実際の列名を指定する必要があります。この句を省略した場合、問合せ内のすべての表の選択された行がロックされます。
NOWAIT | WAIT
NOWAIT
およびWAIT
句を使用すると、他のユーザーによってロックされている行をSELECT
文がロックしようとする場合に処理する方法をデータベースに指示できます。
NOWAIT
を指定すると、ロックされている場合に制御がすぐに戻ります。
WAIT
を指定すると、行が使用可能になるまでinteger
秒待機した後で制御が戻されます。
WAIT
およびNOWAIT
のどちらも指定しない場合、行が使用可能になるまで待機した後でSELECT
文の結果が戻されます。
SKIP LOCKED
SKIP
LOCKED
は、競合するトランザクションを処理するもう1つの方法であり、対象の行のうち一部をロックするというものです。SKIP
LOCKED
を指定すると、WHERE
句で指定した行のロックが試行され、他のトランザクションによってすでにロックされている行はスキップされます。この機能は、Oracle Streams Advanced Queuingなどのマルチコンシューマ・キュー環境で使用するために設計されています。キュー・コンシューマは、他のコンシューマによってロックされた行はスキップして未ロックの行を取得できるので、他のコンシューマの操作が終了するまで待つ必要はなくなります。SKIP
LOCKED
機能を直接使用するかわりに、Oracle Streams Advanced Queuing APIを使用することをお薦めします。詳細は、Oracle Databaseアドバンスド・キューイング・ユーザーズ・ガイドを参照してください。
WAIT
またはSKIP
LOCKED
を指定したときに排他モードで表がロックされていると、表のロックが解除されるまではSELECT
文の結果が戻りません。WAIT
では、指定されている待機時間にかかわらず、SELECT
FOR
UPDATE
句がブロックされます。
row_pattern_clause
MATCH_RECOGNIZE
句を使用すると、パターン一致を実行できます。この句は、table
(行パターン入力表といいます)に含まれる一連の行のパターンを認識するために使用します。MATCH_RECOGNIZE
句を使用する問合せの結果を行パターン出力表といいます。
MATCH_RECOGNIZE
により、次のタスクが実行可能になります。
PARTITION
BY
句およびORDER
BY
句が含まれるデータを、論理的にパーティション化し、順序付けます。
MEASURES
句で、SQL問合せの他のパートで使用可能な式であるメジャーを定義します。
PATTERN
句を使用して、シークする行のパターンを定義します。これらのパターンでは、正規表現構文が使用されます。これは、強力かつ表現力の豊かな機能であり、ユーザーが定義するパターン変数に適用されます。
行をDEFINE
句にある行パターン変数にマップするために必要な論理条件を指定します。
row_pattern_partition_by
PARTITION
BY
を指定すると、行パターン入力表に含まれる行を、行パターン・パーティションという論理グループに分割できます。column
を使用して、1つ以上のパーティション化列を指定します。各パーティションは、パーティション化列の値と同じ値が設定された、行パターン入力表に含まれる行のセットで構成されています。
この句を指定した場合、一致はパーティション内から検出され、他のパーティションからは検出されません。この句を指定しなかった場合、行入力表のすべての行から1つの行パターン・パーティションが構成されます。
row_pattern_order_by
ORDER
BY
を指定すると、各行パターン・パーティション内の行を順序付けできます。column
を使用して、順序付けする1つ以上の列を指定します。複数の列を指定した場合、Oracle Databaseはまず、最初のcolumnにおける行の値に基づいて、それらの行をソートします。次に、最初の列と同じ値を持つ行が2番目のcolumnの値に基づいてソートされる、というように処理が行われます。Oracle Databaseは、昇順でソートされた他のすべての値の後にNULLを配置します。
この句を指定しなかった場合、row_pattern_clause
の結果は非決定的になるため、問合せを実行するたびに異なる結果が返される場合があります。
row_pattern_measures
MEASURES
句を使用すると、1つ以上の行パターンのメジャー列を定義できます。行パターン出力表に含まれるこれらの列には、データ分析に役立つ値が含まれています。
row_pattern_measure_column
句を使用して行パターンのメジャー列を定義する場合、そのパターン・メジャー式を指定します。列内の値は、一致が見つかるたびにパターン・メジャー式を評価することによって計算されます。
row_pattern_measure_column この句を使用すると、行パターンのメジャー列を定義できます。
expr
にはパターン・メジャー式を指定します。第5章「式」で説明されているように、パターン・メジャー式には次の要素のみを含めることができます。
定数: テキスト・リテラルおよび数値リテラル
行パターン入力表に含まれる任意の列の参照
CLASSIFIER
関数。行のマップ先であるプライマリ行パターン変数の名前を返します。詳細は、「row_pattern_classifier_func」を参照してください。
MATCH_NUMBER
関数。行パターン・パーティション内の行パターン一致の連番を返します。詳細は、「row_pattern_match_num_func」を参照してください。
行パターンのナビゲーション関数: PREV
、NEXT
、FIRST
およびLAST
。詳細は、「row_pattern_navigation_func」を参照してください。
行パターン集計関数: AVG、COUNT、MAX、MINまたはSUM。詳細は、「row_pattern_aggregate_func」を参照してください。
c_alias
には、パターン・メジャー式の別名を指定します。Oracle Databaseは、行パターン出力表の列ヘッダーでこの別名を使用します。AS
キーワードはオプションです。別名は、SELECT
... ORDER
BY
句など、問合せのその他の部分で使用できます。
row_pattern_rows_per_match
この句を使用すると、行パターン出力表に、各一致のサマリー・データまたは詳細データを含めるかどうかを指定できます。
ONE
ROW
PER
MATCH
を指定すると、一致ごとに1つのサマリー行が生成されます。これはデフォルトです。
ALL
ROWS
PER
MATCH
を指定すると、一致に複数の行が含まれる場合、その一致に含まれる行ごとに1つの出力行が生成されます。
row_pattern_skip_to
この句を使用すると、空以外の一致が見つかった後の行パターン一致を再開する場所を指定できます。
AFTER
MATCH
SKIP
TO
NEXT
ROW
を指定すると、現在の一致の先頭行の次の行からパターン一致が再開されます。
AFTER
MATCH
SKIP
PAST
LAST
ROW
を指定すると、現在の一致の最終行の次の行からパターン一致が再開されます。これはデフォルトです。
AFTER
MATCH
SKIP
TO
FIRST
variable_name
を指定すると、パターン変数variable_name
にマップされた先頭行からパターン一致が再開されます。variable_name
はDEFINE
句で定義する必要があります。
AFTER
MATCH
SKIP
TO
LAST
variable_name
を指定すると、パターン変数variable_name
にマップされた最終行からパターン一致が再開されます。variable_name
はDEFINE
句で定義する必要があります。
AFTER
MATCH
SKIP
TO
variable_name
の動作は、AFTER
MATCH
SKIP
TO
LAST
variable_name
と同じです。
関連項目: AFTER MATCHSKIP 句の詳細は、 『Oracle Databaseデータ・ウェアハウス・ガイド』 を参照してください。 |
PATTERN
PATTERN
句を使用すると、一致する必要のあるパターン変数、そのパターン変数の一致順序、および各パターン変数に一致する必要のある行数を定義できます。
行パターン一致は、1つの行パターン・パーティション内で連続する行のセットで構成されます。一致の各行はそれぞれ1つのパターン変数にマップされます。行とパターン変数とのマッピングは、row_pattern
句に指定された正規表現に準拠している必要があり、DEFINE
句のすべての条件を満たす必要があります。
注意: 正規表現の概念と詳細についての説明は、このマニュアルでは対象外です。正規表現について十分に理解していない場合は、その他の資料を参照して理解しておくことをお薦めします。 |
PATTERNS
句の正規表現で指定した要素の優先順位は、降順で次のとおりです。
行パターン要素(row_pattern_primary
句で指定されます)
行パターン数量子(row_pattern_quantifier
句で指定されます)
連結(row_pattern_term
句で指定されます)
代替(row_pattern
句で指定されます)
関連項目: PATTERN句の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』 を参照してください。 |
row_pattern
この句を使用すると、行パターンを指定できます。行パターンは、次のいずれかの形式の正規表現です。
単一の行パターン用語
例: PATTERN(A)
行パターン、縦線および行パターン用語
例: PATTERN(A|B)
再帰的に構成された行パターン、縦線および行パターン用語
例: PATTERN(A|B|C)
この句内の縦線は、代替を表します。代替では、複数の使用可能な正規表現のリスト内の1つの正規表現に一致させます。代替項目は、指定された順序で優先されます。たとえば、PATTERN(A|B|C)
を指定した場合、Oracle DatabaseはまずA
との一致を試みます。A
が一致しない場合は、B
との一致を試みます。B
が一致しない場合は、C
との一致を試みます。
row_pattern_term
この句を使用すると、行パターン用語を指定できます。行パターン用語の形式は、次のいずれかです。
単一の行パターン・ファクタ
例: PATTERN(A)
行パターン用語の後に行パターン・ファクタが続く形式。
例: PATTERN(A B)
再帰的に構成された行パターン用語の後に行パターン・ファクタが続く形式
例: PATTERN(A B C)
2番目と3番目の例で使用されている構文は、連結を表します。連結は、一致の対象であるパターン内の2つ以上の項目と、それらの一致順序をリストするために使用します。たとえば、PATTERN(A B C)
を指定した場合、Oracle DatabaseはまずA
との一致を試みた後、一致した行を使用してB
との一致を試み、さらに一致した行を使用してC
との一致を試みます。A
、B
およびC
に一致した行のみが、行パターン一致に含まれます。
row_pattern_factor
この句を使用すると、行パターン・ファクタを指定できます。行パターン・ファクタは、row_pattern_primary
句を使用して指定された行パターン要素と、row_pattern_quantifier
句を使用して指定されたオプションの行パターン数量子で構成されます。
row_pattern_primary
この句を使用すると、行パターン要素を指定できます。表19-1に、有効な行パターン要素とその説明を示します。
表19-1 行パターン要素
行パターン要素 | 説明 |
---|---|
|
|
|
|
|
|
|
|
|
除外構文。 |
|
|
row_pattern_permute
PERMUTE
句を使用すると、指定された行パターン要素の順列であるパターンを表記できます。たとえば、PATTERN
(PERMUTE
(A,
B,
C))
は、次のように、3つの行パターン要素A
、B
およびC
のすべての順列の代替と同じです。
PATTERN (A B C | A C B | B A C | B C A | C A B | C B A)
行パターン要素は辞書編集上で展開され、並べ替える各要素は他の要素からカンマで区切る必要があります。
関連項目: 順列の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。 |
row_pattern_quantifier
この句を使用すると、行パターン数量子を指定できます。行パターン数量子は、一致で受け入れられる反復数を定義する後置演算子です。
行パターン数量子を強欲な数量子といい、適用されている正規表現で最大限のインスタンスの一致を試みます。例外は、接尾辞として疑問符(?
)を持つ行パターン数量子であり、これらを最短一致数量子といいます。これらは、適用されている正規表現で最小限のインスタンスの一致を試みます。
表19-2は、有効な行パターン数量子と、それらが一致で受け入れる反復数を示しています。この表のn
およびm
は、符号なし整数を表します。
表19-2 行パターン数量子
行パターン数量子 | 一致で受け入れられる反復数 |
---|---|
|
0以上の反復(強欲) |
|
0以上の反復(最短一致) |
|
1以上の反復(強欲) |
|
1以上の反復(最短一致) |
|
0または1個の反復(強欲) |
|
0または1個の反復(最短一致) |
|
|
|
|
|
|
|
|
|
0から |
|
0から |
|
|
関連項目: 行パターン数量子の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。 |
row_pattern_subset_clause
SUBSET
句を使用すると、1つ以上の共用体行パターン変数を指定できます。row_pattern_subset_item
句を使用して、各共用体行パターン変数を宣言します。
次の句で、共用体行パターン変数を指定できます。
MEASURES
句: 行パターンのメジャー列の式内。つまり、row_pattern_measure_column
句の式expr
内です。
DEFINE
句: プライマリ・パターン変数を定義する条件内。つまり、row_pattern_definition
句のcondition
内です。
row_pattern_subset_item
この句を使用すると、それ自身の変数名を使って参照可能な複数のパターン変数のグループ化を作成できます。このグループ化を参照する変数名を、共用体行パターン変数といいます。
等号の左側にあるvariable_name
には、共用体行パターン変数の名前を指定します。
等号の右側には、カッコで括られた個別のプライマリ行パターン変数のカンマ区切りリストを指定します。このリストに共用体行パターン変数を含めることはできません。
関連項目: 共用体行パターン変数の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。 |
DEFINE
DEFINE
句を使用すると、1つ以上の行パターン定義を指定できます。行パターン定義は、行を特定のパターン変数にマップするために満たしている必要のある条件を指定します。
DEFINE
句は実行中のセマンティクスのみをサポートします。
関連項目:
|
row_pattern_definition_list
この句を使用すると、1つ以上の行パターン定義を指定できます。
row_pattern_definition
この句を使用すると、行パターン定義を指定できます。行パターン定義には、行を特定のパターン変数にマップするために満たしている必要のある条件が含まれます。
variable_name
には、パターン変数の名前を指定します。
condition
には、第6章「条件」の説明に従って条件を指定しますが、次の追加事項があります。condition
には、row_pattern_navigation_func::=およびrow_pattern_aggregate_func::=によって記述された任意の関数を含めることができます。
row_pattern_rec_func
この句は、行パターン認識関数を指定できる、次の句で構成されます。
row_pattern_classifier_func
: この句を使用すると、CLASSIFIER
関数を指定できます。この関数は、行のマップ先であるパターン変数の名前を値として持つ文字列を返します。
row_pattern_match_num_func
: この句を使用すると、MATCH_NUMBER
関数を指定できます。この関数は、行パターン・パーティション内の一致の連番を表すスケール0(ゼロ)の数値を返します。
row_pattern_navigation_func
: この句を使用すると、行パターンのナビゲーション操作を実行する関数を指定できます。
row_pattern_aggregate_func
: この句を使用すると、行パターンのメジャー列の式、またはプライマリ・パターン変数を定義する条件で、集計関数を指定できます。
次の句で、行パターン認識関数を指定できます。
MEASURES
句: 行パターンのメジャー列の式内。つまり、row_pattern_measure_column
句の式expr
内です。
DEFINE
句: プライマリ・パターン変数を定義する条件内。つまり、row_pattern_definition
句のcondition
内です。
行パターン認識関数の動作は、この関数がMEASURES
句とDEFINE
句のどちらに指定されたかによって異なる場合があります。これらの詳細は、各句のセマンティクス内で説明されています。
row_pattern_classifier_func
CLASSIFIER
関数は、行のマップ先である変数の名前を値として持つ文字列を返します。
MEASURES
句の場合:
ONE
ROW
PER
MATCH
が指定された場合、問合せはMEASURES
句の処理時に一致の最後の行を使用するため、CLASSIFIER
関数は一致の最後の行のマップ先であるパターン変数の名前を返します。
ALL
ROWS
PER
MATCH
が指定された場合、CLASSIFIER
関数は、見つかった一致の行ごとに、行のマップ先であるパターン変数の名前を返します。
一致が空である場合、つまり一致に行が含まれない場合、CLASSIFER
関数はNULLを返します。
DEFINE
句では、CLASSIFIER
関数は現在行のマップ先であるプライマリ・パターン変数の名前を返します。
row_pattern_match_num_func
MATCH_NUMBER
関数は、行パターン・パーティション内の一致の連番を表すスケール0(ゼロ)の数値を返します。
1つの行パターン・パーティション内の一致には、見つかった順に1から順次番号が付けられます。複数の行が一致した場合は、それらすべてに同じ一致番号が割り当てられます。行パターン・パーティション間では順序付けが継承されないため、一致の番号付は行パターン・パーティションごとに1から繰り返し開始されます。
MEASURES
句内の場合: MATCH_NUMBER
を使用して、行パターン内の一致の連番を取得できます。
DEFINE
句内の場合: MATCH_NUMBER
を使用して、一致番号に依存する条件を定義できます。
row_pattern_navigation_func
この句を使用すると、次の行パターンのナビゲーション操作を実行できます。
row_pattern_nav_logical
句の
FIRSTおよびLAST
関数を使用して、パターン変数にマップされる行のグループ内をナビゲートします。
row_pattern_nav_physical
句の
PREVおよびNEXT
関数を使用して、行パターン・パーティションに含まれるすべての行間をナビゲートします。
row_pattern_nav_compound
句を使用して、
FIRST
またはLAST
関数をPREVまたはNEXT
関数内にネストします。
row_pattern_nav_logical
この句を使用すると、FIRST
およびLAST
関数でオプションの論理オフセットを使用することによって、パターン変数にマップされた行のグループ内をナビゲートできます。
FIRST
関数は、式expr
に指定されているパターン変数にマップされた行のグループの先頭行で評価されたときに、expr
の値を返します。パターン変数に行がマップされていない場合、FIRST
関数はNULLを返します。
LAST
関数は、式expr
に指定されているパターン変数にマップされた行のグループの最終行で評価されたときに、expr
の値を返します。パターン変数に行がマップされていない場合、LAST
関数はNULLを返します。
expr
は、評価の対象となる式を指定するために使用します。1つ以上の行パターンの列参照が含まれている必要があります。複数の行パターンの列参照が含まれている場合、それらはすべて、同じパターン変数を参照している必要があります。
オプションのoffset
は、パターン変数にマップされた行のセット内で論理オフセットを指定するために使用します。FIRST
関数とともに指定した場合、先頭行から昇順で数えた行数がオフセットになります。LAST
関数とともに指定した場合、最終行から降順で数えた行数がオフセットになります。デフォルトのオフセットは0です。
offset
には、負以外の整数を指定します。列や副問合せではなく、ランタイム定数(リテラル、バインド変数またはそれらを含む式)である必要があります。
パターン変数にマップされた行の数から1を引いた値以上の値をoffset
に指定した場合、この関数はNULLを返します。
FIRST
およびLAST
関数には、次のように実行中のセマンティクスまたは最終セマンティクスを指定できます。
MEASURES
句は、実行中のセマンティクスと最終セマンティクスをサポートします。実行中のセマンティクスは、RUNNING
によって指定できます。最終セマンティクスは、FINAL
によって指定できます。デフォルトはRUNNING
です。
DEFINE
句は、実行中のセマンティクスのみをサポートします。そのため、RUNNING
を指定したか省略したかにかかわらず、実行中のセマンティクスが使用されます。FINAL
は指定できません。
関連項目:
|
row_pattern_nav_physical
この句を使用すると、PREV
およびNEXT
関数でオプションの物理オフセットを使用することによって、行パターン・パーティション内のすべての行間をナビゲートできます。
PREV
関数は、パーティション内の前の行で評価されたときに、式expr
の値を返します。パーティション内に前の行がない場合、PREV
関数はNULLを返します。
NEXT
関数は、パーティション内の次の行で評価されたときに、式expr
の値を返します。パーティション内に次の行がない場合、NEXT関数はNULLを返します。
expr
は、評価の対象となる式を指定するために使用します。1つ以上の行パターンの列参照が含まれている必要があります。複数の行パターンの列参照が含まれている場合、それらはすべて、同じパターン変数を参照している必要があります。
オプションのoffset
は、パーティション内の物理オフセットを指定するために使用します。PREV
関数とともに指定した場合、現在の行の前にある行数を表します。NEXT
関数とともに指定した場合、現在の行の後にある行数を表します。デフォルトは1です。オフセットを0に指定した場合、現在の行が評価されます。
offset
には、負以外の整数を指定します。列や副問合せではなく、ランタイム定数(リテラル、バインド変数またはそれらを含む式)である必要があります。
PREV
およびNEXT
関数は、常に実行中のセマンティクスを使用します。したがって、この句にはRUNNING
またはFINAL
キーワードを指定できません。
関連項目:
|
row_pattern_nav_compound
この句を使用すると、row_pattern_nav_logical
句をrow_pattern_nav_physical
句内にネストできます。つまり、FIRST
またはLAST
関数をPREV
またはNEXT
関数内にネストできます。まずrow_pattern_nav_logical
が評価された後、その結果がrow_pattern_nav_physical
句に渡されます。
これらの句のセマンティクスの詳細は、「row_pattern_nav_logical」および「row_pattern_nav_physical」を参照してください。
関連項目: FIRSTおよびLAST関数のPREV およびNEXT 関数内へのネストの詳細は、 『Oracle Databaseデータ・ウェアハウス・ガイド』 を参照してください。 |
row_pattern_aggregate_func
この句を使用すると、行パターンのメジャー列の式、またはプライマリ・パターン変数を定義する条件で、集計関数を使用できます。
aggregate_function
には、関数AVG、COUNT、MAX、MIN、SUMのいずれかを指定します。DISTINCT
キーワードはサポートされていません。
集計関数には、次のように実行中のセマンティクスまたは最終セマンティクスを指定できます。
MEASURES
句は、実行中のセマンティクスと最終セマンティクスをサポートします。実行中のセマンティクスは、RUNNING
によって指定できます。最終セマンティクスは、FINAL
によって指定できます。デフォルトはRUNNING
です。
DEFINE
句は、実行中のセマンティクスのみをサポートします。そのため、RUNNING
を指定したか省略したかにかかわらず、実行中のセマンティクスが使用されます。FINAL
は指定できません。
関連項目:
|
例
「WITH句でのPL/SQLファンクションの使用例:」
次の例では、PL/SQLファンクションget_domain
をWITH
句で宣言および定義します。get_domain
ファンクションは、URL文字列に含まれるドメイン名を返します。このファンクションでは、URL文字列はドメイン名の直前に"www
"という接頭辞があり、ドメイン名は左右にあるドットで区切られていると仮定しています。SELECT
文ではget_domain
を使用して、oe
スキーマのorders
表から固有カタログのドメイン名を検出します。
WITH FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS pos BINARY_INTEGER; len BINARY_INTEGER; BEGIN pos := INSTR(url, 'www.'); len := INSTR(SUBSTR(url, pos + 4), '.') - 1; RETURN SUBSTR(url, pos + 4, len); END; SELECT DISTINCT get_domain(catalog_url) FROM product_information; /
副問合せのファクタリング例: 次の文は、結合を含む初期問合せブロックに対する問合せの名前dept_costs
およびavg_cost
を作成し、主問合せの本体でその問合せの名前を使用します。
WITH dept_costs AS ( SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name), avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name; DEPARTMENT_NAME DEPT_TOTAL ------------------------------ ---------- Sales 304500 Shipping 156400
再帰的副問合せのファクタリング例: 次の文は、従業員101に直接的または間接的に報告する従業員とそのレポート・レベルを表示します。
WITH reports_to_101 (eid, emp_last, mgr_id, reportLevel) AS ( SELECT employee_id, last_name, manager_id, 0 reportLevel FROM employees WHERE employee_id = 101 UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1 FROM reports_to_101 r, employees e WHERE r.eid = e.manager_id ) SELECT eid, emp_last, mgr_id, reportLevel FROM reports_to_101 ORDER BY reportLevel, eid; EID EMP_LAST MGR_ID REPORTLEVEL ---------- ------------------------- ---------- ----------- 101 Kochhar 100 0 108 Greenberg 101 1 200 Whalen 101 1 203 Mavris 101 1 204 Baer 101 1 205 Higgins 101 1 109 Faviet 108 2 110 Chen 108 2 111 Sciarra 108 2 112 Urman 108 2 113 Popp 108 2 206 Gietz 205 2
次の文は、従業員101に直接的または間接的に報告する従業員、レポート・レベル、および管理チェーンを表示します。
WITH reports_to_101 (eid, emp_last, mgr_id, reportLevel, mgr_list) AS ( SELECT employee_id, last_name, manager_id, 0 reportLevel, CAST(manager_id AS VARCHAR2(2000)) FROM employees WHERE employee_id = 101 UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1, CAST(mgr_list || ',' || manager_id AS VARCHAR2(2000)) FROM reports_to_101 r, employees e WHERE r.eid = e.manager_id ) SELECT eid, emp_last, mgr_id, reportLevel, mgr_list FROM reports_to_101 ORDER BY reportLevel, eid; EID EMP_LAST MGR_ID REPORTLEVEL MGR_LIST ---------- ------------------------- ---------- ----------- -------- 101 Kochhar 100 0 100 108 Greenberg 101 1 100,101 200 Whalen 101 1 100,101 203 Mavris 101 1 100,101 204 Baer 101 1 100,101 205 Higgins 101 1 100,101 109 Faviet 108 2 100,101,108 110 Chen 108 2 100,101,108 111 Sciarra 108 2 100,101,108 112 Urman 108 2 100,101,108 113 Popp 108 2 100,101,108 206 Gietz 205 2 100,101,205
次の文は、従業員101の直接的または間接的な部下である従業員とそのレポート・レベルを表示します。これは、レポート・レベル1で停止します。
WITH reports_to_101 (eid, emp_last, mgr_id, reportLevel) AS ( SELECT employee_id, last_name, manager_id, 0 reportLevel FROM employees WHERE employee_id = 101 UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1 FROM reports_to_101 r, employees e WHERE r.eid = e.manager_id ) SELECT eid, emp_last, mgr_id, reportLevel FROM reports_to_101 WHERE reportLevel <= 1 ORDER BY reportLevel, eid; EID EMP_LAST MGR_ID REPORTLEVEL ---------- ------------------------- ---------- ----------- 101 Kochhar 100 0 108 Greenberg 101 1 200 Whalen 101 1 203 Mavris 101 1 204 Baer 101 1 205 Higgins 101 1
次の文は、管理レベルごとにインデントを挿入して、組織全体を表示します。
WITH org_chart (eid, emp_last, mgr_id, reportLevel, salary, job_id) AS ( SELECT employee_id, last_name, manager_id, 0 reportLevel, salary, job_id FROM employees WHERE manager_id is null UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, r.reportLevel+1 reportLevel, e.salary, e.job_id FROM org_chart r, employees e WHERE r.eid = e.manager_id ) SEARCH DEPTH FIRST BY emp_last SET order1 SELECT lpad(' ',2*reportLevel)||emp_last emp_name, eid, mgr_id, salary, job_id FROM org_chart ORDER BY order1; EMP_NAME EID MGR_ID SALARY JOB_ID -------------------- ---------- ---------- ---------- ---------- King 100 24000 AD_PRES Cambrault 148 100 11000 SA_MAN Bates 172 148 7300 SA_REP Bloom 169 148 10000 SA_REP Fox 170 148 9600 SA_REP Kumar 173 148 6100 SA_REP Ozer 168 148 11500 SA_REP Smith 171 148 7400 SA_REP De Haan 102 100 17000 AD_VP Hunold 103 102 9000 IT_PROG Austin 105 103 4800 IT_PROG Ernst 104 103 6000 IT_PROG Lorentz 107 103 4200 IT_PROG Pataballa 106 103 4800 IT_PROG Errazuriz 147 100 12000 SA_MAN Ande 166 147 6400 SA_REP . . .
次の文は組織全体を表示し、管理レベルごとにインデントを挿入します(各レベルの順序はhire_date
によって決定します)。is_cycle
の値がY
に設定されるのは、その従業員のhire_date
と同じ値を持つマネージャが管理チェーン内の上位にいる場合です。
WITH dup_hiredate (eid, emp_last, mgr_id, reportLevel, hire_date, job_id) AS ( SELECT employee_id, last_name, manager_id, 0 reportLevel, hire_date, job_id FROM employees WHERE manager_id is null UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, r.reportLevel+1 reportLevel, e.hire_date, e.job_id FROM dup_hiredate r, employees e WHERE r.eid = e.manager_id ) SEARCH DEPTH FIRST BY hire_date SET order1 CYCLE hire_date SET is_cycle TO 'Y' DEFAULT 'N' SELECT lpad(' ',2*reportLevel)||emp_last emp_name, eid, mgr_id, hire_date, job_id, is_cycle FROM dup_hiredate ORDER BY order1; EMP_NAME EID MGR_ID HIRE_DATE JOB_ID IS_CYCLE -------------------- ---------- ---------- --------- ---------- -------- King 100 17-JUN-03 AD_PRES N De Haan 102 100 13-JAN-01 AD_VP N Hunold 103 102 03-JAN-06 IT_PROG N Austin 105 103 25-JUN-05 IT_PROG N . . . Kochhar 101 100 21-SEP-05 AD_VP N Mavris 203 101 07-JUN-02 HR_REP N Baer 204 101 07-JUN-02 PR_REP N Higgins 205 101 07-JUN-02 AC_MGR N Gietz 206 205 07-JUN-02 AC_ACCOUNT Y Greenberg 108 101 17-AUG-02 FI_MGR N Faviet 109 108 16-AUG-02 FI_ACCOUNT N Chen 110 108 28-SEP-05 FI_ACCOUNT N . . .
次の文は、各マネージャの配下の従業員の人数をカウントします。
WITH emp_count (eid, emp_last, mgr_id, mgrLevel, salary, cnt_employees) AS ( SELECT employee_id, last_name, manager_id, 0 mgrLevel, salary, 0 cnt_employees FROM employees UNION ALL SELECT e.employee_id, e.last_name, e.manager_id, r.mgrLevel+1 mgrLevel, e.salary, 1 cnt_employees FROM emp_count r, employees e WHERE e.employee_id = r.mgr_id ) SEARCH DEPTH FIRST BY emp_last SET order1 SELECT emp_last, eid, mgr_id, salary, sum(cnt_employees), max(mgrLevel) mgrLevel FROM emp_count GROUP BY emp_last, eid, mgr_id, salary HAVING max(mgrLevel) > 0 ORDER BY mgr_id NULLS FIRST, emp_last; EMP_LAST EID MGR_ID SALARY SUM(CNT_EMPLOYEES) MGRLEVEL ------------------ ---------- ---------- ---------- ------------------ ---------- King 100 24000 106 3 Cambrault 148 100 11000 7 2 De Haan 102 100 17000 5 2 Errazuriz 147 100 12000 6 1 Fripp 121 100 8200 8 1 Hartstein 201 100 13000 1 1 Kaufling 122 100 7900 8 1 . . .
単純問合せの例: 次の文は、部門番号30の従業員表employees
の行を選択します。
SELECT * FROM employees WHERE department_id = 30 ORDER BY last_name;
次の文は、部門番号30の購買係を除くすべての従業員の名前、職種、給与および部門番号を選択します。
SELECT last_name, job_id, salary, department_id FROM employees WHERE NOT (job_id = 'PU_CLERK' AND department_id = 30) ORDER BY last_name;
次の文は、FROM
句の副問合せから、すべての従業員数と給与合計がすべての部門に対して占める割合を部門ごとに戻します。
SELECT a.department_id "Department", a.num_emp/b.total_count "%_Employees", a.sal_sum/b.total_sal "%_Salary" FROM (SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum FROM employees GROUP BY department_id) a, (SELECT COUNT(*) total_count, SUM(salary) total_sal FROM employees) b ORDER BY a.department_id;
パーティションからの行の選択例: FROM
句にキーワードPARTITION
を指定することによって、パーティション表の1つのパーティションから行を選択できます。次のSQL文は、サンプル表sh.sales
のsales_q2_2000
パーティションへ別名を割り当て、行を取り出します。
SELECT * FROM sales PARTITION (sales_q2_2000) s WHERE s.amount_sold > 1500 ORDER BY cust_id, time_id, channel_id;
次の文は、oe.orders
表から、指定した日付より早い注文が含まれる行を選択します。
SELECT * FROM orders WHERE order_date < TO_DATE('2006-06-15', 'YYYY-MM-DD');
サンプルの選択例: 次の問合せは、oe.orders
表の注文数を推定します。
SELECT COUNT(*) * 10 FROM orders SAMPLE (10); COUNT(*)*10 ----------- 70
問合せでは推定値が戻されるため、実際の戻り値は問合せを行うたびに異なる場合があります。
SELECT COUNT(*) * 10 FROM orders SAMPLE (10); COUNT(*)*10 ----------- 80
次の問合せでは、前述の問合せにシード値を追加します。同じシード値では、常に同じ推定値が戻されます。
SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED (1); COUNT(*)*10 ----------- 130 SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED(4); COUNT(*)*10 ----------- 120 SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED (1); COUNT(*)*10 ----------- 130
フラッシュバック問合せの使用例: 次の文は、サンプル表hr.employees
の現在の値を表示し、値を変更します。この例の目的はデモであるため、使用している時間隔が非常に短くなっています。実際の環境での時間隔は、これより長いのが一般的です。
SELECT salary FROM employees WHERE last_name = 'Chung'; SALARY ---------- 3800 UPDATE employees SET salary = 4000 WHERE last_name = 'Chung'; 1 row updated. SELECT salary FROM employees WHERE last_name = 'Chung'; SALARY ---------- 4000
更新前の値を確認するには、次のフラッシュバック問合せを使用します。
SELECT salary FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE) WHERE last_name = 'Chung'; SALARY ---------- 3800
過去の特定の期間における値を確認するには、次のバージョン・フラッシュバック問合せを使用します。
SELECT salary FROM employees VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE AND SYSTIMESTAMP - INTERVAL '1' MINUTE WHERE last_name = 'Chung';
元の値に戻すには、フラッシュバック問合せを別のUPDATE
文の副問合せとして使用します。
UPDATE employees SET salary = (SELECT salary FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE) WHERE last_name = 'Chung') WHERE last_name = 'Chung'; 1 row updated. SELECT salary FROM employees WHERE last_name = 'Chung'; SALARY ---------- 3800
GROUP BY句の使用例: 次の文は、employees
表の各部門について最低給与と最高給与を戻します。
SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id ORDER BY department_id;
次の文は、各部門の事務員について最高給与と最低給与を戻します。
SELECT department_id, MIN(salary), MAX (salary) FROM employees WHERE job_id = 'PU_CLERK' GROUP BY department_id ORDER BY department_id;
GROUP BY CUBE句の使用例: 部門および職種のすべての組合せについて、従業員数と平均年収を戻すには、サンプル表hr.employees
およびhr.departments
に次の問合せを発行します。
SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department_name, DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id, COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal" FROM employees e, departments d WHERE d.department_id = e.department_id GROUP BY CUBE (department_name, job_id) ORDER BY department_name, job_id; DEPARTMENT_NAME JOB_ID Total Empl Average Sal ------------------------------ ---------- ---------- ----------- Accounting AC_ACCOUNT 1 99600 Accounting AC_MGR 1 144000 Accounting All Jobs 2 121800 Administration AD_ASST 1 52800 . . . Shipping ST_CLERK 20 33420 Shipping ST_MAN 5 87360
GROUPING SETS句の使用例: 次の例は、指定した3つのグループで集計した販売の合計を示します。
(channel_desc, calendar_month_desc, country_id)
(channel_desc, country_id)
(calendar_month_desc, country_id)
GROUPING
SETS
構文を指定しない場合、SQLはより複雑になり、問合せの効果は低くなります。たとえば、3つの個別の問合せを実行し、UNION
演算を行うか、またはCUBE(channel_desc, calendar_month_desc, country_id)
操作を指定した問合せを実行し、生成される8つのグループから5つを除去します。
SELECT channel_desc, calendar_month_desc, co.country_id, TO_CHAR(sum(amount_sold) , '9,999,999,999') SALES$ FROM sales, customers, times, channels, countries co WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND sales.channel_id= channels.channel_id AND customers.country_id = co.country_id AND channels.channel_desc IN ('Direct Sales', 'Internet') AND times.calendar_month_desc IN ('2000-09', '2000-10') AND co.country_iso_code IN ('UK', 'US') GROUP BY GROUPING SETS( (channel_desc, calendar_month_desc, co.country_id), (channel_desc, co.country_id), (calendar_month_desc, co.country_id) ); CHANNEL_DESC CALENDAR COUNTRY_ID SALES$ -------------------- -------- ---------- ---------- Internet 2000-09 52790 124,224 Direct Sales 2000-09 52790 638,201 Internet 2000-10 52790 137,054 Direct Sales 2000-10 52790 682,297 2000-09 52790 762,425 2000-10 52790 819,351 Internet 52790 261,278 Direct Sales 52790 1,320,497
階層問合せの例 CONNECT
BY
句を指定した次の問合せは、親である行のemployee_id
値が子である行のmanager_id
値と等しいという階層関係を定義します。
SELECT last_name, employee_id, manager_id FROM employees CONNECT BY employee_id = manager_id ORDER BY last_name;
次のCONNECT
BY
句では、PRIOR
演算子がemployee_id
値のみに適用されます。この条件を評価するために、データベースは親である行に対してはemployee_id
の値を評価し、子である行に対してはmanager_id
、salary
およびcommission_pct
のそれぞれの値を評価します。
SELECT last_name, employee_id, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id AND salary > commission_pct ORDER BY last_name;
子である行を限定する場合、manager_id
の値と親である行のemployee_id
の値が等しく、salary
の値がcommission_pct
の値より大きい必要があります。
HAVING条件の使用例: 次の文は、従業員の最低給与が$5,000未満の部門についての最高給与と最低給与を戻します。
SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id HAVING MIN(salary) < 5000 ORDER BY department_id; DEPARTMENT_ID MIN(SALARY) MAX(SALARY) ------------- ----------- ----------- 10 4400 4400 30 2500 11000 50 2100 8200 60 4200 9000
次の例は、HAVING
句で相関副問合せを使用して、マネージャのいない部門および部門のないマネージャを結果セットから除外しています。
SELECT department_id, manager_id FROM employees GROUP BY department_id, manager_id HAVING (department_id, manager_id) IN (SELECT department_id, manager_id FROM employees x WHERE x.department_id = employees.department_id) ORDER BY department_id;
ORDER BY句の使用例: 次の文は、employees
表からすべての購買係のレコードを選択し、その給与によって降順にソートします。
SELECT * FROM employees WHERE job_id = 'PU_CLERK' ORDER BY salary DESC;
次の文は、employees
表から情報を選択し、最初に部門番号で昇順にソートした後、給与で降順にソートします。
SELECT last_name, department_id, salary FROM employees ORDER BY department_id ASC, salary DESC, last_name;
次の文は、前述のSELECT
文と同じ情報を選択し、ORDER
BY
位置表記法を使用します。ここでは、まずdepartment_id
で昇順に、次にsalary
で降順に、最後にlast_name
でアルファベット順に順序付けします。
SELECT last_name, department_id, salary FROM employees ORDER BY 2 ASC, 3 DESC, 1;
MODEL句の例: ここで作成されたビューは、サンプル・スキーマsh
に基づいており、後述の例で使用されています。
CREATE OR REPLACE VIEW sales_view_ref AS SELECT country_name country, prod_name prod, calendar_year year, SUM(amount_sold) sale, COUNT(amount_sold) cnt FROM sales,times,customers,countries,products WHERE sales.time_id = times.time_id AND sales.prod_id = products.prod_id AND sales.cust_id = customers.cust_id AND customers.country_id = countries.country_id AND ( customers.country_id = 52779 OR customers.country_id = 52776 ) AND ( prod_name = 'Standard Mouse' OR prod_name = 'Mouse Pad' ) GROUP BY country_name,prod_name,calendar_year; SELECT country, prod, year, sale FROM sales_view_ref ORDER BY country, prod, year; COUNTRY PROD YEAR SALE ---------- ----------------------------------- -------- --------- France Mouse Pad 1998 2509.42 France Mouse Pad 1999 3678.69 France Mouse Pad 2000 3000.72 France Mouse Pad 2001 3269.09 France Standard Mouse 1998 2390.83 France Standard Mouse 1999 2280.45 France Standard Mouse 2000 1274.31 France Standard Mouse 2001 2164.54 Germany Mouse Pad 1998 5827.87 Germany Mouse Pad 1999 8346.44 Germany Mouse Pad 2000 7375.46 Germany Mouse Pad 2001 9535.08 Germany Standard Mouse 1998 7116.11 Germany Standard Mouse 1999 6263.14 Germany Standard Mouse 2000 2637.31 Germany Standard Mouse 2001 6456.13 16 rows selected.
次の例では、国、製品、年および売上が格納されている列が存在するsales_view_ref
から多次元配列を作成します。さらに、次のことも行われます。
2001年のマウス・パッドの売上を含む行が存在する場合、1999年および2000年のマウス・パッドの売上を2001年のマウス・パッドの売上に割り当てます。
2002年のスタンダード・マウスの売上を含む行が存在しない場合、2001年のスタンダード・マウスの売上を2002年のスタンダード・マウスの売上に割り当てます。
SELECT country,prod,year,s FROM sales_view_ref MODEL PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale s) IGNORE NAV UNIQUE DIMENSION RULES UPSERT SEQUENTIAL ORDER ( s[prod='Mouse Pad', year=2001] = s['Mouse Pad', 1999] + s['Mouse Pad', 2000], s['Standard Mouse', 2002] = s['Standard Mouse', 2001] ) ORDER BY country, prod, year; COUNTRY PROD YEAR SALE ---------- ----------------------------------- -------- --------- France Mouse Pad 1998 2509.42 France Mouse Pad 1999 3678.69 France Mouse Pad 2000 3000.72 France Mouse Pad 2001 6679.41 France Standard Mouse 1998 2390.83 France Standard Mouse 1999 2280.45 France Standard Mouse 2000 1274.31 France Standard Mouse 2001 2164.54 France Standard Mouse 2002 2164.54 Germany Mouse Pad 1998 5827.87 Germany Mouse Pad 1999 8346.44 Germany Mouse Pad 2000 7375.46 Germany Mouse Pad 2001 15721.9 Germany Standard Mouse 1998 7116.11 Germany Standard Mouse 1999 6263.14 Germany Standard Mouse 2000 2637.31 Germany Standard Mouse 2001 6456.13 Germany Standard Mouse 2002 6456.13 18 rows selected.
最初のルールでは、ルールの左側で記号参照が使用されているため、UPDATE
動作が使用されます。ルールの左側で表される行が存在するため、メジャー列が更新されます。行が存在しない場合、何も実行されません。
2番目のルールでは、ルールの左側で位置参照が使用され、単一セルが参照されているため、UPSERT
動作が使用されます。行が存在しないため、新しい行が追加され、関連するメジャー列が更新されます。行が存在する場合、メジャー列は更新されません。
関連項目: 詳細および例は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。 |
次の例は、同じsales_view_ref
ビューおよび分析ファンクションSUM
を使用して、国および年ごとの累計(csum
)を計算しています。
SELECT country, year, sale, csum FROM (SELECT country, year, SUM(sale) sale FROM sales_view_ref GROUP BY country, year ) MODEL DIMENSION BY (country, year) MEASURES (sale, 0 csum) RULES (csum[any, any]= SUM(sale) OVER (PARTITION BY country ORDER BY year ROWS UNBOUNDED PRECEDING) ) ORDER BY country, year; COUNTRY YEAR SALE CSUM --------------- ---------- ---------- ---------- France 1998 4900.25 4900.25 France 1999 5959.14 10859.39 France 2000 4275.03 15134.42 France 2001 5433.63 20568.05 Germany 1998 12943.98 12943.98 Germany 1999 14609.58 27553.56 Germany 2000 10012.77 37566.33 Germany 2001 15991.21 53557.54 8 rows selected.
行制限の例:
次の文は、employee_id
値が低いほうから5人の従業員を返します。
SELECT employee_id, last_name FROM employees ORDER BY employee_id FETCH FIRST 5 ROWS ONLY; EMPLOYEE_ID LAST_NAME ----------- ------------------------- 100 King 101 Kochhar 102 De Haan 103 Hunold 104 Ernst
次の文は、その次にemployee_id
値が低いほうから5人の従業員を返します。
SELECT employee_id, last_name FROM employees ORDER BY employee_id OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; EMPLOYEE_ID LAST_NAME ----------- ------------------------- 105 Austin 106 Pataballa 107 Lorentz 108 Greenberg 109 Faviet
次の文は、給与が低いほうから5パーセントの従業員を返します。
SELECT employee_id, last_name, salary FROM employees ORDER BY salary FETCH FIRST 5 PERCENT ROWS ONLY; EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 132 Olson 2100 128 Markle 2200 136 Philtanker 2200 127 Landry 2400 135 Gee 2400 119 Colmenares 2500
WITH
TIES
が指定されているため、次の文は、給与が低いほうから5パーセントの従業員と、前述の例で最後にフェッチされた行と同じ給与の追加の従業員が返されれます。
SELECT employee_id, last_name, salary FROM employees ORDER BY salary FETCH FIRST 5 PERCENT ROWS WITH TIES; EMPLOYEE_ID LAST_NAME SALARY ----------- ------------------------- ---------- 132 Olson 2100 128 Markle 2200 136 Philtanker 2200 127 Landry 2400 135 Gee 2400 119 Colmenares 2500 131 Marlow 2500 140 Patel 2500 144 Vargas 2500 182 Sullivan 2500 191 Perkins 2500
FOR UPDATE句の使用例: 次の文は、employees
表中のオックスフォード勤務(location_id
は2500)の購買係の行をロックし、departments
表中の購買係が存在するオックスフォードの部門の行をロックします。
SELECT e.employee_id, e.salary, e.commission_pct FROM employees e, departments d WHERE job_id = 'SA_REP' AND e.department_id = d.department_id AND location_id = 2500 ORDER BY e.employee_id FOR UPDATE;
次の文は、employees
表中のオックスフォード勤務の購買係の行のみをロックします。departments
表中の行はロックされません。
SELECT e.employee_id, e.salary, e.commission_pct FROM employees e JOIN departments d USING (department_id) WHERE job_id = 'SA_REP' AND location_id = 2500 ORDER BY e.employee_id FOR UPDATE OF e.salary;
WITH CHECK OPTION句の使用例: 次の文は、3番目の値が副問合せwhere_clause
の条件に違反していても有効です。
INSERT INTO (SELECT department_id, department_name, location_id FROM departments WHERE location_id < 2000) VALUES (9999, 'Entertainment', 2500);
ただし、次の文はWITH
CHECK
OPTION
句を含むため、無効になります。
INSERT INTO (SELECT department_id, department_name, location_id FROM departments WHERE location_id < 2000 WITH CHECK OPTION) VALUES (9999, 'Entertainment', 2500); * ERROR at line 2: ORA-01402: view WITH CHECK OPTION where-clause violation
PIVOTおよびUNPIVOTの使用例: oe.orders
表には、注文が発注された日時(order_date
)、発注方法(order_mode
)および注文の合計数(order_total
)に関する情報とその他の情報が含まれています。次の例は、PIVOT
句を使用して、order_mode
値を列にピボットし、処理中にorder_total
データを集計して発注モードごとの年間合計を取得する方法を示します。
CREATE TABLE pivot_table AS SELECT * FROM (SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM orders) PIVOT (SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet)); SELECT * FROM pivot_table ORDER BY year; YEAR STORE INTERNET ---------- ---------- ---------- 2004 5546.6 2006 371895.5 100056.6 2007 1274078.8 1271019.5 2008 252108.3 393349.4
UNPIVOT
句では、入力列ヘッダーが1つ以上の記述子列の値として出力され、入力列値が1つ以上のメジャー列の値として出力されるように、指定した列を変換します。次に示す最初の問合せでは、デフォルトでNULLが除外されています。2番目の問合せは、INCLUDE
NULLS
句を使用してNULLを組み込むことができることを示しています。
SELECT * FROM pivot_table UNPIVOT (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online')) ORDER BY year, order_mode; YEAR ORDER_ YEARLY_TOTAL ---------- ------ ------------ 2004 direct 5546.6 2006 direct 371895.5 2006 online 100056.6 2007 direct 1274078.8 2007 online 1271019.5 2008 direct 252108.3 2008 online 393349.4 7 rows selected. SELECT * FROM pivot_table UNPIVOT INCLUDE NULLS (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online')) ORDER BY year, order_mode; YEAR ORDER_ YEARLY_TOTAL ---------- ------ ------------ 2004 direct 5546.6 2004 online 2006 direct 371895.5 2006 online 100056.6 2007 direct 1274078.8 2007 online 1271019.5 2008 direct 252108.3 2008 online 393349.4 8 rows selected.
結合問合せの使用例: 次の例は、問合せにおける様々な表の結合方法を示します。最初の例では、等価結合は、それぞれの従業員の名前と職種、およびその従業員が属する部門の番号と名前を戻します。
SELECT last_name, job_id, departments.department_id, department_name FROM employees, departments WHERE employees.department_id = departments.department_id ORDER BY last_name, job_id; LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME ------------------- ---------- ------------- ---------------------- Abel SA_REP 80 Sales Ande SA_REP 80 Sales Atkinson ST_CLERK 50 Shipping Austin IT_PROG 60 IT . . .
従業員名およびジョブは部門名と異なる表に格納されているため、このデータを戻すには結合を使用する必要があります。Oracle Databaseでは、次の結合条件に従って2つの表の行が結合されます。
employees.department_id = departments.department_id
次の等価結合は、すべての販売マネージャの名前、職種、部門番号および部門名を戻します。
SELECT last_name, job_id, departments.department_id, department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND job_id = 'SA_MAN' ORDER BY last_name; LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME ------------------- ---------- ------------- ----------------------- Cambrault SA_MAN 80 Sales Errazuriz SA_MAN 80 Sales Partners SA_MAN 80 Sales Russell SA_MAN 80 Sales Zlotkey SA_MAN 80 Sales
この問合せは、次のwhere_clause
条件を使用して'SA_MAN
'というjob
値を持つ行のみを戻すこと以外は、前述の例と同じです。
副問合せの使用例: 次の文は、従業員'Lorentz
'と同じ部門で働く従業員を判断します。
SELECT last_name, department_id FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE last_name = 'Lorentz') ORDER BY last_name, department_id;
次の文は、employees
表の職種を変更した(job_history
表に示される)すべての従業員の給与を10%上げます。
UPDATE employees SET salary = salary * 1.1 WHERE employee_id IN (SELECT employee_id FROM job_history);
次の文は、departments
表から3つの列のみを伴って新しい表new_departments
を作成します。
CREATE TABLE new_departments (department_id, department_name, location_id) AS SELECT department_id, department_name, location_id FROM departments;
自己結合の使用例: 次の問合せは、自己結合を使用して、それぞれの従業員の名前およびその従業員の上司の名前を戻します。出力を短くするためにWHERE
句が追加されています。
SELECT e1.last_name||' works for '||e2.last_name "Employees and Their Managers" FROM employees e1, employees e2 WHERE e1.manager_id = e2.employee_id AND e1.last_name LIKE 'R%' ORDER BY e1.last_name; Employees and Their Managers ------------------------------- Rajs works for Mourgos Raphaely works for King Rogers works for Kaufling Russell works for King
この問合せの結合条件では、サンプル表employees
に対する別名e1
およびe2
を使用します。
e1.manager_id = e2.employee_id
外部結合の使用例: 次の例では、パーティション化された外部結合によって行のデータの欠損を補完し、分析ファンクションの指定および信頼性の高いレポートの書式設定を簡単にする方法を示します。この例では、結合で使用する小規模なデータ表を最初に作成します。
SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name;
これは、次に示す以前のOracle Databaseの外部結合の構文と同じ問合せです。
SELECT d.department_id, e.last_name FROM departments d, employees e WHERE d.department_id = e.department_id(+) ORDER BY d.department_id, e.last_name;
この構文ではなく、前述の例で示した、より柔軟性が高いFROM
句の結合構文を使用することをお薦めします。
左側外部結合では、従業員のいない部門を含むすべての部門が戻されます。右側外側結合が指定された同一文では、どの部門にも割り当てられていない従業員を含むすべての従業員が戻されます。
注意: 前述の例の従業員表には、従業員Zeussが追加されていますが、この従業員はサンプル・データには含まれません。 |
SELECT d.department_id, e.last_name FROM departments d RIGHT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name; DEPARTMENT_ID LAST_NAME ------------- ------------------------- . . . 110 Gietz 110 Higgins Grant Zeuss
この結果からは、GrantとZeussという従業員のdepartment_id
がNULL
であるのか、department_id
がdepartments
表に存在しないのかは不明です。これを確認するには、完全な外部結合が必要です。
SELECT d.department_id as d_dept_id, e.department_id as e_dept_id, e.last_name FROM departments d FULL OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name; D_DEPT_ID E_DEPT_ID LAST_NAME ---------- ---------- ------------------------- . . . 110 110 Gietz 110 110 Higgins . . . 260 270 999 Zeuss Grant
この例の列名は、結合状態にある両方の表で同じであるため、結合構文のUSING
句を指定することで、共通列機能も使用できます。出力は、一致する2つのdepartment_id
列がUSING
句によって結合されることを除き、前述の例と同じです。
SELECT department_id AS d_e_dept_id, e.last_name FROM departments d FULL OUTER JOIN employees e USING (department_id) ORDER BY department_id, e.last_name; D_E_DEPT_ID LAST_NAME ----------- ------------------------- . . . 110 Higgins 110 Gietz . . . 260 270 999 Zeuss Grant
パーティション化された外部結合の使用例: 次の例では、パーティション化された外部結合によって行の欠損を補完し、分析計算の指定および信頼性の高いレポートの書式設定を簡単にする方法を示します。この例では、結合で使用する単純な表を最初に作成し、移入します。
CREATE TABLE inventory (time_id DATE, product VARCHAR2(10), quantity NUMBER); INSERT INTO inventory VALUES (TO_DATE('01/04/01', 'DD/MM/YY'), 'bottle', 10); INSERT INTO inventory VALUES (TO_DATE('06/04/01', 'DD/MM/YY'), 'bottle', 10); INSERT INTO inventory VALUES (TO_DATE('01/04/01', 'DD/MM/YY'), 'can', 10); INSERT INTO inventory VALUES (TO_DATE('04/04/01', 'DD/MM/YY'), 'can', 10); SELECT times.time_id, product, quantity FROM inventory PARTITION BY (product) RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id) WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY') AND TO_DATE('06/04/01', 'DD/MM/YY') ORDER BY 2,1; TIME_ID PRODUCT QUANTITY --------- ---------- ---------- 01-APR-01 bottle 10 02-APR-01 bottle 03-APR-01 bottle 04-APR-01 bottle 05-APR-01 bottle 06-APR-01 bottle 10 01-APR-01 can 10 02-APR-01 can 03-APR-01 can 04-APR-01 can 10 05-APR-01 can 06-APR-01 can 12 rows selected.
これで、製品ディメンションの各パーティションのデータの密度は、時間ディメンションに沿ってより密になります。ただし、各パーティションに新しく追加された行のquantity列はそれぞれNULLです。さらに役立つようにするには、NULL値を、時間順でそれより前にあるNULL
以外の値で置き換えます。このことを実現するには、分析ファンクションLAST_VALUE
を問合せ結果に適用します。
SELECT time_id, product, LAST_VALUE(quantity IGNORE NULLS) OVER (PARTITION BY product ORDER BY time_id) quantity FROM ( SELECT times.time_id, product, quantity FROM inventory PARTITION BY (product) RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id) WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY') AND TO_DATE('06/04/01', 'DD/MM/YY')) ORDER BY 2,1; TIME_ID PRODUCT QUANTITY --------- ---------- ---------- 01-APR-01 bottle 10 02-APR-01 bottle 10 03-APR-01 bottle 10 04-APR-01 bottle 10 05-APR-01 bottle 10 06-APR-01 bottle 10 01-APR-01 can 10 02-APR-01 can 10 03-APR-01 can 10 04-APR-01 can 10 05-APR-01 can 10 06-APR-01 can 10 12 rows selected.
関連項目: 時系列計算における欠損補完の詳細および使用例については、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。 |
アンチ結合の使用例: 次の例では、特定の部門の集合に所属していない従業員のリストを選択します。
SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE location_id = 1700) ORDER BY last_name;
セミ結合の使用例: 次の例では、副問合せに一致する行がemployees
表に多数存在する場合でも、departments
表から1つの行のみが戻されます。employees
のsalary
列に索引が定義されていない場合、セミ結合を使用すると、問合せのパフォーマンスが向上します。
SELECT * FROM departments WHERE EXISTS (SELECT * FROM employees WHERE departments.department_id = employees.department_id AND employees.salary > 2500) ORDER BY department_name;
CROSS APPLYおよびOUTER APPLY結合の使用例 次の文では、cross_outer_apply_clause
の
CROSS APPLY
句を使用します。この結合は、結合(departments
)の左側にある表の行のうち、結合の右側にあるインライン・ビューから結果を生成する行のみを返します。つまり、この結合は従業員が1人以上の部門のみを返します。WHERE
句は、Marketing、OperationsおよびPublic Relations部門のみが含まれるように結果セットを制限します。ただし、Operations部門には従業員がいないため、この部門は結果セットに含まれません。
SELECT d.department_name, v.employee_id, v.last_name FROM departments d CROSS APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations') ORDER BY d.department_name, v.employee_id; DEPARTMENT_NAME EMPLOYEE_ID LAST_NAME ------------------------------ ----------- ------------------------- Marketing 201 Hartstein Marketing 202 Fay Public Relations 204 Baer
次の文では、cross_outer_apply_clause
の
OUTER APPLY
句を使用します。この結合は、結合の右側にあるインライン・ビューから結果を生成するかどうかにかかわらず、結合(departments
)の左側にある表のすべての行を返します。つまり、この結合は従業員がいるかどうかにかかわらず、すべての部門を返します。WHERE
句は、Marketing、OperationsおよびPublic Relations部門のみが含まれるように結果セットを制限します。Operations部門には従業員がいませんが、この部門も結果セットに含まれます。
SELECT d.department_name, v.employee_id, v.last_name FROM departments d OUTER APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations') ORDER by d.department_name, v.employee_id; DEPARTMENT_NAME EMPLOYEE_ID LAST_NAME ------------------------------ ----------- ------------------------- Marketing 201 Hartstein Marketing 202 Fay Operations Public Relations 204 Baer
LATERALインライン・ビューの使用例: 次の例では、2つのオペランドの結合を示します。2番目のオペランドは、WHERE
句で最初のオペランドである表e
を指定するインライン・ビューです。これは結果的にエラーとなります。
SELECT * FROM employees e, (SELECT * FROM departments d WHERE e.department_id = d.department_id); ORA-00904: "E"."DEPARTMENT_ID": invalid identifier
次の例では、2つのオペランドの結合を示します。2番目のオペランドは、WHERE
句で最初オペランドである表e
を指定するLATERALインライン・ビューであり、エラーが発生することなく成功します。
SELECT * FROM employees e, LATERAL(SELECT * FROM departments d WHERE e.department_id = d.department_id);
表のコレクション例: DML操作は、表の列として定義された場合にのみ、ネストした表で実行できます。したがって、INSERT、DELETE
またはUPDATE
文のquery_table_expr_clause
が
table_collection_expression
の場合、コレクション式は、TABLE
コレクション式を使用して表のネストした表の列を選択する副問合せである必要があります。次の例は、次の使用例に基づいています。
データベースに、department_id
列、location_id
列、manager_id
列を持つhr_info
表と、マネージャごとのすべての従業員のlast_name
列、department_id
列およびsalary
列を持つネストした表型people
の列が含まれていると仮定します。
CREATE TYPE people_typ AS OBJECT ( last_name VARCHAR2(25), department_id NUMBER(4), salary NUMBER(8,2)); / CREATE TYPE people_tab_typ AS TABLE OF people_typ; / CREATE TABLE hr_info ( department_id NUMBER(4), location_id NUMBER(4), manager_id NUMBER(6), people people_tab_typ) NESTED TABLE people STORE AS people_stor_tab; INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());
次の例では、hr_info
表の部門番号280のネストした表people
の列に値を挿入します。
INSERT INTO TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) VALUES ('Smith', 280, 1750);
次の例では、部門番号280のネストした表people
を更新します。
UPDATE TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) p SET p.salary = p.salary + 100;
次の例では、部門番号280のネストした表people
を削除します。
DELETE TABLE(SELECT h.people FROM hr_info h WHERE h.department_id = 280) p WHERE p.salary > 1700;
コレクションのネスト解除例: ネストした表の列からデータを選択するには、TABLE
コレクション式を使用して、ネストした表を表の列として処理します。このプロセスをコレクション・ネスト解除と呼びます。
次の文を使用すると、前述の例で作成したhr_info
からすべての行を取得し、hr_info
のネストした表people
の列からすべての行を取得できます。
SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(t1.people) t2 WHERE t2.department_id = t1.department_id;
people
は、hr_info
のネストした表の列ではなく、last_name
、department_id
、address
、hiredate
およびsalary
列と別の表であると仮定します。次の文を使用して、前述の例と同じ行を抽出できます。
SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST(MULTISET( SELECT t3.last_name, t3.department_id, t3.salary FROM people t3 WHERE t3.department_id = t1.department_id) AS people_tab_typ)) t2;
最後に、people
はhr_info
表のネストした表の列でも、表そのものでもないと仮定します。かわりに、すべての従業員の名前、部門および給与を様々な情報から抽出するpeople_func
ファンクションを作成しておきます。次の問合せを使用して、前述の例と同様の情報を得ることができます。
SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST (people_func( ... ) AS people_tab_typ)) t2;
関連項目: コレクション・ネスト解除の別の例は、『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照してください。 |
LEVEL疑似列の使用例: 次の文は、すべての従業員を階層順序で戻します。職種がAD_VP
である従業員がルート行となるように定義されています。また、親である行の従業員番号が上司の従業員番号となるように、親である行の子である行が定義されています。
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees START WITH job_id = 'AD_VP' CONNECT BY PRIOR employee_id = manager_id; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- Kochhar 101 100 AD_VP Greenberg 108 101 FI_MGR Faviet 109 108 FI_ACCOUNT Chen 110 108 FI_ACCOUNT Sciarra 111 108 FI_ACCOUNT Urman 112 108 FI_ACCOUNT Popp 113 108 FI_ACCOUNT Whalen 200 101 AD_ASST Mavris 203 101 HR_REP Baer 204 101 PR_REP Higgins 205 101 AC_MGR Gietz 206 205 AC_ACCOUNT De Haan 102 100 AD_VP Hunold 103 102 IT_PROG Ernst 104 103 IT_PROG Austin 105 103 IT_PROG Pataballa 106 103 IT_PROG Lorentz 107 103 IT_PROG
次の文は、前述の例とほぼ同じですが、職種がFI_MGR
である従業員を選択しません。
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees WHERE job_id != 'FI_MGR' START WITH job_id = 'AD_VP' CONNECT BY PRIOR employee_id = manager_id; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- Kochhar 101 100 AD_VP Faviet 109 108 FI_ACCOUNT Chen 110 108 FI_ACCOUNT Sciarra 111 108 FI_ACCOUNT Urman 112 108 FI_ACCOUNT Popp 113 108 FI_ACCOUNT Whalen 200 101 AD_ASST Mavris 203 101 HR_REP Baer 204 101 PR_REP Higgins 205 101 AC_MGR Gietz 206 205 AC_ACCOUNT De Haan 102 100 AD_VP Hunold 103 102 IT_PROG Ernst 104 103 IT_PROG Austin 105 103 IT_PROG Pataballa 106 103 IT_PROG Lorentz 107 103 IT_PROG
Greenberg
が管理する従業員は戻されますが、マネージャGreenberg
は戻されません。
次の文も、前述の例と同じですが、LEVEL
疑似列を使用して管理階層の最初の2つのレベルのみを選択します。
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees START WITH job_id = 'AD_PRES' CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 2; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- King 100 AD_PRES Kochhar 101 100 AD_VP De Haan 102 100 AD_VP Raphaely 114 100 PU_MAN Weiss 120 100 ST_MAN Fripp 121 100 ST_MAN Kaufling 122 100 ST_MAN Vollman 123 100 ST_MAN Mourgos 124 100 ST_MAN Russell 145 100 SA_MAN Partners 146 100 SA_MAN Errazuriz 147 100 SA_MAN Cambrault 148 100 SA_MAN Zlotkey 149 100 SA_MAN Hartstein 201 100 MK_MAN
分散問合せの使用例: 次の文は、ローカル・データベース上のdepartments
表と、remote
データベース上のemployees
表を結合します。
SELECT last_name, department_name FROM employees@remote, departments WHERE employees.department_id = departments.department_id;
相関副問合せの使用例: 次に、相関副問合せの構文の一般的な例を示します。
SELECT select_list FROM table1 t_alias1 WHERE expr operator (SELECT column_list FROM table2 t_alias2 WHERE t_alias1.column operator t_alias2.column); UPDATE table1 t_alias1 SET column = (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column); DELETE FROM table1 t_alias1 WHERE column operator (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column);
次の文は、部門内の平均給与を超える給与を支給されている従業員の情報を戻します。給与情報が格納されているemployees
表に別名を割り当て、相関副問合せではその別名を使用します。
SELECT department_id, last_name, salary FROM employees x WHERE salary > (SELECT AVG(salary) FROM employees WHERE x.department_id = department_id) ORDER BY department_id;
親問合せでは、相関副問合せを使用して同一部門の従業員の平均給与を、employees
表の行ごとに計算します。相関副問合せは、employees
表の各行について次の手順を実行します。
行のdepartment_id
を判断します。
department_id
に基づいて親問合せが評価されます。
行の部門の平均給与より高い給与の行がある場合は、その行を戻します。
副問合せは、employees
表の各行につき1回ずつ評価されます。
DUAL表からの選択例: 次の文は、現在の日付を戻します。
SELECT SYSDATE FROM DUAL;
employees
表から簡単にSYSDATE
を選択できますが、このとき、employees
表のすべての行に対して1件ずつ14行の同じSYSDATE
が戻ります。このため、DUAL
から選択する方が便利です。
順序値の選択例: 次の文は、employees_seq
順序を増分し、新しい値を戻します。
SELECT employees_seq.nextval FROM DUAL;
次の文は、employees_seq
の現在値を選択します。
SELECT employees_seq.currval FROM DUAL;
行パターン一致の例 この例では、行パターン一致を使用して株価データを問い合せます。次の文では、表Ticker
を作成し、株価データを表に挿入します。
CREATE TABLE Ticker (SYMBOL VARCHAR2(10), tstamp DATE, price NUMBER); INSERT INTO Ticker VALUES('ACME', '01-Apr-11', 12); INSERT INTO Ticker VALUES('ACME', '02-Apr-11', 17); INSERT INTO Ticker VALUES('ACME', '03-Apr-11', 19); INSERT INTO Ticker VALUES('ACME', '04-Apr-11', 21); INSERT INTO Ticker VALUES('ACME', '05-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '06-Apr-11', 12); INSERT INTO Ticker VALUES('ACME', '07-Apr-11', 15); INSERT INTO Ticker VALUES('ACME', '08-Apr-11', 20); INSERT INTO Ticker VALUES('ACME', '09-Apr-11', 24); INSERT INTO Ticker VALUES('ACME', '10-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '11-Apr-11', 19); INSERT INTO Ticker VALUES('ACME', '12-Apr-11', 15); INSERT INTO Ticker VALUES('ACME', '13-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '14-Apr-11', 25); INSERT INTO Ticker VALUES('ACME', '15-Apr-11', 14); INSERT INTO Ticker VALUES('ACME', '16-Apr-11', 12); INSERT INTO Ticker VALUES('ACME', '17-Apr-11', 14); INSERT INTO Ticker VALUES('ACME', '18-Apr-11', 24); INSERT INTO Ticker VALUES('ACME', '19-Apr-11', 23); INSERT INTO Ticker VALUES('ACME', '20-Apr-11', 22);
次の問合せでは、行パターン一致を使用し、株価が底値まで下降した後に上昇したすべての事例を検索します。これは一般的にV字形と呼ばれます。問合せでONE
ROW
PER
MATCH
が指定され、3つの一致が見つかったため、出力結果には3つの行のみが含まれます。
SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES STRT.tstamp AS start_tstamp, LAST(DOWN.tstamp) AS bottom_tstamp, LAST(UP.tstamp) AS end_tstamp ONE ROW PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP+) DEFINE DOWN AS DOWN.price < PREV(DOWN.price), UP AS UP.price > PREV(UP.price) ) MR ORDER BY MR.symbol, MR.start_tstamp; SYMBOL START_TST BOTTOM_TS END_TSTAM ---------- --------- --------- --------- ACME 05-APR-11 06-APR-11 10-APR-11 ACME 10-APR-11 12-APR-11 13-APR-11 ACME 14-APR-11 16-APR-11 18-APR-11