ヘッダーをスキップ

Oracle Database SQL言語リファレンス
11g リリース1(11.1)

E05750-03
目次
目次
索引
索引

戻る 次へ

19 SQL文: SAVEPOINT〜UPDATE

この章では、次のSQL文について説明します。


SAVEPOINT

用途

SAVEPOINT文を使用すると、後でロールバックできるシステム変更番号(SCN)の名前を作成できます。

参照:

  • セーブポイントの詳細は、『Oracle Database概要』を参照してください。

  • トランザクションのロールバックの詳細は、「ROLLBACK」を参照してください。

  • 現行トランザクションの特性の設定については、「SET TRANSACTION」を参照してください。

 

前提条件

ありません。

構文

savepoint::=

画像の説明

セマンティクス

savepoint

作成するセーブポイントの名前を指定します。

同一トランザクション内のセーブポイント名は、区別する必要があります。同じ識別子のセーブポイントを作成した場合、最初のセーブポイントは消去されます。セーブポイントを作成した後は、処理の継続、作業のコミット、トランザクション全体のロールバックまたはセーブポイントまでのロールバックを実行できます。

セーブポイントの作成例:

次の文は、サンプル表hr.employeesBandaGreeneの給与を更新するために、部門の給与合計が314,000ドルを超えていないことを確認してから、Greeneの給与を再入力します。

UPDATE employees 
    SET salary = 7000 
    WHERE last_name = 'Banda';
SAVEPOINT banda_sal;

UPDATE employees 
    SET salary = 12000 
    WHERE last_name = 'Greene';
SAVEPOINT greene_sal;

SELECT SUM(salary) FROM employees;

ROLLBACK TO SAVEPOINT banda_sal;
 
UPDATE employees 
    SET salary = 11000 
    WHERE last_name = 'Greene';
 
COMMIT; 

SELECT

用途

SELECT文または副問合せを使用すると、1つ以上の表、オブジェクト表、ビュー、オブジェクト・ビューまたはマテリアライズド・ビューからデータを取り出すことができます。

SELECT文の結果(またはその一部)が既存のマテリアライズド・ビューと同じ場合、そのマテリアライズド・ビューをSELECT文で指定した1つ以上の表のかわりに使用できます。このような置換をクエリー・リライトといいます。これは、コストの最適化が有効で、QUERY_REWRITE_ENABLEDパラメータがTRUEに設定されている場合にのみ行われます。クエリー・リライトが行われるかどうかを確認する場合は、EXPLAIN PLAN文を使用してください。

参照:

  • 問合せおよび副問合せの概要は、第9章「SQL問合せおよび副問合せ」を参照してください。

  • マテリアライズド・ビューおよびクエリー・リライトの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

  • 「EXPLAIN PLAN」

 

前提条件

表またはマテリアライズド・ビューからデータを選択する場合、表またはマテリアライズド・ビューが自分のスキーマ内にある必要があります。自分のスキーマ内にない場合は、その表またはマテリアライズド・ビューに対するSELECT権限が必要です。

ビューの実表から行を選択する場合、次の条件を2つとも満たしている必要があります。

SELECT ANY TABLEシステム権限を持っている場合、任意の表、マテリアライズド・ビューまたはビューの実表からデータを選択できます。

flashback_query_clauseを使用してOracleフラッシュバック問合せを発行する場合は、SELECT構文のリスト内のオブジェクトに対するSELECT権限が必要です。さらに、SELECT構文のリスト内のオブジェクトに対するFLASHBACKオブジェクト権限またはFLASHBACK ANY TABLEシステム権限のいずれかが必要です。

構文

select::=

画像の説明

subquery_factoring_clause::=for_update_clause::=を参照)

subquery::=

画像の説明

query_block::=order_by_clause::=を参照)

query_block::=

画像の説明

select_list::=table_reference::=join_clause::=where_clause::=hierarchical_query_clause::=group_by_clause::=model_clause::=を参照)

subquery_factoring_clause::=

画像の説明

select_list::=

画像の説明

table_reference::=

画像の説明

query_table_expression::=flashback_query_clause::=pivot_clause::=unpivot_clause::=を参照)

flashback_query_clause::=

画像の説明

query_table_expression::=

画像の説明

subquery_restriction_clause::=table_collection_expression::=を参照)

pivot_clause::=

画像の説明

pivot_for_clause::=

画像の説明

pivot_in_clause::=

画像の説明

unpivot_clause::=

画像の説明

unpivot_in_clause::=

画像の説明

sample_clause::=

画像の説明

partition_extension_clause::=

画像の説明

subquery_restriction_clause::=

画像の説明

table_collection_expression::=

画像の説明

join_clause::=

画像の説明

inner_cross_join_clause::=

画像の説明

table_reference::=を参照)

outer_join_clause::=

画像の説明

table_reference::=query_partition_clause::=を参照)

query_partition_clause::=

画像の説明

outer_join_type::=

画像の説明

where_clause::=

画像の説明

hierarchical_query_clause::=

画像の説明

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::=を参照)

cell_reference_options::=

画像の説明

return_rows_clause::=

画像の説明

reference_model::=

画像の説明

model_column_clauses::=cell_reference_options::=を参照)

main_model::=

画像の説明

model_column_clauses::=cell_reference_options::=model_rules_clause::=を参照)

model_column_clauses::=

画像の説明

model_column::=

画像の説明

model_rules_clause::=

画像の説明

model_iterate_clause::=cell_assignment::=order_by_clause::=を参照)

model_iterate_clause::=

画像の説明

cell_assignment::=

画像の説明

single_column_for_loop::=multi_column_for_loop::=を参照)

single_column_for_loop::=

画像の説明

multi_column_for_loop::=

画像の説明

order_by_clause::=

画像の説明

for_update_clause::=

画像の説明

セマンティクス

subquery_factoring_clause

WITH query_name句を使用すると、副問合せブロックに名前を割り当てることができます。問合せの名前を指定することによって、問合せに複数存在する副問合せブロックを参照することができます。問合せの名前をインライン・ビューまたは一時表として扱うことによって、問合せが最適化されます。

最上位のSELECT文およびほとんどの副問合せでこの句を指定できます。問合せの名前は、主問合せおよび後続のすべての副問合せ(自身の問合せ名を定義する副問合せを除く)から参照できます。

副問合せのファクタリングの制限事項:

この句には、次の制限事項があります。

hint

文の実行計画を選択する場合に、オプティマイザに指示を与えるためのコメントを指定します。

参照:

ヒントの構文および説明については、「ヒントの使用方法」を参照してください。 

DISTINCT | UNIQUE

DISTINCTまたはUNIQUEを指定すると、選択された重複行の1行のみを戻すことができます。これらの2つのキーワードは同義です。重複行とは、SELECT構文のリスト中のそれぞれの式で一致する値を持つ行のことです。

DISTINCTおよびUNIQUE問合せの制限事項:

このタイプの問合せには、次の制限事項があります。

ALL

ALLを指定すると、重複行を含め、選択されたすべての行を戻すことができます。デフォルトはALLです。

*(全列ワイルド・カード)

全列ワイルド・カード(アスタリスク)を指定すると、疑似列を除いて、FROM句に指定されているすべての表、ビューまたはマテリアライズド・ビューのすべての列を選択できます。列は、表、ビューまたはマテリアライズド・ビューの*_TAB_COLUMNSデータ・ディクショナリ・ビューのCOLUMN_IDによって指定されている順序で戻されます。

ビューやマテリアライズド・ビューではなく表から選択する場合、ALTER TABLE SET UNUSED文によってUNUSEDのマークが付けられた列は選択されません。

参照:

ALTER TABLE」、「単純問合せの例:」および「DUAL表からの選択例:」を参照してください。 

select_list

データベースから取り出す列を指定します。

query_name

subquery_factoring_clauseですでに指定されている名前を指定します。select_listquery_nameを指定するには、subquery_factoring_clauseを指定する必要があります。select_listquery_nameを指定するには、query_table_expressionFROM句)でもquery_nameを指定する必要があります。

table.* | view.* | materialized view.*

オブジェクト名の後にピリオドおよびアスタリスクを指定すると、指定した表、ビューまたはマテリアライズド・ビューのすべての列を選択できます。オブジェクトの作成時に指定された順序で列の集合が戻されます。2つ以上の表、ビューまたはマテリアライズド・ビューの行を選択する問合せを結合といいます。

他のユーザーのスキーマの表、ビューまたはマテリアライズド・ビューから選択する場合には、スキーマ修飾子を使用します。schemaを指定しない場合、この表、ビューおよびマテリアライズド・ビューは自分のスキーマ内にあるとみなされます。

参照:

「結合」 

expr

選択する情報を表す式を指定します。リスト中の列が含まれている表、ビューまたはマテリアライズド・ビューがFROM句でschema名で指定されている場合のみ、その列名をschema名で指定できます。オブジェクト型のメンバー・メソッドを指定するときは、メソッドが引数をとらない場合でも、カッコを使用するメソッド名に従う必要があります。

参照:

「順序値の選択例:」 

c_alias

列式の別名を指定します。この別名は、結果セットの列のヘッダーで使用されます。ASキーワードはオプションです。別名によって、問合せ中にSELECT構文のリストの項目名を効果的に変更できます。問合せにおいて、別名はorder_by_clauseで使用できますが、他の句では使用できません。

参照:

  • 複数のマテリアライズド・ビューの問合せで、UNION ALL演算子とともにexpr AS c_alias構文を使用する場合の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

  • exprの構文については、「SQL式」を参照してください。

 

SELECT構文のリストの制限事項:

SELECT構文のリストには、次の制限事項があります。

FROM句

FROM句を指定すると、どのオブジェクトからデータを選択するかを指定できます。

query_table_expression

query_table_expression句を使用すると、表、ビュー、マテリアライズド・ビュー、パーティションまたはサブパーティションの識別、またはオブジェクトを識別する副問合せの指定を行うことができます。

参照:

「副問合せの使用例:」 

ONLY

ONLY句は、ビューのみに適用されます。FROM句のビューが階層に属し、サブビューの行を含めない場合は、ONLY句を使用します。

flashback_query_clause

flashback_query_clauseを使用すると、表、ビューまたはマテリアライズド・ビューの過去のデータを問い合せることができます。

この句は、SQLによるフラッシュバックを実装します。この句を使用すると、SELECT構文のリスト内の各オブジェクトについて異なるシステム変更番号またはタイムスタンプを指定できます。DBMS_FLASHBACKパッケージを使用して、セッション・レベルのフラッシュバックも実装できます。

フラッシュバック問合せを使用すると、行に対して行った変更の履歴を取り出すことができます。VERSIONS_XID疑似列を使用して、変更を行ったトランザクションの対応する識別子を取り出すことができます。また、Oracle Flashback Transaction Queryを発行して、特定の行バージョンを生成したトランザクションの情報を取り出すこともできます。これを行うには、特定のトランザクションIDをFLASHBACK_TRANSACTION_QUERYデータ・ディクショナリ・ビューで問い合せます。

AS OF

AS OFを指定すると、特定のシステム変更番号(SCN)またはタイムスタンプでの問合せによって戻された行の単一のバージョンを取り出すことができます。SCNを指定する場合、exprは数値に評価される必要があります。TIMESTAMPを指定する場合、exprはタイムスタンプ値に評価される必要があります。指定されたシステム変更番号または時刻に存在した行が戻されます。

VERSIONS

VERSIONSを指定すると、問合せによって戻された行の複数のバージョンを取り出すことができます。2つのSCNまたは2つのタイムスタンプ値の間に存在する、行のすべてのコミット済バージョンが戻されます。戻された行には、削除後に再度挿入された行のバージョンが含まれます。

Oracle Databaseでは、バージョン問合せ疑似列のグループを使用して、様々な行のバージョンに関する追加情報を取り出すことができます。詳細は、「バージョン問合せ疑似列」を参照してください。

両方の句を同時に使用する場合、AS OF句によって、SCNまたはデータベースが問合せを発行した時点が判断されます。VERSIONS句によって、AS OFで指定した時点を基準とした行のバージョンが判断されます。トランザクションが、BETWEENの最初の値より前に開始したり、AS OFで指定した時点より後に終了した場合は、行のバージョンとしてNULLが戻されます。

フラッシュバック問合せの制限事項:

この問合せには、次の制限事項があります。

partition_extension_clause

データを取り出す表のパーティションまたはサブパーティションの名前を指定します。

レンジ・パーティション表およびリスト・パーティション・データでは、この句のかわりに、データの取出しをtableの1つ以上のパーティションに制限する条件をWHERE句に指定できます。Oracle Databaseはこの条件を認識し、これらのパーティションのみからデータをフェッチします。ハッシュ・パーティション・データには、このWHERE条件を指定することはできません。

参照:

「パーティション表と索引の参照」および「パーティションからの行の選択例:」を参照してください。 

dblink

表、ビューまたはマテリアライズド・ビューが存在するリモート・データベースのデータベース・リンクの完全名または部分名を指定します。このデータベースは、Oracle Databaseである必要はありません。

参照:

 

dblinkを指定しない場合、その表、ビューまたはマテリアライズド・ビューは、ローカル・データベース内にあるものとみなされます。

データベース・リンクの制限事項:

データベース・リンクには、次の制限事項があります。

table | view | materialized view

データを選択する表、ビューまたはマテリアライズド・ビューの名前を指定します。

sample_clause

sample_clauseを指定すると、表全体からではなく、表のランダムなサンプル・データから選択が行われます。

参照:

「サンプルの選択例:」 

BLOCK

BLOCKを指定すると、ランダムな行サンプリングのかわりに、ランダムなブロック・サンプリングを実行できます。

ブロック・サンプリングは、全表スキャン中または高速全索引スキャン中にのみ使用可能です。より効率的な実行パスが存在する場合、ブロック・サンプリングは実行されません。特定の表または索引に対するブロック・サンプリングを確実に実行する場合は、FULLまたはINDEX_FFSのヒントを使用します。

sample_percent

サンプルに含める行またはブロックの割合(パーセント)を指定します。0.000001以上100未満の範囲の値を指定します。この割合は、各行(ブロック・サンプリングの場合は行の各クラスタ)が、サンプルの一部として選択される可能性を示します。sample_percentに指定した割合の行が表から正確に取り出されるわけではありません。


注意:

統計的に適切でない想定値でこの機能を使用した場合、不正確な、または望ましくない結果になります。 


SEED seed_value

この句を指定すると、今回の実行と次の実行で同じサンプルが戻されます。seed_valueには、0(ゼロ)〜4294967295の整数を指定します。この句を省略した場合、戻されるサンプルは実行ごとに異なります。

問合せ中のサンプリングの制限事項:

ビューからサンプリングを行う場合、ビューがキー保存されていることを確認する必要があります。これを確認する方法の1つは、CREATE TABLE ... AS subquery文を使用して任意の問合せの結果を具体化し、結果として生成された問合せに対してサンプリングを実行することです。

sample_clauseの制限事項:

SAMPLE句は、DML文の副問合せで指定できません。

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制約の名前を指定します。この識別子を省略した場合、その制約にSYS_Cnという形式の名前が自動的に割り当てられます。この場合のnは、その制約名をデータベース内で一意の名前にする整数です。

参照:

「WITH CHECK OPTION句の使用例:」 

table_collection_expression

table_collection_expressionを使用すると、問合せおよびDML操作で、collection_expression値を表として扱うことができます。collection_expressionには、副問合せ、列、ファンクションまたはコレクション・コンストラクタのいずれかを指定できます。その形式にかかわらず、集合値(ネストした表型またはVARRAY型の値)を戻す必要があります。このようなコレクションの要素抽出プロセスをコレクション・ネスト解除といいます。

TABLE式を親表と結合する場合は、オプションのプラス(+)には大きな意味があります。+を指定すると、その2つの外部結合が作成され、コレクション式がNULLの場合でも、外部表の行が問合せで戻されるようになります。


注意:

以前のリリースのOracleでは、collection_expressionが副問合せの場合、table_collection_expressionTHE subqueryと表現していました。現在、このような表現方法は非推奨になっています。 


collection_expressionは、FROM句で左側に定義された表の列を参照できます。これを左相関といいます。左相関はtable_collection_expressionのみで行われます。その他の副問合せは、その副問合せ以外で定義された列を参照することはできません。

オプションの(+)を使用すると、コレクションがNULLまたは空である場合、すべてのフィールドにNULLが設定された行をtable_collection_expressionが戻すように指定できます。この(+)collection_expressionが左相関を使用する場合にのみ有効です。結果は、外部結合の結果と似ています。

UPDATEまたはDELETE操作で副問合せのWHERE句に(+)構文を使用する場合は、副問合せのFROM句に2つの表を指定する必要があります。副問合せに結合が存在しないかぎり、外部結合構文は無視されます。

参照:

 

t_alias

相関名(表、ビュー、マテリアライズド・ビューまたは問合せを評価するための副問合せの別名)を指定します。SELECT構文のリストがオブジェクト型属性またはオブジェクト型メソッドを参照する場合、この別名が必要になります。相関名は、相関問合せ内で最も頻繁に使用されます。表、ビューまたはマテリアライズド・ビューを参照する問合せでは、この別名を参照する必要があります。

参照:

「相関副問合せの使用例:」 

pivot_clause

pivot_clauseを使用すると、行を列に変換し、変換処理中にデータを集計するクロス集計問合せを記述できます。ピボット演算の出力には、最初のデータセットよりも多くの列と少ない行が含まれています。pivot_clauseでは、次の手順が実行されます。

  1. 句の先頭で指定されている集計ファンクションが計算されます。集計ファンクションは、複数の値を戻すようにGROUP BY句を指定する必要がありますが、pivot_clauseには、明示的なGROUP BY句が含まれていません。かわりに、暗黙的なGROUP BYが実行されます。暗黙的なグループ化は、pivot_clauseで参照されていないすべての列、およびpivot_in_clauseで指定されている値セットに基づいています。

  2. 列のグループ化および手順1で計算された集計値は、次のクロス集計出力を生成するように構成されています。

    1. 最初に、pivot_clauseで参照されていないすべての暗黙的なグループ化列が出力されます。

    2. 次に、pivot_in_clause内の値に対応している新しい列が出力されます。集計済の値はそれぞれ、クロス集計の該当する新しい列に書き換えられます。XMLキーワードを指定した場合、結果は、XML文字列としてデータを表記する単一の新しい列となります。

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文字列が生成されます。

参照:

PIVOTおよびUNPIVOTの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。また、「PIVOTおよびUNPIVOTの使用例:」も参照してください。 

unpivot_clause

unpivot_clauseは、列を行に変換します。

アンピボット操作は、複数の値列を単一の列に変更します。このため、値列のすべてのデータ型は、数値、文字などの同じデータ型グループに属している必要があります。

join_clause

適切なjoin_clause構文を使用すると、データが選択され、結合の一部となる表を識別できます。inner_cross_join_clauseを使用すると、内部結合またはクロス結合を指定できます。outer_join_clauseを使用すると、外部結合を指定できます。

結合する行ソースが3つ以上ある場合は、カッコを使用してデフォルトの優先順位を無効にすることができます。たとえば、次のような構文があるとします。

SELECT ... FROM a JOIN (b JOIN c) ...

この場合、bcが結合され、次にその結果とaが結合されます。

参照:

結合の詳細は、「結合」「結合問合せの使用例:」「自己結合の使用例:」および「外部結合の使用例:」を参照してください。 

内部結合

内部結合は、結合条件を満たす行のみを戻します。

INNER

INNERを指定すると、内部結合を明示的に指定できます。

JOIN

JOINキーワードを使用すると、結合の実行を明示的に示すことができます。この構文を使用すると、WHERE句の結合で使用されている、カンマで区切られた表の式を、FROM句の結合構文に置き換えることができます。

ON condition

ON句を使用すると、結合条件を指定できます。これによって、WHERE句の検索またはフィルタ条件とは分離して結合条件を指定できます。

USING (column)

両方の表で同じ名前の列同士を等価結合する場合、USING column句に使用する列を指定します。両方の表で同じ名前の列同士を結合する場合のみ、この句を使用できます。この句の中では、列名を表の名前および別名で修飾しないでください。

クロス結合

CROSSキーワードを使用すると、クロス結合を実行できます。クロス結合は、2つの関係のクロス積を生成します。カンマで区切られたの表記法と基本的に同じです。

外部結合

外部結合は、結合条件を満たすすべての行と、結合条件を満たす他方の表の行を除いた、一方の表のすべての行を戻します。指定可能な外部結合は、結合の両側にtable_reference構文を使用した従来の外部結合と、いずれかの側にquery_partition_clauseを使用したパーティション化された外部結合の2種類です。パーティション化された外部結合は、内部表の各パーティションと外部表の間で結合が行われるという点を除いて、従来の外部結合と同じです。この形式の結合では、対象のディメンションに沿って、選択的に疎データをより密にできます。このプロセスはデータの稠密化といいます。

outer_join_type

実行する外部結合の種類を指定します。

query_partition_clause

query_partition_clauseを使用すると、パーティション化された外部結合を定義できます。このような結合は、問合せによって戻されたパーティションに外部結合を適用し、従来の外部結合構文を拡張します。PARTITION BY句で指定した各式に対する行のパーティションが作成されます。問合せの各パーティションの行は、PARTITION BY式に対して同じ値を持ちます。

query_partition_clauseは、外部結合のいずれかの側で使用できます。パーティション化された外部結合の結果は、パーティション化された結果セットの各パーティションの外部結合と結合の反対側の表との論理和になります。この形式の結果は、疎データの欠損の補完に役立つため、分析計算が簡単になります。

この句を省略した場合、表の式全体(table_referenceに指定したすべてのもの)が単一のパーティションとして扱われるため、従来型の外部結合となります。

分析ファンクションでquery_partition_clauseを使用するには、構文の上位ブランチ(カッコなし)を使用します。この句をモデルの問合せ(model_column_clauses内)またはパーティション化された外部結合(outer_join_clause内)で使用するには、構文の下位ブランチ(カッコ付き)を使用します。

パーティション化された外部結合の制限事項:

パーティション化された外部結合には、次の制限事項があります。

ON condition

ON句を使用すると、結合条件を指定できます。これによって、WHERE句の検索またはフィルタ条件とは分離して結合条件を指定できます。

ON condition句の制限事項:

NATURAL外部結合を使用してこの句を指定することはできません。

USING column

USING句を含む外部結合の場合、問合せによって単一列が戻されます。この単一列は、結合内の一致する2つの列が結合したものです。この結合は、次のように機能します。

COALESCE (a, b) = a if a NOT NULL, else b.

したがって、次のことがいえます。

USING column句の制限事項:

NATURAL JOIN

NATURALキーワードを使用すると、自然結合を実行できます。自然結合は、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列またはコレクション列は、自然結合の一部として指定できません。

where_clause

WHERE条件を指定すると、選択する行を1つ以上の条件を満たす行のみに制限できます。conditionには、有効なSQL条件を指定します。

この句を省略した場合、FROM句に指定されている表、ビューまたはマテリアライズド・ビューのすべての行が戻されます。


注意:

この句がパーティション表またはパーティション索引のDATE列を参照している場合、データベースは、次の条件でのみパーティション・プルーニングを実行します。

  • TO_DATEファンクションで4桁書式マスクを使用して年を完全に指定した表または索引パーティションを作成した場合

  • TO_DATEファンクションで2または4桁書式マスクを使用して問合せのwhere_clauseに日付を指定した場合

 

参照:

 

hierarchical_query_clause

hierarchical_query_clauseを使用すると、階層順序で行を選択できます。

階層問合せを含むSELECT文では、SELECT構文のリスト内のLEVEL疑似列を使用できます。LEVELは、ルート・ノードには1を、ルート・ノードの子であるノードには2を、孫であるノードには3を戻します(以下同様)。階層問合せによって戻されるレベルの数値は、使用可能なユーザー・メモリーによって制限されます。

Oracleは次のように階層問合せを処理します。

この句を指定する場合、ORDER BYおよびGROUP BYを指定すると、CONNECT BY結果の階層順序が破棄されるため、これらの句のどちらも指定しないでください。同じ親の兄弟である行を順序付ける場合は、ORDER SIBLINGS BY句を使用します。

参照:

階層問合せの詳細は、「階層問合せ」および「LEVEL疑似列の使用例:」を参照してください。 

START WITH句

階層問合せのルートとして使用される行を識別する場合の条件を指定します。Oracle Databaseでは、この条件を満たすすべての行がルートとして使用されます。この句を省略した場合、表内のすべての行がルート行として使用されます。

CONNECT BY句

階層の親/子の行の関連を識別する条件を指定します。connect_by_conditionには、第7章「条件」のどの条件でも含めることができます。ただし、親である行を参照するためのPRIOR演算子を使用する必要があります。

参照:

 

group_by_clause

GROUP BY句を指定すると、選択した行を各行のexprの値に基づいてグループ化し、各グループのサマリー情報を1行戻すことができます。この句にCUBEまたはROLLUP拡張要素を指定した場合、標準グループ化の他に超集合グループ化が生成されます。

GROUP BY句の式には、SELECT構文のリストに指定されている列であるかどうかにかかわらず、FROM句の表、ビューおよびマテリアライズド・ビューの列を指定できます。

GROUP BY句は行をグループ化しますが、結果セットの順序は保証しません。グループ化の順序付けを行うには、ORDER BY句を使用します。

参照

  • データを集計するSQLグループ化構文の詳細および例については、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

  • この句の例については、「GROUP_ID」、「GROUPING」および「GROUPING_ID」を参照してください。

  • 「GROUP BY句の使用例:」

 

ROLLUP

simple_grouping_clauseROLLUP操作を使用すると、選択した行をGROUP BYで指定した式n、n-1、n-2、... 0の最初の値に基づいてグループ化し、各グループのサマリー情報を1行戻すことができます。ROLLUP操作をSUMファンクションとともに使用すると、小計値を出力できます。ROLLUPSUMとともに使用すると、最も詳細なレベルの小計から総計までが生成されます。COUNTなどの集計ファンクションは、他の種類の超集合の出力に使用できます。

たとえば、simple_grouping_clauseROLLUP句に式を3つ指定した場合(n=3)、操作の結果はn+1=3+1=4グループになります。

最初のn式の値でグループ化した行を標準行、その他を超集合行といいます。

参照:

マテリアライズド・ビューでROLLUPを使用する場合の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。 

CUBE

simple_grouping_clauseCUBE操作を使用すると、選択した行を、指定した式のあらゆる組合せの値に基づいてグループ化できます。また、各グループのサマリー情報を1行戻すことができます。CUBE操作を使用すると、クロス集計値を出力できます。

たとえば、simple_grouping_clauseCUBE句に式を3つ指定した場合(n=3)、操作の結果は2n = 23 = 8グループになります。n式の値でグループ化した行を標準行、その他を超集合行といいます。

参照:

  • マテリアライズド・ビューでCUBEを使用する場合の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

  • 「GROUP BY CUBE句の使用例:」

 

GROUPING SETS

GROUPING SETSは、データを複数にグループ化するGROUP BY句をさらに拡張したものです。これによって、不要な集計が排除され、効率的に集計できるようになります。必要なグループを指定すると、データベースがCUBEまたはROLLUPによって生成された集計のすべてを実行する必要がなくなります。GROUPING SETS句で指定したすべてのグループ化が計算され、UNION ALL操作で個々のグループ化の結果が組み合されます。UNION ALLは、結果セットが重複行を含むことを許可します。

GROUP BY句では、様々な方法で式を組み合せることができます。

HAVING句

HAVING句を使用すると、戻す行のグループを、指定した条件がTRUEである行のグループのみに制限できます。この句を省略した場合、すべてのグループのサマリー行が戻されます。

where_clauseおよびhierarchical_query_clauseの後に、GROUP BYおよびHAVINGを指定します。GROUP BYおよびHAVING句を両方指定する場合は、どちらを先に指定してもかまいません。

参照:

「HAVING条件の使用例:」 

GROUP BY句の制限事項:

この句には、次の制限事項があります。

model_clause

model_clauseでは、選択した行を多次元配列として表示し、その配列内のセルにランダムにアクセスできます。model_clauseを使用すると、一連のセルの割当てを指定できます。この割当てはルールと呼ばれ、個々のセルおよびセルの範囲に対する計算を実行します。これらのルールは問合せの結果を操作しますが、データベース表は更新しません。

問合せでmodel_clauseを使用する場合、SELECT句およびORDER BY句は、model_column_clausesで定義された列のみを参照する必要があります。

参照:

  • exprの構文については、「SQL式」を、conditionの構文については、第7章「条件」を参照してください。

  • 詳細および例は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

  • 「MODEL句の例:」

 

main_model

main_model句を使用すると、選択した行を多次元配列内で表示する方法および配列内の各セルに適用するルールを定義できます。

model_column_clauses

model_column_clausesを使用すると、問合せの列を、パーティション列、ディメンション列およびメジャー列の3つのグループに定義して分類できます。この3つの副次句内のexprがモデル列の場合、列の別名(c_alias)の指定は任意です。exprがモデル列でない場合、列の別名は必須です。

PARTITION BY

PARTITION BY句を使用すると、選択した行を列の値に基づいてパーティションに分割するために使用する列を指定できます。

DIMENSION BY

DIMENSION BY句を使用すると、パーティション内で行を識別する列を指定できます。ディメンション列およびパーティション列の値は、行のメジャー列に対する配列の索引として使用されます。

MEASURES

MEASURES句を使用すると、計算が実行可能な列を識別できます。個々の行のメジャー列は、パーティション列およびディメンション列の値を指定することによる参照および更新が可能なセルと同様に扱われます。

model_column

model_columnを使用すると、モデルの定義に使用する列を識別できます。exprが列名ではない場合、列の別名が必要です。モデル式の詳細は、「モデル式」を参照してください。

cell_reference_options

cell_reference_options句を使用すると、ルールでNULLまたは値なしを処理する方法および列の一意性を制約する方法を指定できます。

IGNORE NAV

IGNORE NAVを指定すると、指定したデータ型の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ルールが評価されると、次の手順が実行され、アップサートするセル参照のリストが作成されます。

  1. セル参照のすべてのシンボリック述語を満たす既存のセルを検索します。

  2. 記号参照があるディメンションのみを使用して、これらのセルの異なるディメンション値の組合せを検索します。

  3. 位置参照によって指定されたディメンション値を持つ、これらの値の組合せのクロス積が実行されます。

UPSERT ALLのセマンティクスの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

UPSERT

UPSERTを指定すると、ルールの左側で参照されるセルが多次元配列内に存在している場合、セルにルールが適用され、多次元配列内に存在しないセルに対しては新しい行が挿入されます。UPSERT動作は、ルールの左側で位置参照が使用され、単一セルが参照されている場合にのみ適用されます。UPSERTはデフォルトです。位置参照および単一セル参照の詳細は、「cell_assignment」を参照してください。

UPDATEおよびUPSERTは、個々のルールに同様に指定できます。特定のルールにUPDATEまたはUPSERTのいずれかを指定した場合、その指定はRULES句に指定したその他のオプションより優先されます。


UPSERT [ALL]およびUPDATEに関する注意

UPSERT ALLUPSERTまたはUPDATEルールに適切な条件が含まれていない場合は、別のタイプのルールに暗黙的に変換される場合があります。

  • UPSERTルールに存在述語が含まれている場合、そのルールはUPDATEルールとして処理されます。

  • UPSERT ALLルールには、その左側に少なくとも1つの存在述語と1つの修飾述語が必要です。

    • 存在述語がない場合は、UPSERTルールとして処理されます。

    • 修飾述語がない場合は、UPDATEルールとして処理されます。

 

UPDATE

UPDATEを指定するとルールの左側で参照されるセルが多次元配列内に存在している場合、そのセルにルールが適用されます。セルが存在しない場合、割当ては無視されます。

AUTOMATIC ORDER

AUTOMATIC ORDERを指定すると、依存順序に基づいてルールが評価されます。この場合、セルには値が1回のみ割り当てられます。

SEQUENTIAL ORDER

SEQUENTIAL ORDERを指定すると、表示されている順序でルールが評価されます。この場合、セルには値が複数回割り当てられます。SEQUENTIAL ORDERはデフォルトです。

ITERATE ... [UNTIL]

ITERATE...[UNTIL]を使用すると、ルールを繰り返す回数を指定できます。また、早期終了条件も指定できます。UNTIL条件を囲むカッコの使用は任意です。

ITERATE ... [UNTIL]を指定した場合、ルールは表示されている順序で評価されます。model_rules_clauseAUTOMATIC 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=nullFALSEと評価されるため、該当するセルは存在しません。ただし、a[null,2000]のような単一セルの位置参照を使用すると、null = nullがTRUEと評価されるため、xがNULL、yが2000のセルが該当します。単一セルの位置参照を使用すると、ディメンション列がNULLのセルを参照、更新および挿入できます。

記号参照または位置参照を使用して、ディメンション列の値を表す条件または式を指定できます。conditionに集計ファンクションまたはCVファンクションを含めることはできません。また、conditionは単一のディメンション列を参照する必要があります。exprに副問合せを含めることはできません。モデル式の詳細は、「モデル式」を参照してください。

single_column_for_loop

single_column_for_loop句を使用すると、更新するセルの範囲を単一のディメンション列内で指定できます。

IN句を使用すると、ディメンション列の値を値のリストまたは副問合せとして指定できます。副問合せを使用する場合、次の制限事項があります。

FROM句を使用すると、ディメンション列の値の範囲を指定できます。範囲内の増分は不連続でもかまいません。FROM句は、加算および減算がサポートされているデータ型の列にのみ使用できます。INCREMENTおよびDECREMENTの値は、正の値である必要があります。

オプションで、FROM句内でLIKE句を指定することができます。LIKE句のpatternは、単一のパターン一致文字%を含む文字列です。この文字は、実行時にFROM句の現在の増分値または減分値で置き換えられます。

FORループで使用されるディメンション以外のすべてのディメンションが単一セル参照に関係する場合は、式で新しい行を挿入できます。FORループによって生成されたディメンション値の組合せの数は、MODEL句の行制限(10,000)の計算に含まれます。

multi_column_for_loop

multi_column_for_loop句を使用すると、更新するセルの範囲を複数のディメンション列にまたがって指定できます。IN句を使用すると、ディメンション列の値を複数の値のリストまたは副問合せとして指定できます。副問合せを使用する場合、次の制限事項があります。

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句を使用する場合には、次の制限があります。

expr

ルールの右側に指定した単一または複数のセルの値を表す式を指定します。exprに副問合せを含めることはできません。モデル式の詳細は、「モデル式」を参照してください。

return_rows_clause

return_rows_clauseを使用すると、選択されたすべての行を戻すか、モデル・ルールによって更新された行のみを戻すかどうかを指定できます。ALLはデフォルトです。

reference_model

reference_modelは、model_clause内から複数の配列にアクセスする必要がある場合に使用します。この句は、問合せの結果に基づいて、読取り専用の多次元配列を定義します。

reference_model句の副次句は、main_model句と同じセマンティクスを持ちます。詳細は、「cell_reference_options」および「model_column_clauses」を参照してください。

reference_model句の制限事項:

この句には、次の制限事項があります。

集合演算子: UNION、UNION ALL、INTERSECTおよびMINUS

集合演算子は、2つのSELECT文によって戻された行を1つの結果に結合します。それぞれのコンポーネント問合せで選択される列の数とデータ型は同じである必要がありますが、列の長さは異なってもかまいません。結果セット内の列の名前は、集合演算子の前にあるSELECT構文のリスト内の式の名前です。

集合演算子で3つ以上の問合せを結合する場合、隣接する問合せが左から右へ評価されます。副問合せを囲むカッコは任意指定です。この評価順序を変更する場合、カッコを使用します。

これらの演算子の詳細および使用方法の制限事項については、「UNION [ALL]、INTERSECTおよびMINUS演算子」を参照してください。

order_by_clause

ORDER BY句を使用すると、文によって戻された行を順序付けることができます。order_by_clauseを指定しない場合、同じ問合せで取り出される行の順序が異なることがあります。

SIBLINGS

SIBLINGSキーワードは、hierarchical_query_clauseCONNECT 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句には次の制限事項があります。

for_update_clause

FOR UPDATE句を使用すると、トランザクションが終了する前に、選択した行が別のユーザーによってロックまたは更新されることがないように、選択した行をロックできます。最上位のSELECT文でのみ、この句を指定できます。副問合せでは指定できません。


注意:

LOB値を更新する場合、そのLOBを含む行をロックしておく必要があります。行をロックする方法の1つに、埋込みSELECT ... FOR UPDATE文があります。この場合、プログラム言語の1つまたはDBMS_LOBパッケージを使用します。LOBの書込み前に行う行のロックの詳細は、『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』を参照してください。 


親表の行がロックされても、ネストした表の行はロックされません。ネストした表の行をロックする場合、ネストした表を明示的にロックする必要があります。

FOR UPDATE句の制限事項:

この句には、次の制限事項があります。

OF ... column

OF ... column句を使用すると、結合内の特定の表またはビューで選択された行のみをロックできます。OF句の列は、どの表またはビューの行をロックするかを識別する場合にのみ使用します。指定する列は重要ではありません。ただし、列の別名ではなく、実際の列名を指定する必要があります。この句を省略した場合、問合せ内のすべての表の選択された行がロックされます。

NOWAIT | WAIT

NOWAITおよびWAIT句を使用すると、他のユーザーによってロックされている行をSELECT文がロックしようとする場合に処理する方法をOracleに指示できます。

WAITおよびNOWAITのどちらも指定しない場合、行が使用可能になるまで待機した後でSELECT文の結果が戻されます。

SKIP LOCKED

SKIP LOCKEDは、競合するトランザクションを処理するもう1つの方法で、対象のいくつかの行をロックします。SKIP LOCKEDを指定すると、WHERE句で指定された行をロックし、ロック済であることが検出された行をスキップするようにデータベースに指示することができます。この機能は、問合せの目的が、行の実際の内容ではなく、その数を取得することである場合に有用です。

WAIT句およびSKIP LOCKED句の注意事項

WAITまたはSKIP LOCKEDを指定したときに排他モードで表がロックされていると、表のロックが解除されるまではSELECT文の結果が戻りません。WAITでは、指定されている待機時間にかかわらず、SELECT FOR UPDATE句がブロックされます。

副問合せのファクタリング例:

次の文は、結合を含む初期問合せブロックに対する問合せの名前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                              313800
Shipping                           156400
単純問合せの例:

次の文は、部門番号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.salessales_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('2000-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
-----------
        110

SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED(4);

COUNT(*)*10
-----------
        120

SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED (1);

COUNT(*)*10
-----------
        110
フラッシュバック問合せの使用例:

次の文は、サンプル表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
. . .
All Departments                ST_MAN              5       87360
All Departments                All Jobs          107  77798.1308
GROUPING SETS句の使用例:

次の例は、指定した3つのグループで集計した販売の合計を示します。

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 CO SALES$
-------------------- -------- -- --------------
Direct Sales         2000-09  UK      1,378,126
Direct Sales         2000-10  UK      1,388,051
Direct Sales         2000-09  US      2,835,557
Direct Sales         2000-10  US      2,908,706
Internet             2000-09  UK        911,739
Internet             2000-10  UK        876,571
Internet             2000-09  US      1,732,240
Internet             2000-10  US      1,893,753
Direct Sales                  UK      2,766,177
Direct Sales                  US      5,744,263
Internet                      UK      1,788,310
Internet                      US      3,625,993
                     2000-09  UK      2,289,865
                     2000-09  US      4,567,797
                     2000-10  UK      2,264,622
                     2000-10  US      4,802,459

参照:

これらのファンクションの詳細は、「GROUP_ID」、「GROUPING」および「GROUPING_ID」を参照してください。 

階層問合せの例

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_idsalaryおよび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から多次元配列を作成します。また、次の処理も実行されます。

最初のルールでは、ルールの左側で記号参照が使用されているため、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.
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
   FOR UPDATE
   ORDER BY e.employee_id;

次の文は、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
   FOR UPDATE OF e.salary
   ORDER BY e.employee_id;
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
---------- ---------- ----------
      1990    61655.7
      1996     5546.6
      1997        310
      1998   309929.8   100056.6
      1999  1274078.8  1271019.5
      2000   252108.3   393349.4
 
6 rows selected.

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
---------- ------ ------------
      1990 direct      61655.7
      1996 direct       5546.6
      1997 direct          310
      1998 direct     309929.8
      1998 online     100056.6
      1999 direct    1274078.8
      1999 online    1271019.5
      2000 direct     252108.3
      2000 online     393349.4
9 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
---------- ------ ------------
      1990 direct      61655.7
      1990 online
      1996 direct       5546.6
      1996 online
      1997 direct          310
      1997 online
      1998 direct     309929.8
      1998 online     100056.6
      1999 direct    1274078.8
      1999 online    1271019.5
      2000 direct     252108.3
      2000 online     393349.4
 
12 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
------------------- ---------- ------------- ----------------------
. . .
Sciarra             FI_ACCOUNT           100 Finance
Urman               FI_ACCOUNT           100 Finance
Popp                FI_ACCOUNT           100 Finance
. . .

従業員の名前および職種は部門名とは別の表に格納されているため、このデータを戻す場合は結合を使用する必要があります。次の結合条件に従って、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
------------------- ---------- ------------- -----------------------
Russell             SA_MAN                80 Sales
Partners            SA_MAN                80 Sales
Errazuriz           SA_MAN                80 Sales
Cambrault           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 Higgins
          110 Gietz
              Grant
              Zeuss

この結果からは、GrantとZeussという従業員のdepartment_idNULLかどうか、またはそのdepartment_iddepartments表に存在するかどうかは不明です。これを確認するには、完全な外部結合が必要です。

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
06-APR-01 bottle              8
01-APR-01 can                10
01-APR-01 can                15
02-APR-01 can
03-APR-01 can
04-APR-01 can                10
04-APR-01 can                11
05-APR-01 can
06-APR-01 can

15 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              8
06-APR-01 bottle              8
01-APR-01 can                15
01-APR-01 can                15
02-APR-01 can                15
03-APR-01 can                15
04-APR-01 can                11
04-APR-01 can                11
05-APR-01 can                11
06-APR-01 can                11

15 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つの行のみが戻されます。employeessalary列に索引が定義されていない場合、セミ結合を使用すると、問合せのパフォーマンスが向上します。

SELECT * FROM departments 
   WHERE EXISTS 
   (SELECT * FROM employees 
       WHERE departments.department_id = employees.department_id 
       AND employees.salary > 2500)
   ORDER BY department_name; 
表のコレクション例:

DML操作は、表の列として定義された場合にのみ、ネストした表で実行できます。したがって、INSERTDELETEまたはUPDATE文のquery_table_expr_clausetable_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_namedepartment_idaddresshiredateおよび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;

最後に、peoplehr_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); 

次の文は、部門内の平均給与を超える給与を支給されている従業員の情報を戻します。給与情報が格納されているempoyees表に別名を割り当て、相関副問合せではその別名を使用します。

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表の各行について次の手順を実行します。

  1. 行のdepartment_idを判断します。

  2. department_idに基づいて親問合せが評価されます。

  3. 行の部門の平均給与より高い給与の行がある場合は、その行を戻します。

副問合せは、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; 

SET CONSTRAINT[S]

用途

SET CONSTRAINTS文を使用すると、遅延可能な制約の検証を、各DML文の実行後に行うか(IMMEDIATE)、トランザクションのコミット時に行うか(DEFERRED)をトランザクションごとに指定できます。この文を使用して、制約名のリストまたはALL制約のモードを設定できます。

SET CONSTRAINTSモードは、トランザクションの存続期間中、または別のSET CONSTRAINTS文によってモードがリセットされるまで継続します。


注意:

ALTER SESSION文をSET CONSTRAINTS句とともに使用して、すべての遅延可能制約を設定することもできます。 これは、現在のセッションの各トランザクションの開始時にSET CONSTRAINTS文を発行することと同等です。 


トリガー定義の内部でこの文を指定することはできません。

SET CONSTRAINTSは、分散型の文にすることができます。処理中のトランザクションを持つ既存のデータベース・リンクにはSET CONSTRAINTS ALL文の発行時にその発行が通知され、新しいリンクにはトランザクションの開始直後にその発行が通知されます。

前提条件

遅延可能な制約を検証する時期を指定する場合は、表が自分のスキーマ内にある必要があります。自分のスキーマ内にない場合は、制約が適用される表に対するSELECT権限が必要です。

構文

set_constraints::=

画像の説明

セマンティクス

constraint

1つ以上の整合性制約の名前を指定します。

ALL

ALLを指定すると、このトランザクションに対するすべての遅延可能な制約を設定できます。

IMMEDIATE

IMMEDIATEを指定すると、指定された制約が、各制約DML文の実行時に即時にチェックされます。Oracle Databaseでは、チェック済のすべての制約に一貫性があり、他のSET CONSTRAINTS文が発行されていない場合、トランザクションで以前に遅延された制約が最初にチェックされ、その後すぐにそのトランザクションの他の文の制約チェックが継続してチェックされます。制約のチェックに失敗した場合は、エラーが通知されます。その時点で、COMMIT文を実行すると、トランザクション全体が元に戻されます。

COMMITを正常に実行できるかどうかをチェックする方法としてトランザクションの終了直後に制約を行います。制約をトランザクション内の最後の文としてIMMEDIATEに設定することで、予期しないロールバックを回避できます。いずれかの制約のチェックに失敗した場合は、トランザクションをコミットする前にエラーを解決できます。

DEFERRED

DEFERREDを指定すると、遅延可能な制約によって指定された条件が、トランザクションのコミット時に検証されます。


注意:

SET CONSTRAINTS ALL IMMEDIATE文を発行することによって、遅延可能な制約をコミットする前に、それらの制約が完全に適用されたかどうかを検証できます。 


制約の設定例:

次の文は、このトランザクション内のすべての遅延可能な制約が、各DML文の直後に検証されるように設定します。

SET CONSTRAINTS ALL IMMEDIATE;

次の文は、トランザクションのコミット時に3つの遅延制約を検証します。この例では、制約をNOT DEFERRABLEに設定すると失敗します。

SET CONSTRAINTS emp_job_nn, emp_salary_min ,
   hr.jhist_dept_fk@remote DEFERRED;

SET ROLE

用途

データベースでは、ユーザー・ログイン時に、ユーザーに明示的に付与されたすべての権限およびユーザーのすべてのデフォルトのロールが使用可能になります。セッション中、ユーザーまたはアプリケーションは、SET ROLE文を使用して、そのセッションに対してロールを何度でも使用可能または使用禁止にできます。

148を超えるユーザー定義のロールを一度に使用可能にできません。


注意:

ほとんどのロールは、直接的に付与されているか、または他のロールを介して付与されていないかぎり、使用可能または使用禁止にできません。ただし、保護アプリケーション・ロールは、関連付けられているPL/SQLパッケージによって付与して使用可能にすることができます。保護アプリケーション・ロールについては、「USING package」のCREATE ROLEセマンティクスおよび『Oracle Databaseセキュリティ・ガイド』を参照してください。 


SESSION_ROLESデータ・ディクショナリ・ビューを問い合せると、現在使用可能なロールを参照できます。

参照:

  • ロールの作成の詳細は、「CREATE ROLE」を参照してください。

  • ユーザーのデフォルト・ロールの変更については、「ALTER USER」を参照してください。

  • SESSION_ROLESセッション・パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。

 

前提条件

SET ROLE 文に指定するロールが付与されている必要があります。

構文

set_role::=

画像の説明

セマンティクス

role

現行のセッションで使用可能にするロールを指定します。リストされないロールおよび使用可能でないロールは、現行のセッションで使用禁止になります。

IDENTIFIED BY password句では、ロールに対するパスワードを指定します。ロールにパスワードが設定されている場合は、指定する必要があります。

ロールの設定の制限事項:

グローバルに識別されるロールは指定できません。グローバル・ロールは、ログイン時にデフォルトで使用可能になり、後で再度使用可能にすることはできません。

ALL句

ALLを指定すると、現行のセッションに対して付与されているすべてのロールを使用可能にできます。ただし、EXCEPT句に任意に指定されているロールは除きます。

EXCEPT句に指定するロールは、ユーザーに直接付与されている必要があります。他のロールによってユーザーに付与されたものは無効です。

直接付与されているロール、および他のロールを介してユーザーに付与されているロールをEXCEPT句に指定した場合、そのロールの付与先のロールにより、そのロールは使用可能のままになります。

ALL句の制限事項:

このオプションを使用して、ユーザーに直接付与されているパスワード付きのロールを使用可能にすることはできません。

NONE

NONEを指定すると、現行のセッションで、DEFAULTロールを含むすべてのロールを使用禁止にできます。

ロールの設定例:

次の文は、現行のセッションのパスワードwarehouseによって識別されるロールdw_managerを使用可能にします。

SET ROLE dw_manager IDENTIFIED BY warehouse; 

次の文は、現行のセッションで付与されているロールをすべて使用可能にします。

SET ROLE ALL; 

次の文は、dw_managerを除くロールをすべて使用可能にします。

SET ROLE ALL EXCEPT dw_manager;

次の文は、現行のセッションで付与されているすべてのロールを使用禁止にします。

SET ROLE NONE; 

SET TRANSACTION

用途

SET TRANSACTION文を使用すると、現行のトランザクションを読取り専用または読み書き両用として設定したり、分離レベルを設定したり、指定したロールバック・セグメントにトランザクションを割り当てることができます。

TXロックを取得する操作によって、トランザクションが暗黙的に開始されます。

COMMIT文またはROLLBACK文を発行すると、現行のトランザクションが明示的に終了されます。

SET TRANSACTION文によって実行される処理は、現行のトランザクションのみに影響します。他のユーザーまたは他のトランザクションには影響しません。COMMIT文またはROLLBACK文を発行すると、トランザクションは常に終了されます。現行のトランザクションはデータ定義言語(DDL)文の実行の前後でOracle Databaseによって暗黙的にコミットされます。

参照:

COMMIT」および「ROLLBACK」を参照してください。 

前提条件

SET TRANSACTION文を使用する場合、トランザクションの先頭に記述する必要があります。ただし、SET TRANSACTION文が必要ないトランザクションもあります。

構文

set_transaction::=

画像の説明

セマンティクス

READ ONLY

READ ONLY句を使用すると、現行のトランザクションを読取り専用に設定できます。この句では、トランザクション・レベルの読取り一貫性を設定します。

そのトランザクションの後続のすべての問合せでは、トランザクションの開始前にコミットされた変更のみが参照されます。読取り専用トランザクションは、他のユーザーが更新中の1つ以上の表に対して、複数の問合せを実行するレポートに役立ちます。

ユーザーSYSは、この句を使用できません。SYSによる問合せでは、SYSがトランザクションをREAD ONLYに設定した場合でも、トランザクション中の変更が戻されます。

読取り専用トランザクションの制限事項:

読取り専用トランザクションは、次の文のみを使用できます。

READ WRITE

READ WRITEを指定すると、現行のトランザクションを読み書き両用に設定できます。この句では、文レベルの読取り一貫性を設定します。これはデフォルトです。

読み書き両用トランザクションの制限事項:

同一トランザクション内では、読取り一貫性のレベル(トランザクション・レベルおよび文レベル)を切り替えることができません。

ISOLATION LEVEL句

ISOLATION LEVEL句を使用すると、データベースを変更するトランザクションがどのように処理されるかを指定できます。

USE ROLLBACK SEGMENT句


注意:

この句は、UNDO用のロールバック・セグメントを使用している場合にのみ有効です。自動UNDO管理モードでUNDO領域を管理することをお薦めします。データベースを自動UNDOモードで実行すると、この句は無視されます。 


USE ROLLBACK SEGMENTを指定すると、現行のトランザクションを、指定したロールバック・セグメントに割り当てることができます。この句によって、現行のトランザクションは暗黙的に読み書き両用トランザクションに設定されます。

パラレルDMLでは、複数のロールバック・セグメントが必要です。したがって、トランザクションにパラレルDML操作が含まれている場合、この句は無視されます。

NAME句

NAME句を使用すると、現行のトランザクションに名前を割り当てることができます。この句は、分散データベース環境でインダウト・トランザクションを識別および変換する場合に便利です。string値の最大長は255バイトです。

分散トランザクションに対して名前を指定する場合、トランザクションのコミット時に、名前はコミットのコメントとなり、COMMIT文で明示的に指定した任意のコメントを上書きします。

参照:

トランザクションの名前指定の詳細は、『Oracle Database概要』を参照してください。 

トランザクションの設定例:

次の文は、サンプルの注文入力スキーマ(oe)のトロントの倉庫にある在庫の製品と量を計算するもので、毎月の最終日の真夜中に実行されます。このレポートは、別の倉庫の在庫を追加および削除する他のユーザーの影響は受けません。

COMMIT; 

SET TRANSACTION READ ONLY NAME 'Toronto'; 

SELECT product_id, quantity_on_hand FROM inventories
   WHERE warehouse_id = 5
   ORDER BY product_id; 

COMMIT; 

最初のCOMMIT文によって、SET TRANSACTIONがトランザクションの最初の文であることが保証されます。最後のCOMMIT文は、データベースに対する変更を保存するためではありません。単に、読取り専用トランザクションを終了するためのものです。


TRUNCATE CLUSTER

用途


注意:

TRUNCATE CLUSTER文はロールバックできません。 


TRUNCATE CLUSTER文を使用すると、クラスタからすべての行を削除できます。デフォルトでは、次の処理も実行されます。

クラスタを削除して再作成するより、TRUNCATE文で行を削除する方が効果的です。クラスタを削除して再作成した場合、そのクラスタに依存するオブジェクトが無効になり、クラスタに対するオブジェクト権限を再度付与する必要があります。また、表の索引とクラスタを再作成し、その記憶域パラメータを再指定する必要があります。切捨ての場合、このような影響はありません。

TRUNCATE CLUSTER文を使用すると、DELETE文を使用してすべての行を削除するよりも迅速に削除できます。特に、クラスタに索引およびその他の依存オブジェクトが多数ある場合に有効です。

参照:

  • クラスタからデータを削除する他の方法については、「DELETE」および「DROP CLUSTER」を参照してください。

  • 表の切捨ての詳細は、「TRUNCATE TABLE」を参照してください。

 

前提条件

クラスタを切り捨てるには、自分のスキーマ内にそのクラスタがあるか、またはDROP ANY TABLEシステム権限が必要です。

参照:

「表の切捨ての制限事項:」 

構文

truncate_cluster::=

画像の説明

セマンティクス

CLUSTER句

切り捨てるクラスタが設定されているスキーマと、そのクラスタの名前を指定します。なお、索引クラスタは切り捨てられますが、ハッシュ・クラスタは切り捨てられません。schemaを指定しない場合、クラスタは自分のスキーマ内にあるとみなされます。

クラスタを切り捨てた場合、そのクラスタにある表のすべての索引データも自動的に削除されます。

STORAGE句

STORAGE句を使用すると、行の切捨てによって解放された領域をどのようにするかを指定できます。DROP STORAGE句およびREUSE STORAGE句は、対応する索引から削除されたデータの空き領域にも適用されます。

DROP STORAGE

DROP STORAGEを指定すると、クラスタのMINEXTENTSパラメータで割り当てられた領域を除き、クラスタから削除された行からすべての領域の割当てを解除できます。この領域は、後で表領域内の他のオブジェクトで使用できます。また、NEXT記憶域パラメータが、切捨て処理によってセグメントから最後に削除されたエクステントのサイズに設定されます。これはデフォルトです。

REUSE STORAGE

REUSE STORAGEを指定すると、クラスタに割り当てられた削除行の領域を確保できます。STORAGEの値は、表またはクラスタを作成したときの値にリセットされません。この領域は、挿入操作または更新操作によってそのクラスタ内に作成される新規データによってのみ使用されます。記憶域パラメータは現行の設定のまま残ります。

切り捨てるオブジェクトに対して複数の空きリストを指定している場合は、REUSE STORAGE句によって、インスタンスへの空きリストのマッピングも削除され、最高水位標は第1エクステントの先頭までリセットされます。

クラスタの切捨て例:

次の文は、personnelクラスタ内の表のすべての行を削除しますが、空き領域は表に割り当てられたままにしておきます。

TRUNCATE CLUSTER personnel REUSE STORAGE;

この文では、personnelクラスタにある表のすべての索引データも削除されます。


TRUNCATE TABLE

用途


注意:

TRUNCATE TABLE文をロールバックしたり、FLASHBACK TABLE文を使用して、切り捨てられた表の内容を取得することはできません。 


TRUNCATE TABLEを使用すると、表からすべての行を削除できます。デフォルトでは、次の処理も実行されます。

表を削除して再作成するより、TRUNCATE TABLE文で行を削除する方が効果的です。表を削除して再作成した場合、その表に依存するオブジェクトが無効になり、表に対するオブジェクト権限を再度付与する必要があります。また、表の索引、整合性制約およびトリガーを再作成し、その記憶域パラメータを再指定する必要があります。切捨ての場合、このような影響はありません。

TRUNCATE TABLE文を使用すると、DELETE文を使用してすべての行を削除するよりも迅速に削除できます。特に、表にトリガー、索引およびその他の依存オブジェクトが多数ある場合に有効です。

参照:

  • 表からデータを削除する他の方法については、「DELETE」および「DROP TABLE」を参照してください。

  • クラスタの切捨ての詳細は、「TRUNCATE CLUSTER」を参照してください。

 

前提条件

表を切り捨てるには、自分のスキーマ内にその表があるか、またはDROP ANY TABLEシステム権限が必要です。

参照:

「表の切捨ての制限事項:」 

構文

truncate_table::=

画像の説明

セマンティクス

TABLE句

切り捨てる表が設定されているスキーマおよびその表の名前を指定します。クラスタを構成する表は、切り捨てることができません。schemaを指定しない場合、表が自分のクラスタに定義されているとみなされます。

表の切捨ての制限事項:

この文には、次の制限事項があります。

MATERIALIZED VIEW LOG句

MATERIALIZED VIEW LOG句を使用すると、表が切り捨てられた場合に、この表に定義されているマテリアライズド・ビュー・ログを保存するか、または削除するかを指定できます。この句を使用した場合、マテリアライズド・ビューのマスター表を、エクスポートまたはインポートによって再編成できます。この場合、マスター表で定義された主キー・マテリアライズド・ビューを高速リフレッシュする機能は影響を受けません。主キー・マテリアライズド・ビューの連続高速リフレッシュをサポートする場合、マテリアライズド・ビュー・ログに主キー情報を記録する必要があります。


注意:

下位互換性を保つために、MATERIALIZED VIEWのかわりにキーワードSNAPSHOTもサポートされています。 


PRESERVE

PRESERVEを指定すると、マスター表を切り捨てたときにマテリアライズド・ビュー・ログを保存できます。これはデフォルトです。

PURGE

PURGEを指定すると、マスター表を切り捨てたときにマテリアライズド・ビュー・ログを削除できます。

参照:

マテリアライズド・ビュー・ログおよびTRUNCATE文の詳細は、『Oracle Databaseアドバンスト・レプリケーション』を参照してください。 

STORAGE句

STORAGE句を使用すると、行の切捨てによって解放された領域をどのようにするかを指定できます。DROP STORAGE句およびREUSE STORAGE句は、対応する索引から削除されたデータの空き領域にも適用されます。

DROP STORAGE

DROP STORAGEを指定すると、表またはクラスタのMINEXTENTSパラメータで割り当てられた領域を除き、表から削除された行からすべての領域の割当てを解除できます。この領域は、後で表領域内の他のオブジェクトで使用できます。また、NEXT記憶域パラメータが、切捨て処理によってセグメントから最後に削除されたエクステントのサイズに設定されます。これはデフォルトです。

REUSE STORAGE

REUSE STORAGEを指定すると、表に割り当てられた削除行の領域を確保できます。STORAGEの値は、表またはクラスタを作成したときの値にリセットされません。この領域は、挿入操作または更新操作によってその表またはクラスタ内に作成される新規データによってのみ使用されます。記憶域パラメータは現行の設定のまま残ります。

切り捨てるオブジェクトに対して複数の空きリストを指定している場合は、REUSE STORAGE句によって、インスタンスへの空きリストのマッピングも削除され、最高水位標は第1エクステントの先頭までリセットされます。

表の切捨て例:

次の文は、サンプル表hr.employeesの仮想コピーのすべての行を削除して、解放された領域をemployees表が定義されている表領域に戻します。

TRUNCATE TABLE employees_demo; 

ここでは、employees表の索引データもすべて削除され、解放された領域は、それらの索引が定義されていた表領域に戻されます。

切捨て後のマテリアライズド・ビュー・ログの保存例:

次の文は、マテリアライズド・ビュー・ログを保存するTRUNCATE文の使用例です。

TRUNCATE TABLE sales_demo PRESERVE MATERIALIZED VIEW LOG; 

TRUNCATE TABLE orders_demo;

UPDATE

用途

UPDATE文を使用すると、表、ビューの実表またはマテリアライズド・ビューのマスター表の既存の値を変更できます。

前提条件

表の値を更新する場合は、表が自分のスキーマ内にある必要があります。自分のスキーマ内にない場合は、その表に対するUPDATEオブジェクト権限が必要です。

ビューの実表の値を更新する場合は、次の条件を満たす必要があります。

UPDATE ANY TABLEシステム権限を持っている場合は、任意の表またはビューの実表の値を更新できます。

次の場合は、更新するオブジェクトに対するSELECTオブジェクト権限も必要です。

構文

update::=

画像の説明

DML_table_expression_clause::=update_set_clause::=where_clause::=returning_clause::=error_logging_clause::=を参照)

DML_table_expression_clause::=

画像の説明

partition_extension_clause::=、「SELECT」構文の項にあるsubquery::=subquery_restriction_clause::=table_collection_expression::=を参照)

partition_extension_clause::=

画像の説明

subquery_restriction_clause::=

画像の説明

table_collection_expression::=

画像の説明

update_set_clause::=

画像の説明

where_clause::=

画像の説明

returning_clause::=

画像の説明

error_logging_clause::=

画像の説明

セマンティクス

hint

文の実行計画を選択する場合に、オプティマイザに指示を与えるためのコメントを指定します。

UPDATEキーワードの直後にパラレル・ヒントを指定した場合、基礎となるスキャンおよびUPDATE操作の両方をパラレル化できます。

参照:

  • ヒントの構文および説明については、「ヒントの使用方法」を参照してください。

  • パラレル実行の詳細は、『Oracle Database概要』を参照してください。

 

DML_table_expression_clause

ONLY句は、ビューのみに適用されます。UPDATE句のビューが階層に属し、そのどのサブビューの行も変更しない場合は、ONLY構文を指定します。

参照:

「DML_table_expression_clauseの制限事項:」および「表の更新例:」を参照してください。 

schema

更新するオブジェクトが含まれているスキーマを指定します。schemaを指定しない場合、オブジェクトは自分のスキーマ内にあるとみなされます。

table | view | materialized_view | subquery

更新する対象となる、表、ビュー、マテリアライズド・ビュー、または副問合せから戻された列の名前を指定します。表に対してUPDATE文を実行した場合、その表に対応付けられたUPDATEトリガーが起動します。

partition_extension_clause

更新対象のtable内にあるパーティションまたはサブパーティションの名前またはパーティション・キー値を指定します。パーティション表内の値を更新する場合は、パーティション名を指定する必要はありません。ただし、パーティション名を指定した方が、複雑なwhere_clauseを使用するよりも効果的な場合もあります。

参照:

「パーティション表と索引の参照」および「パーティションの更新例:」を参照してください。 

dblink

オブジェクトが格納されているリモート・データベースへのデータベース・リンクの完全名または部分名を指定します。Oracle Databaseの分散機能を使用している場合にかぎり、データベース・リンクを使用してリモート・オブジェクトを更新できます。

dblinkを省略した場合、オブジェクトがローカル・データベース上にあるとみなされます。

参照:

データベース・リンクの参照方法の詳細は、「リモート・データベース内のオブジェクトの参照」を参照してください。 

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制約の名前を指定します。この識別子を省略した場合、その制約にSYS_Cnという形式の名前が自動的に割り当てられます。この場合のnは、その制約名をデータベース内で一意の名前にする整数です。

参照:

「WITH CHECK OPTION句の使用例:」 

table_collection_expression

table_collection_expressionを使用すると、問合せおよびDML操作で、collection_expression値を表として扱うことができます。collection_expressionには、副問合せ、列、ファンクションまたはコレクション・コンストラクタのいずれかを指定できます。その形式にかかわらず、集合値(ネストした表型またはVARRAY型の値)を戻す必要があります。このようなコレクションの要素抽出プロセスをコレクション・ネスト解除といいます。

TABLE式を親表と結合する場合は、オプションのプラス(+)には大きな意味があります。+を指定すると、その2つの外部結合が作成され、コレクション式がNULLの場合でも、外部表の行が問合せで戻されるようになります。


注意:

以前のリリースのOracleでは、collection_expressionが副問合せの場合、table_collection_expressionTHE subqueryと表現していました。現在、このような表現方法は非推奨になっています。 


table_collection_expressionを使用して、ある表の行を別の表の行を基にして更新できます。たとえば、四半期ごとの売上表を、年度ごとの売上表にまとめることができます。

t_alias

文中で参照する表、ビューまたは副問合せの相関名(別名)を指定します。DML_table_expression_clauseがいずれかのオブジェクト型属性またはオブジェクト型メソッドを参照する場合、この別名が必要です。

参照:

「相関更新の例:」 

DML_table_expression_clauseの制限事項:

この句には、次の制限事項があります。

update_set_clause

update_set_clauseを使用すると、列の値を設定できます。

column

更新するオブジェクトの列の名前を指定します。update_set_clauseに表の列を指定しない場合、その列の値は変更されません。

columnがLOBオブジェクト属性を参照している場合、まず空またはNULLの値で初期化する必要があります。リテラルで更新はできません。また、UPDATE以外のSQL文を使用してLOB値を更新する場合は、LOBを含む行を最初にロックしておく必要があります。詳細は、「for_update_clause」を参照してください。

columnが仮想列である場合、ここで指定することはできません。この場合、仮想列の導出元となっている値を更新する必要があります。

columnがパーティション表のパーティション化キーに含まれる場合、別のパーティションまたはサブパーティションに行を移動する列の値を変更すると、行の移動を有効にしないかぎり、UPDATEは正常に実行されません。「CREATE TABLE」の「row_movement_clause」または「ALTER TABLE」を参照してください。

また、columnがリスト・パーティション表のパーティション化キーの一部である場合、パーティションのpartition_valueリストに存在していない列の値を指定すると、UPDATEは正常に実行されません。

subquery

更新される行ごとに1行ずつ戻す副問合せを指定します。

副問合せ内でflashback_query_clauseを使用すると、過去のデータでtableを更新できます。 この句の詳細は、「SELECT」の「flashback_query_clause」を参照してください。

参照:

 

expr

対応する列に割り当てられた新しい値に変換する式を指定します。


参照:

exprの構文については、第6章「式」および「オブジェクト表の更新例:」を参照してください。 


DEFAULT

DEFAULTを指定すると、以前に列のデフォルト値として指定した値を列に設定できます。対応する列に対してデフォルト値を指定していない場合、列にNULLが設定されます。

デフォルト値への更新の制限事項:

ビューを更新する場合は、DEFAULTを指定できません。

VALUE句

VALUE句を使用すると、オブジェクト表の行全体を指定できます。

VALUE句の制限事項:

この句は、オブジェクト表に対してのみ指定できます。


注意:

RAW列に文字列リテラルを挿入する場合、後続の問合せ中にRAW列にある索引は使用されずに、全表スキャンが行われます。 


参照:

「オブジェクト表の更新例:」 

where_clause

where_clauseを使用すると、指定した条件が真の行のみが更新されるように制限できます。この句を指定しない場合、表またはビューのすべての行が更新されます。conditionの構文は、第7章「条件」を参照してください。

where_clauseは、値を更新する行を決定します。where_clauseを指定しない場合、すべての行が更新されます。where_clauseの条件を満たす行ごとに、update_set_clauseの等号演算子(=)の左側にある列に、演算子の対応する右側の式の値が設定されます。式は行が更新される場合に評価されます。

returning_clause

この句を使用すると、DML文に影響される行を取り出すことができます。この句は、表、マテリアライズド・ビュー、および単一の実表を持つビューに指定できます。

returning_clauseを指定したDML文を単一行に実行すると、影響された行、ROWID、および処理された行へのREFを使用している列式が取り出され、ホスト変数またはPL/SQL変数に格納されます。

returning_clauseを指定したDML文を複数行に実行すると、式の値、ROWIDおよび処理された行に関連するREFがバインド配列に格納されます。

expr

exprリストの各項目は、適切な構文で表す必要があります。

INTO

INTO句を指定すると、変更された行の値を、data_itemリストに指定する変数に格納できます。

data_item

取り出されたexpr値を格納するホスト変数またはPL/SQL変数を指定します。

RETURNINGリストの各式については、INTOリストに、対応する型に互換性があるPL/SQL変数またはホスト変数を指定する必要があります。

RETURNING句の制限事項:

RETURNING句には、次の制限事項があります。

error_logging_clause

error_logging_clauseUPDATE文での動作は、INSERT文の場合と同じです。詳細は、INSERT文の「error_logging_clause」を参照してください。

参照:

「エラー・ロギングによる表への挿入例:」 

表の更新例:

次の文は、職種がSH_CLERKのすべての従業員の歩合にNULL値を指定します。

UPDATE employees
   SET commission_pct = NULL
   WHERE job_id = 'SH_CLERK';

次の文は、Douglas Grantを部門20の管理者に昇格させ、給与を1,000ドル引き上げます。

UPDATE employees SET 
    job_id = 'SA_MAN', salary = salary + 1000, department_id = 120 
    WHERE first_name||' '||last_name = 'Douglas Grant'; 

次の文は、remoteデータベースのemployees表の従業員の給与を増加します。

UPDATE employees@remote
   SET salary = salary*1.1
   WHERE last_name = 'Baer';

次の例は、UPDATE文の次の構文要素を示します。

このUPDATE文によって、次の処理が実行されます。

パーティションの更新例:

次の文は、sales表の1つのパーティションの値を更新します。

UPDATE sales PARTITION (sales_q1_1999) s
   SET s.promo_id = 494
   WHERE amount_sold > 1000;
オブジェクト表の更新例:

次の文は、2つのオブジェクト表people_demo1およびpeople_demo2を作成します。ここで使用するpeople_typオブジェクトは、「表のコレクション例:」で作成したものです。この例では、people_demo2から行を選択してpeople_demo1の行を更新する方法を示します。

CREATE TABLE people_demo1 OF people_typ;

CREATE TABLE people_demo2 OF people_typ;

UPDATE people_demo1 p SET VALUE(p) =
   (SELECT VALUE(q) FROM people_demo2 q
    WHERE p.department_id = q.department_id)
   WHERE p.department_id = 10;

この例では、SET句と副問合せの両方で、VALUEオブジェクト参照ファンクションを使用します。

相関更新の例:

相関副問合せを使用してネストした表の行を更新する例は、「表のコレクション例:」を参照してください。

UPDATE操作中にRETURNING句を使用する例:

次の文は、更新された行の値を戻し、PL/SQL変数bnd1bnd2bnd3に結果を格納します。

UPDATE employees
  SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
  WHERE last_name = 'Jones'
  RETURNING salary*0.25, last_name, department_id
    INTO :bnd1, :bnd2, :bnd3;

次の文は、RETURNING句の式で単一セットの集計ファンクションを指定できることを示します。

UPDATE employees
   SET salary = salary * 1.1
   WHERE department_id = 100
   RETURNING SUM(salary) INTO :bnd1;

戻る 次へ
Oracle
Copyright © 1996, 2008, Oracle Corporation.

All Rights Reserved.
目次
目次
索引
索引