11g リリース1（11.1）
E05750-03
 目次 |
 索引 |
| Oracle Database SQL言語リファレンス 11g リリース1（11.1） E05750-03 |
|
この章では、次のSQL文について説明します。
SAVEPOINT文を使用すると、後でロールバックできるシステム変更番号（SCN）の名前を作成できます。
|
参照:
|
ありません。
作成するセーブポイントの名前を指定します。
同一トランザクション内のセーブポイント名は、区別する必要があります。同じ識別子のセーブポイントを作成した場合、最初のセーブポイントは消去されます。セーブポイントを作成した後は、処理の継続、作業のコミット、トランザクション全体のロールバックまたはセーブポイントまでのロールバックを実行できます。
次の文は、サンプル表hr.employeesのBandaとGreeneの給与を更新するために、部門の給与合計が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文または副問合せを使用すると、1つ以上の表、オブジェクト表、ビュー、オブジェクト・ビューまたはマテリアライズド・ビューからデータを取り出すことができます。
SELECT文の結果（またはその一部）が既存のマテリアライズド・ビューと同じ場合、そのマテリアライズド・ビューをSELECT文で指定した1つ以上の表のかわりに使用できます。このような置換をクエリー・リライトといいます。これは、コストの最適化が有効で、QUERY_REWRITE_ENABLEDパラメータがTRUEに設定されている場合にのみ行われます。クエリー・リライトが行われるかどうかを確認する場合は、EXPLAIN PLAN文を使用してください。
|
参照:
|
表またはマテリアライズド・ビューからデータを選択する場合、表またはマテリアライズド・ビューが自分のスキーマ内にある必要があります。自分のスキーマ内にない場合は、その表またはマテリアライズド・ビューに対するSELECT権限が必要です。
ビューの実表から行を選択する場合、次の条件を2つとも満たしている必要があります。
SELECT ANY TABLEシステム権限を持っている場合、任意の表、マテリアライズド・ビューまたはビューの実表からデータを選択できます。
flashback_query_clauseを使用してOracleフラッシュバック問合せを発行する場合は、SELECT構文のリスト内のオブジェクトに対するSELECT権限が必要です。さらに、SELECT構文のリスト内のオブジェクトに対するFLASHBACKオブジェクト権限またはFLASHBACK ANY TABLEシステム権限のいずれかが必要です。
（subquery_factoring_clause::=、for_update_clause::=を参照）
（query_block::=、order_by_clause::=を参照）
（select_list::=、table_reference::=、join_clause::=、where_clause::=、hierarchical_query_clause::=、group_by_clause::=、model_clause::=を参照）
（query_table_expression::=、flashback_query_clause::=、pivot_clause::=、unpivot_clause::=を参照）
（subquery_restriction_clause::=、table_collection_expression::=を参照）
（table_reference::=を参照）
（table_reference::=、query_partition_clause::=を参照）
（rollup_cube_clause::=、grouping_sets_clause::=を参照）
（grouping_expression_list::=を参照）
（rollup_cube_clause::=、grouping_expression_list::=を参照）
（cell_reference_options::=、return_rows_clause::=、reference_model::=、main_model::=を参照）
（model_column_clauses::=、cell_reference_options::=を参照）
（model_column_clauses::=、cell_reference_options::=、model_rules_clause::=を参照）
（model_iterate_clause::=、cell_assignment::=、order_by_clause::=を参照）
（single_column_for_loop::=、multi_column_for_loop::=を参照）
WITH query_name句を使用すると、副問合せブロックに名前を割り当てることができます。問合せの名前を指定することによって、問合せに複数存在する副問合せブロックを参照することができます。問合せの名前をインライン・ビューまたは一時表として扱うことによって、問合せが最適化されます。
最上位のSELECT文およびほとんどの副問合せでこの句を指定できます。問合せの名前は、主問合せおよび後続のすべての副問合せ（自身の問合せ名を定義する副問合せを除く）から参照できます。
この句には、次の制限事項があります。
subquery_factoring_clauseは、1つのSQL文内に１つのみ指定できます。query_nameは、それ自体の副問合せの中では指定できません。ただし、subquery_factoring_clauseで定義したquery_nameは、そのsubquery_factoring_clause内の後続の任意の名前付き問合せブロックで使用できます。
query_nameを使用できませんが、各問合せのFROM句ではquery_nameを使用できます。文の実行計画を選択する場合に、オプティマイザに指示を与えるためのコメントを指定します。
DISTINCTまたはUNIQUEを指定すると、選択された重複行の1行のみを戻すことができます。これらの2つのキーワードは同義です。重複行とは、SELECT構文のリスト中のそれぞれの式で一致する値を持つ行のことです。
このタイプの問合せには、次の制限事項があります。
DISTINCTまたはUNIQUEを指定する場合、SELECT構文のリスト中の式すべての総バイト数は、データ・ブロックのサイズからオーバーヘッド分を引いたサイズに制限されます。このサイズは、初期化パラメータDB_BLOCK_SIZEによって指定されます。
select_listにLOB列が含まれている場合、DISTINCTは指定できません。
ALLを指定すると、重複行を含め、選択されたすべての行を戻すことができます。デフォルトはALLです。
全列ワイルド・カード（アスタリスク）を指定すると、疑似列を除いて、FROM句に指定されているすべての表、ビューまたはマテリアライズド・ビューのすべての列を選択できます。列は、表、ビューまたはマテリアライズド・ビューの*_TAB_COLUMNSデータ・ディクショナリ・ビューのCOLUMN_IDによって指定されている順序で戻されます。
ビューやマテリアライズド・ビューではなく表から選択する場合、ALTER TABLE SET UNUSED文によってUNUSEDのマークが付けられた列は選択されません。
データベースから取り出す列を指定します。
subquery_factoring_clauseですでに指定されている名前を指定します。select_listでquery_nameを指定するには、subquery_factoring_clauseを指定する必要があります。select_listでquery_nameを指定するには、query_table_expression（FROM句）でもquery_nameを指定する必要があります。
オブジェクト名の後にピリオドおよびアスタリスクを指定すると、指定した表、ビューまたはマテリアライズド・ビューのすべての列を選択できます。オブジェクトの作成時に指定された順序で列の集合が戻されます。2つ以上の表、ビューまたはマテリアライズド・ビューの行を選択する問合せを結合といいます。
他のユーザーのスキーマの表、ビューまたはマテリアライズド・ビューから選択する場合には、スキーマ修飾子を使用します。schemaを指定しない場合、この表、ビューおよびマテリアライズド・ビューは自分のスキーマ内にあるとみなされます。
選択する情報を表す式を指定します。リスト中の列が含まれている表、ビューまたはマテリアライズド・ビューがFROM句でschema名で指定されている場合のみ、その列名をschema名で指定できます。オブジェクト型のメンバー・メソッドを指定するときは、メソッドが引数をとらない場合でも、カッコを使用するメソッド名に従う必要があります。
列式の別名を指定します。この別名は、結果セットの列のヘッダーで使用されます。ASキーワードはオプションです。別名によって、問合せ中にSELECT構文のリストの項目名を効果的に変更できます。問合せにおいて、別名はorder_by_clauseで使用できますが、他の句では使用できません。
|
参照:
|
SELECT構文のリストには、次の制限事項があります。
FROM句で結合を指定した場合、表の名前または表の別名でその列名を修飾する必要があります。
FROM句を指定すると、どのオブジェクトからデータを選択するかを指定できます。
query_table_expression句を使用すると、表、ビュー、マテリアライズド・ビュー、パーティションまたはサブパーティションの識別、またはオブジェクトを識別する副問合せの指定を行うことができます。
ONLY句は、ビューのみに適用されます。FROM句のビューが階層に属し、サブビューの行を含めない場合は、ONLY句を使用します。
flashback_query_clauseを使用すると、表、ビューまたはマテリアライズド・ビューの過去のデータを問い合せることができます。
この句は、SQLによるフラッシュバックを実装します。この句を使用すると、SELECT構文のリスト内の各オブジェクトについて異なるシステム変更番号またはタイムスタンプを指定できます。DBMS_FLASHBACKパッケージを使用して、セッション・レベルのフラッシュバックも実装できます。
フラッシュバック問合せを使用すると、行に対して行った変更の履歴を取り出すことができます。VERSIONS_XID疑似列を使用して、変更を行ったトランザクションの対応する識別子を取り出すことができます。また、Oracle Flashback Transaction Queryを発行して、特定の行バージョンを生成したトランザクションの情報を取り出すこともできます。これを行うには、特定のトランザクションIDをFLASHBACK_TRANSACTION_QUERYデータ・ディクショナリ・ビューで問い合せます。
AS OFを指定すると、特定のシステム変更番号（SCN）またはタイムスタンプでの問合せによって戻された行の単一のバージョンを取り出すことができます。SCNを指定する場合、exprは数値に評価される必要があります。TIMESTAMPを指定する場合、exprはタイムスタンプ値に評価される必要があります。指定されたシステム変更番号または時刻に存在した行が戻されます。
VERSIONSを指定すると、問合せによって戻された行の複数のバージョンを取り出すことができます。2つのSCNまたは2つのタイムスタンプ値の間に存在する、行のすべてのコミット済バージョンが戻されます。戻された行には、削除後に再度挿入された行のバージョンが含まれます。
BETWEEN SCN ...を指定すると、2つのSCNの間に存在する行のバージョンを取り出すことができます。式は、両方とも数値に評価される必要があります。MINVALUEおよびMAXVALUEは、それぞれ使用可能な一番古いデータおよび最新のデータのSCNに解決されます。
BETWEEN TIMESTAMP ...を指定すると、2つのタイムスタンプの間に存在する行のバージョンを取り出すことができます。式は、両方ともタイムスタンプ値に評価される必要があります。MINVALUEおよびMAXVALUEは、それぞれ使用可能な一番古いデータおよび最新のデータのタイムスタンプに解決されます。
Oracle Databaseでは、バージョン問合せ疑似列のグループを使用して、様々な行のバージョンに関する追加情報を取り出すことができます。詳細は、「バージョン問合せ疑似列」を参照してください。
両方の句を同時に使用する場合、AS OF句によって、SCNまたはデータベースが問合せを発行した時点が判断されます。VERSIONS句によって、AS OFで指定した時点を基準とした行のバージョンが判断されます。トランザクションが、BETWEENの最初の値より前に開始したり、AS OFで指定した時点より後に終了した場合は、行のバージョンとしてNULLが戻されます。
この問合せには、次の制限事項があります。
AS OF句の式には、副問合せを指定できません。
VERSIONS句を使用できません。
VERSIONS句を使用できません。ただし、ビューを定義する問合せには、VERSIONS構文を使用できます。
query_table_expressionでquery_nameを指定した場合、この句は指定できません。
参照:
DBMS_FLASHBACKパッケージを使用したセッション・レベルのフラッシュバックの詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』および『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
FLASHBACK_TRANSACTION_QUERYに関する説明を参照してください。
データを取り出す表のパーティションまたはサブパーティションの名前を指定します。
レンジ・パーティション表およびリスト・パーティション・データでは、この句のかわりに、データの取出しをtableの1つ以上のパーティションに制限する条件をWHERE句に指定できます。Oracle Databaseはこの条件を認識し、これらのパーティションのみからデータをフェッチします。ハッシュ・パーティション・データには、このWHERE条件を指定することはできません。
表、ビューまたはマテリアライズド・ビューが存在するリモート・データベースのデータベース・リンクの完全名または部分名を指定します。このデータベースは、Oracle Databaseである必要はありません。
|
参照:
|
dblinkを指定しない場合、その表、ビューまたはマテリアライズド・ビューは、ローカル・データベース内にあるものとみなされます。
データベース・リンクには、次の制限事項があります。
データを選択する表、ビューまたはマテリアライズド・ビューの名前を指定します。
sample_clauseを指定すると、表全体からではなく、表のランダムなサンプル・データから選択が行われます。
BLOCKを指定すると、ランダムな行サンプリングのかわりに、ランダムなブロック・サンプリングを実行できます。
ブロック・サンプリングは、全表スキャン中または高速全索引スキャン中にのみ使用可能です。より効率的な実行パスが存在する場合、ブロック・サンプリングは実行されません。特定の表または索引に対するブロック・サンプリングを確実に実行する場合は、FULLまたはINDEX_FFSのヒントを使用します。
サンプルに含める行またはブロックの割合（パーセント）を指定します。0.000001以上100未満の範囲の値を指定します。この割合は、各行（ブロック・サンプリングの場合は行の各クラスタ）が、サンプルの一部として選択される可能性を示します。sample_percentに指定した割合の行が表から正確に取り出されるわけではありません。
この句を指定すると、今回の実行と次の実行で同じサンプルが戻されます。seed_valueには、0（ゼロ）〜4294967295の整数を指定します。この句を省略した場合、戻されるサンプルは実行ごとに異なります。
ビューからサンプリングを行う場合、ビューがキー保存されていることを確認する必要があります。これを確認する方法の1つは、CREATE TABLE ... AS subquery文を使用して任意の問合せの結果を具体化し、結果として生成された問合せに対してサンプリングを実行することです。
SAMPLE句は、DML文の副問合せで指定できません。
subquery_restriction_clauseを使用すると、次のいずれかの方法で副問合せを制限できます。
WITH READ ONLYを指定すると、表またはビューを更新禁止にできます。
WITH CHECK OPTIONを指定すると、副問合せに含まれない行を生成する表またはビューの変更を禁止できます。この句をDML文の副問合せ内で使用する場合、FROM句内の副問合せには指定できますが、WHERE句内の副問合せには指定できません。
CHECK OPTION制約の名前を指定します。この識別子を省略した場合、その制約にSYS_Cnという形式の名前が自動的に割り当てられます。この場合のnは、その制約名をデータベース内で一意の名前にする整数です。
table_collection_expressionを使用すると、問合せおよびDML操作で、collection_expression値を表として扱うことができます。collection_expressionには、副問合せ、列、ファンクションまたはコレクション・コンストラクタのいずれかを指定できます。その形式にかかわらず、集合値（ネストした表型またはVARRAY型の値）を戻す必要があります。このようなコレクションの要素抽出プロセスをコレクション・ネスト解除といいます。
TABLE式を親表と結合する場合は、オプションのプラス（+）には大きな意味があります。+を指定すると、その2つの外部結合が作成され、コレクション式がNULLの場合でも、外部表の行が問合せで戻されるようになります。
collection_expressionは、FROM句で左側に定義された表の列を参照できます。これを左相関といいます。左相関はtable_collection_expressionのみで行われます。その他の副問合せは、その副問合せ以外で定義された列を参照することはできません。
オプションの(+)を使用すると、コレクションがNULLまたは空である場合、すべてのフィールドにNULLが設定された行をtable_collection_expressionが戻すように指定できます。この(+)はcollection_expressionが左相関を使用する場合にのみ有効です。結果は、外部結合の結果と似ています。
UPDATEまたはDELETE操作で副問合せのWHERE句に(+)構文を使用する場合は、副問合せのFROM句に2つの表を指定する必要があります。副問合せに結合が存在しないかぎり、外部結合構文は無視されます。
相関名（表、ビュー、マテリアライズド・ビューまたは問合せを評価するための副問合せの別名）を指定します。SELECT構文のリストがオブジェクト型属性またはオブジェクト型メソッドを参照する場合、この別名が必要になります。相関名は、相関問合せ内で最も頻繁に使用されます。表、ビューまたはマテリアライズド・ビューを参照する問合せでは、この別名を参照する必要があります。
pivot_clauseを使用すると、行を列に変換し、変換処理中にデータを集計するクロス集計問合せを記述できます。ピボット演算の出力には、最初のデータセットよりも多くの列と少ない行が含まれています。pivot_clauseでは、次の手順が実行されます。
GROUP BY句を指定する必要がありますが、pivot_clauseには、明示的なGROUP BY句が含まれていません。かわりに、暗黙的なGROUP BYが実行されます。暗黙的なグループ化は、pivot_clauseで参照されていないすべての列、およびpivot_in_clauseで指定されている値セットに基づいています。
pivot_clauseの副次句のセマンティクスは、次のとおりです。
オプションのXMLキーワードは、問合せのXML出力を生成します。XMLキーワードを指定すると、pivot_in_clauseには、副問合せまたはワイルド・カード・キーワードANYを含めることができます。副問合せおよびANYワイルド・カードは、pivot_in_clause値が事前にわかっていない場合に有効です。XML出力では、ピボット列の値が実行時に評価されます。pivot_in_clauseで式を使用して明示的なピボット値を指定する場合は、XMLを指定することができません。
XML出力が生成される際、集計ファンクションが各ピボット値に適用され、データベースによって、値とメジャーのすべてのペアのXML文字列を含むXMLTypeの列が戻されます。
ピボット列の定数値への評価を行う式を指定します。オプションで、各ピボット列値の別名を指定できます。別名がない場合は、列ヘッダーが引用識別子となります。
subqueryは、XMLキーワードとともにのみ使用されます。subqueryを指定すると、subqueryによって検出されたすべての値がピボットに使用されます。出力は、XML以外のピボット問合せによって戻されるクロス集計書式とは異なります。pivot_in_clauseで指定されている複数の列のかわりに、subqueryでは、XML文字列の列が1つ生成されます。各行のXML文字列は、その行の暗黙的なGROUP BY値に対応する集計データを保持します。入力データに対応する行がない場合でも、各出力行のXML文字列には、subqueryによって検出されたすべてのピボット値が含まれています。
subqueryは、ピボット問合せの実行時に、一意の値リストを戻します。subqueryが一意の値を戻さない場合、Oracle Databaseによってランタイム・エラーが生成されます。問合せが一意の値を戻すかどうかがわからない場合は、subqueryにDISTINCTキーワードを使用します。
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など、カテゴリ値を指定する名前が含まれています。オプションのaliasを指定すると、必要な値に列名をマップすることができます。
アンピボット操作は、複数の値列を単一の列に変更します。このため、値列のすべてのデータ型は、数値、文字などの同じデータ型グループに属している必要があります。
CHARの場合、アンピボットされる列はCHARになります。値列がVARCHAR2の場合、アンピボットされる列はVARCHAR2になります。
NUMBERの場合、アンピボットされる列はNUMBERになります。値列がBINARY_DOUBLEの場合、アンピボットされる列はBINARY_DOUBLEになります。BINARY_DOUBLEの値列はないが、いずれかの値列がBINARY_FLOATの場合、アンピボットされる列はBINARY_FLOATになります。
適切なjoin_clause構文を使用すると、データが選択され、結合の一部となる表を識別できます。inner_cross_join_clauseを使用すると、内部結合またはクロス結合を指定できます。outer_join_clauseを使用すると、外部結合を指定できます。
結合する行ソースが3つ以上ある場合は、カッコを使用してデフォルトの優先順位を無効にすることができます。たとえば、次のような構文があるとします。
SELECT ... FROM a JOIN (b JOIN c) ...
この場合、bとcが結合され、次にその結果とaが結合されます。
内部結合は、結合条件を満たす行のみを戻します。
INNERを指定すると、内部結合を明示的に指定できます。
JOINキーワードを使用すると、結合の実行を明示的に示すことができます。この構文を使用すると、WHERE句の結合で使用されている、カンマで区切られた表の式を、FROM句の結合構文に置き換えることができます。
ON句を使用すると、結合条件を指定できます。これによって、WHERE句の検索またはフィルタ条件とは分離して結合条件を指定できます。
両方の表で同じ名前の列同士を等価結合する場合、USING column句に使用する列を指定します。両方の表で同じ名前の列同士を結合する場合のみ、この句を使用できます。この句の中では、列名を表の名前および別名で修飾しないでください。
CROSSキーワードを使用すると、クロス結合を実行できます。クロス結合は、2つの関係のクロス積を生成します。カンマで区切られたの表記法と基本的に同じです。
外部結合は、結合条件を満たすすべての行と、結合条件を満たす他方の表の行を除いた、一方の表のすべての行を戻します。指定可能な外部結合は、結合の両側にtable_reference構文を使用した従来の外部結合と、いずれかの側にquery_partition_clauseを使用したパーティション化された外部結合の2種類です。パーティション化された外部結合は、内部表の各パーティションと外部表の間で結合が行われるという点を除いて、従来の外部結合と同じです。この形式の結合では、対象のディメンションに沿って、選択的に疎データをより密にできます。このプロセスはデータの稠密化といいます。
実行する外部結合の種類を指定します。
RIGHTを指定すると、右側外部結合が実行されます。
LEFTを指定すると、左側外部結合が実行されます。
FULLを指定すると、完全な外部結合または両側外部結合が実行されます。内部結合に加え、内部結合の結果に戻されない両方の表からの行は、保持され、NULLで拡張されます。
RIGHT、LEFTまたはFULLの後にオプションのOUTERキーワードを指定し、外部結合の実行を明示的に示すことができます。
query_partition_clauseを使用すると、パーティション化された外部結合を定義できます。このような結合は、問合せによって戻されたパーティションに外部結合を適用し、従来の外部結合構文を拡張します。PARTITION BY句で指定した各式に対する行のパーティションが作成されます。問合せの各パーティションの行は、PARTITION BY式に対して同じ値を持ちます。
query_partition_clauseは、外部結合のいずれかの側で使用できます。パーティション化された外部結合の結果は、パーティション化された結果セットの各パーティションの外部結合と結合の反対側の表との論理和になります。この形式の結果は、疎データの欠損の補完に役立つため、分析計算が簡単になります。
この句を省略した場合、表の式全体（table_referenceに指定したすべてのもの）が単一のパーティションとして扱われるため、従来型の外部結合となります。
分析ファンクションでquery_partition_clauseを使用するには、構文の上位ブランチ（カッコなし）を使用します。この句をモデルの問合せ（model_column_clauses内）またはパーティション化された外部結合（outer_join_clause内）で使用するには、構文の下位ブランチ（カッコ付き）を使用します。
パーティション化された外部結合には、次の制限事項があります。
query_partition_clauseは、結合の右側または左側に指定できますが、両方に指定することはできません。
FULL）は指定できません。
ON句を使用して外部結合にquery_partition_clauseを指定した場合、ON条件内には副問合せを指定できません。
参照:
ON句を使用すると、結合条件を指定できます。これによって、WHERE句の検索またはフィルタ条件とは分離して結合条件を指定できます。
NATURAL外部結合を使用してこの句を指定することはできません。
USING句を含む外部結合の場合、問合せによって単一列が戻されます。この単一列は、結合内の一致する2つの列が結合したものです。この結合は、次のように機能します。
COALESCE (a, b) = a if a NOT NULL, else b.
したがって、次のことがいえます。
FROM句内の左側の表から共通するすべての列値が戻されます。
FROM句内の右側の表から共通するすべての列値が戻されます。
USING column句で指定できません。
NATURAL外部結合を使用してこの句を指定することはできません。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条件を指定すると、選択する行を1つ以上の条件を満たす行のみに制限できます。conditionには、有効なSQL条件を指定します。
この句を省略した場合、FROM句に指定されている表、ビューまたはマテリアライズド・ビューのすべての行が戻されます。
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句を使用します。
階層問合せのルートとして使用される行を識別する場合の条件を指定します。Oracle Databaseでは、この条件を満たすすべての行がルートとして使用されます。この句を省略した場合、表内のすべての行がルート行として使用されます。
階層の親/子の行の関連を識別する条件を指定します。connect_by_conditionには、第7章「条件」のどの条件でも含めることができます。ただし、親である行を参照するためのPRIOR演算子を使用する必要があります。
GROUP BY句を指定すると、選択した行を各行のexprの値に基づいてグループ化し、各グループのサマリー情報を1行戻すことができます。この句にCUBEまたはROLLUP拡張要素を指定した場合、標準グループ化の他に超集合グループ化が生成されます。
GROUP BY句の式には、SELECT構文のリストに指定されている列であるかどうかにかかわらず、FROM句の表、ビューおよびマテリアライズド・ビューの列を指定できます。
GROUP BY句は行をグループ化しますが、結果セットの順序は保証しません。グループ化の順序付けを行うには、ORDER BY句を使用します。
|
参照
|
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式の値でグループ化した行を標準行、その他を超集合行といいます。
simple_grouping_clauseのCUBE操作を使用すると、選択した行を、指定した式のあらゆる組合せの値に基づいてグループ化できます。また、各グループのサマリー情報を1行戻すことができます。CUBE操作を使用すると、クロス集計値を出力できます。
たとえば、simple_grouping_clauseのCUBE句に式を3つ指定した場合（n=3）、操作の結果は2n = 23 = 8グループになります。n式の値でグループ化した行を標準行、その他を超集合行といいます。
GROUPING SETSは、データを複数にグループ化するGROUP BY句をさらに拡張したものです。これによって、不要な集計が排除され、効率的に集計できるようになります。必要なグループを指定すると、データベースがCUBEまたはROLLUPによって生成された集計のすべてを実行する必要がなくなります。GROUPING SETS句で指定したすべてのグループ化が計算され、UNION ALL操作で個々のグループ化の結果が組み合されます。UNION ALLは、結果セットが重複行を含むことを許可します。
GROUP BY句では、様々な方法で式を組み合せることができます。
ROLLUP操作またはCUBE操作の計算でこれらを1つの単位として処理します。
ROLLUP操作およびCUBE操作を区切ります。データベースは、これらを1つのGROUP BY句に結合します。結果は、各グルーピング・セットからのグループ化のクロス積です。HAVING句を使用すると、戻す行のグループを、指定した条件がTRUEである行のグループのみに制限できます。この句を省略した場合、すべてのグループのサマリー行が戻されます。
where_clauseおよびhierarchical_query_clauseの後に、GROUP BYおよびHAVINGを指定します。GROUP BYおよびHAVING句を両方指定する場合は、どちらを先に指定してもかまいません。
この句には、次の制限事項があります。
exprの一部として指定できません。
group_by_clauseがオブジェクト型列を参照する場合、問合せはパラレル化されません。
model_clauseでは、選択した行を多次元配列として表示し、その配列内のセルにランダムにアクセスできます。model_clauseを使用すると、一連のセルの割当てを指定できます。この割当てはルールと呼ばれ、個々のセルおよびセルの範囲に対する計算を実行します。これらのルールは問合せの結果を操作しますが、データベース表は更新しません。
問合せでmodel_clauseを使用する場合、SELECT句およびORDER BY句は、model_column_clausesで定義された列のみを参照する必要があります。
|
参照:
|
main_model句を使用すると、選択した行を多次元配列内で表示する方法および配列内の各セルに適用するルールを定義できます。
model_column_clausesを使用すると、問合せの列を、パーティション列、ディメンション列およびメジャー列の3つのグループに定義して分類できます。この3つの副次句内のexprがモデル列の場合、列の別名（c_alias）の指定は任意です。exprがモデル列でない場合、列の別名は必須です。
PARTITION BY句を使用すると、選択した行を列の値に基づいてパーティションに分割するために使用する列を指定できます。
DIMENSION BY句を使用すると、パーティション内で行を識別する列を指定できます。ディメンション列およびパーティション列の値は、行のメジャー列に対する配列の索引として使用されます。
MEASURES句を使用すると、計算が実行可能な列を識別できます。個々の行のメジャー列は、パーティション列およびディメンション列の値を指定することによる参照および更新が可能なセルと同様に扱われます。
model_columnを使用すると、モデルの定義に使用する列を識別できます。exprが列名ではない場合、列の別名が必要です。モデル式の詳細は、「モデル式」を参照してください。
cell_reference_options句を使用すると、ルールでNULLまたは値なしを処理する方法および列の一意性を制約する方法を指定できます。
IGNORE NAVを指定すると、指定したデータ型のNULLまたは値なしに対して、次の値が戻されます。
KEEP NAVを指定すると、NULLまたは値なしのセル値に対してNULLが戻されます。KEEP NAVはデフォルトです。
UNIQUE SINGLE REFERENCEを指定すると、問合せの結果セット全体ではなく、ルールの右側の単一セルの参照のみが一意性をチェックされます。
UNIQUE DIMENSIONを指定すると、PARTITION BYおよびDIMENSION BYで指定した列が、問合せに対する一意キーであるかどうかが確認されます。UNIQUE DIMENSIONはデフォルトです。
model_rules_clauseを使用すると、更新するセルおよびこれらのセルを更新するルールを指定できます。オプションで、ルールを適用および処理する方法も指定できます。
各ルールは割当てを表し、左側と右側にわかれています。ルールの左側は、ルールの右側によって更新されるセルを識別します。ルールの右側は、ルールの左側で指定されたセルに割り当てられる値を評価します。
UPSERT ALLを使用すると、ルールの左側に位置参照と記号参照の両方があるルールに対してUPSERT動作が可能になります。UPSERT ALLルールが評価されると、次の手順が実行され、アップサートするセル参照のリストが作成されます。
UPSERT ALLのセマンティクスの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。
UPSERTを指定すると、ルールの左側で参照されるセルが多次元配列内に存在している場合、セルにルールが適用され、多次元配列内に存在しないセルに対しては新しい行が挿入されます。UPSERT動作は、ルールの左側で位置参照が使用され、単一セルが参照されている場合にのみ適用されます。UPSERTはデフォルトです。位置参照および単一セル参照の詳細は、「cell_assignment」を参照してください。
UPDATEおよびUPSERTは、個々のルールに同様に指定できます。特定のルールにUPDATEまたはUPSERTのいずれかを指定した場合、その指定はRULES句に指定したその他のオプションより優先されます。
UPDATEを指定するとルールの左側で参照されるセルが多次元配列内に存在している場合、そのセルにルールが適用されます。セルが存在しない場合、割当ては無視されます。
AUTOMATIC ORDERを指定すると、依存順序に基づいてルールが評価されます。この場合、セルには値が1回のみ割り当てられます。
SEQUENTIAL ORDERを指定すると、表示されている順序でルールが評価されます。この場合、セルには値が複数回割り当てられます。SEQUENTIAL ORDERはデフォルトです。
ITERATE...[UNTIL]を使用すると、ルールを繰り返す回数を指定できます。また、早期終了条件も指定できます。UNTIL条件を囲むカッコの使用は任意です。
ITERATE ... [UNTIL]を指定した場合、ルールは表示されている順序で評価されます。model_rules_clauseでAUTOMATIC ORDERおよびITERATE ... [UNTIL]の両方が指定されている場合、エラーが戻されます。
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句を使用すると、更新するセルの範囲を単一のディメンション列内で指定できます。
IN句を使用すると、ディメンション列の値を値のリストまたは副問合せとして指定できます。副問合せを使用する場合、次の制限事項があります。
FROM句を使用すると、ディメンション列の値の範囲を指定できます。範囲内の増分は不連続でもかまいません。FROM句は、加算および減算がサポートされているデータ型の列にのみ使用できます。INCREMENTおよびDECREMENTの値は、正の値である必要があります。
オプションで、FROM句内でLIKE句を指定することができます。LIKE句のpatternは、単一のパターン一致文字%を含む文字列です。この文字は、実行時にFROM句の現在の増分値または減分値で置き換えられます。
FORループで使用されるディメンション以外のすべてのディメンションが単一セル参照に関係する場合は、式で新しい行を挿入できます。FORループによって生成されたディメンション値の組合せの数は、MODEL句の行制限（10,000）の計算に含まれます。
multi_column_for_loop句を使用すると、更新するセルの範囲を複数のディメンション列にまたがって指定できます。IN句を使用すると、ディメンション列の値を複数の値のリストまたは副問合せとして指定できます。副問合せを使用する場合、次の制限事項があります。
FORループで使用されるディメンション以外のすべてのディメンションが単一セル参照に関係する場合は、式で新しい行を挿入できます。FORループによって生成されたディメンション値の組合せの数は、MODEL句の行制限（10,000）の計算に含まれます。
ORDER BY句を使用すると、ルールの左側のセルを評価する順序を指定できます。exprは、ディメンションまたはメジャー列に変換される必要があります。ORDER BY句を指定しない場合、DIMENSION BY句で指定した列の順序がデフォルトで使用されます。詳細は、「order_by_clause」を参照してください。
モデル・ルールでORDER BY句を使用する場合には、次の制限があります。
model_clause句のorder_by_clauseには、SIBLINGS、positionまたはc_aliasを指定できません。
FORループも指定することはできません。
ルールの右側に指定した単一または複数のセルの値を表す式を指定します。exprに副問合せを含めることはできません。モデル式の詳細は、「モデル式」を参照してください。
return_rows_clauseを使用すると、選択されたすべての行を戻すか、モデル・ルールによって更新された行のみを戻すかどうかを指定できます。ALLはデフォルトです。
reference_modelは、model_clause内から複数の配列にアクセスする必要がある場合に使用します。この句は、問合せの結果に基づいて、読取り専用の多次元配列を定義します。
reference_model句の副次句は、main_model句と同じセマンティクスを持ちます。詳細は、「cell_reference_options」および「model_column_clauses」を参照してください。
この句には、次の制限事項があります。
集合演算子は、2つのSELECT文によって戻された行を1つの結果に結合します。それぞれのコンポーネント問合せで選択される列の数とデータ型は同じである必要がありますが、列の長さは異なってもかまいません。結果セット内の列の名前は、集合演算子の前にあるSELECT構文のリスト内の式の名前です。
集合演算子で3つ以上の問合せを結合する場合、隣接する問合せが左から右へ評価されます。副問合せを囲むカッコは任意指定です。この評価順序を変更する場合、カッコを使用します。
これらの演算子の詳細および使用方法の制限事項については、「UNION [ALL]、INTERSECTおよびMINUS演算子」を参照してください。
ORDER BY句を使用すると、文によって戻された行を順序付けることができます。order_by_clauseを指定しない場合、同じ問合せで取り出される行の順序が異なることがあります。
SIBLINGSキーワードは、hierarchical_query_clause（CONNECT BY）を指定する場合のみに有効です。ORDER SIBLINGS BYは階層問合せ句で指定した任意の順序を保持し、兄弟関係にある階層にorder_by_clauseを適用します。
exprを使用すると、exprの値を基準にして行を順番付けることができます。式は、SELECT構文のリストの列、あるいはFROM句の表、ビューまたはマテリアライズド・ビューの列に基づきます。
positionを使用すると、SELECT構文のリストの指定した位置にある式の値に基づいて行を順序付けることができます。positionには整数を指定する必要があります。
order_by_clauseには複数の式を指定できます。この場合、まず、最初の式の値に基づいて行がソートされます。次に、最初の式と同じ値を持つ行が2番目の式の値に基づいてソートされる、というように処理が行われます。NULL値は昇順では最後に、降順では先頭にソートされます。問合せ結果の順位付けの詳細は、「問合せ結果のソート」を参照してください。
昇順か降順かを指定します。デフォルトはASCです。
NULL値を含む戻された行が順序の最初にくるか、最後にくるかを指定します。
NULLS LASTは昇順のデフォルトで、NULLS FIRSTは降順のデフォルトです。
ORDER BY句には次の制限事項があります。
DISTINCT演算子を指定した場合、SELECT構文のリストに指定された列でないかぎり、この句は列を参照することはできません。
order_by_clauseには最大255個の式を指定できます。
LONG列、LONG RAW列、ネストした表またはVARRAYを使用して順位付けすることはできません。
order_by_clauseは次の式に制限されます。
FOR UPDATE句を使用すると、トランザクションが終了する前に、選択した行が別のユーザーによってロックまたは更新されることがないように、選択した行をロックできます。最上位のSELECT文でのみ、この句を指定できます。副問合せでは指定できません。
親表の行がロックされても、ネストした表の行はロックされません。ネストした表の行をロックする場合、ネストした表を明示的にロックする必要があります。
この句には、次の制限事項があります。
DISTINCT演算子またはCURSOR式、集合演算子、group_by_clause、または集計ファンクションの構造体とともに指定することはできません。
LONG列および順序と同じデータベース内にある必要があります。OF ... column句を使用すると、結合内の特定の表またはビューで選択された行のみをロックできます。OF句の列は、どの表またはビューの行をロックするかを識別する場合にのみ使用します。指定する列は重要ではありません。ただし、列の別名ではなく、実際の列名を指定する必要があります。この句を省略した場合、問合せ内のすべての表の選択された行がロックされます。
NOWAITおよびWAIT句を使用すると、他のユーザーによってロックされている行をSELECT文がロックしようとする場合に処理する方法をOracleに指示できます。
WAITおよびNOWAITのどちらも指定しない場合、行が使用可能になるまで待機した後でSELECT文の結果が戻されます。
SKIP LOCKEDは、競合するトランザクションを処理するもう1つの方法で、対象のいくつかの行をロックします。SKIP LOCKEDを指定すると、WHERE句で指定された行をロックし、ロック済であることが検出された行をスキップするようにデータベースに指示することができます。この機能は、問合せの目的が、行の実際の内容ではなく、その数を取得することである場合に有用です。
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.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('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
次の文は、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;
部門および職種のすべての組合せについて、従業員数と平均年収を戻すには、サンプル表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
次の例は、指定した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 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
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の値より大きい必要があります。
次の文は、従業員の最低給与が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;
次の文は、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;
ここで作成されたビューは、サンプル・スキーマ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から多次元配列を作成します。また、次の処理も実行されます。
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動作が使用されます。行が存在しないため、新しい行が追加され、関連するメジャー列が更新されます。行が存在する場合、メジャー列は更新されません。
次の例は、同じ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.
次の文は、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;
次の文は、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
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句の結合構文を使用することをお薦めします。
左側外部結合では、従業員のいない部門を含むすべての部門が戻されます。右側外側結合が指定された同一文では、どの部門にも割り当てられていない従業員を含むすべての従業員が戻されます。
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_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 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.
次の例では、特定の部門の集合に所属していない従業員のリストを選択します。
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;
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;
次の文は、すべての従業員を階層順序で戻します。職種が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表の各行について次の手順を実行します。
副問合せは、employees表の各行につき1回ずつ評価されます。
次の文は、現在の日付を戻します。
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 CONSTRAINTS文を使用すると、遅延可能な制約の検証を、各DML文の実行後に行うか（IMMEDIATE）、トランザクションのコミット時に行うか（DEFERRED）をトランザクションごとに指定できます。この文を使用して、制約名のリストまたはALL制約のモードを設定できます。
SET CONSTRAINTSモードは、トランザクションの存続期間中、または別のSET CONSTRAINTS文によってモードがリセットされるまで継続します。
トリガー定義の内部でこの文を指定することはできません。
SET CONSTRAINTSは、分散型の文にすることができます。処理中のトランザクションを持つ既存のデータベース・リンクにはSET CONSTRAINTS ALL文の発行時にその発行が通知され、新しいリンクにはトランザクションの開始直後にその発行が通知されます。
遅延可能な制約を検証する時期を指定する場合は、表が自分のスキーマ内にある必要があります。自分のスキーマ内にない場合は、制約が適用される表に対するSELECT権限が必要です。
1つ以上の整合性制約の名前を指定します。
ALLを指定すると、このトランザクションに対するすべての遅延可能な制約を設定できます。
IMMEDIATEを指定すると、指定された制約が、各制約DML文の実行時に即時にチェックされます。Oracle Databaseでは、チェック済のすべての制約に一貫性があり、他のSET CONSTRAINTS文が発行されていない場合、トランザクションで以前に遅延された制約が最初にチェックされ、その後すぐにそのトランザクションの他の文の制約チェックが継続してチェックされます。制約のチェックに失敗した場合は、エラーが通知されます。その時点で、COMMIT文を実行すると、トランザクション全体が元に戻されます。
COMMITを正常に実行できるかどうかをチェックする方法としてトランザクションの終了直後に制約を行います。制約をトランザクション内の最後の文としてIMMEDIATEに設定することで、予期しないロールバックを回避できます。いずれかの制約のチェックに失敗した場合は、トランザクションをコミットする前にエラーを解決できます。
DEFERREDを指定すると、遅延可能な制約によって指定された条件が、トランザクションのコミット時に検証されます。
次の文は、このトランザクション内のすべての遅延可能な制約が、各DML文の直後に検証されるように設定します。
SET CONSTRAINTS ALL IMMEDIATE;
次の文は、トランザクションのコミット時に3つの遅延制約を検証します。この例では、制約をNOT DEFERRABLEに設定すると失敗します。
SET CONSTRAINTS emp_job_nn, emp_salary_min , hr.jhist_dept_fk@remote DEFERRED;
データベースでは、ユーザー・ログイン時に、ユーザーに明示的に付与されたすべての権限およびユーザーのすべてのデフォルトのロールが使用可能になります。セッション中、ユーザーまたはアプリケーションは、SET ROLE文を使用して、そのセッションに対してロールを何度でも使用可能または使用禁止にできます。
148を超えるユーザー定義のロールを一度に使用可能にできません。
|
注意:
ほとんどのロールは、直接的に付与されているか、または他のロールを介して付与されていないかぎり、使用可能または使用禁止にできません。ただし、保護アプリケーション・ロールは、関連付けられているPL/SQLパッケージによって付与して使用可能にすることができます。保護アプリケーション・ロールについては、「USING package」の |
SESSION_ROLESデータ・ディクショナリ・ビューを問い合せると、現在使用可能なロールを参照できます。
|
参照:
|
SET ROLE 文に指定するロールが付与されている必要があります。
現行のセッションで使用可能にするロールを指定します。リストされないロールおよび使用可能でないロールは、現行のセッションで使用禁止になります。
IDENTIFIED BY password句では、ロールに対するパスワードを指定します。ロールにパスワードが設定されている場合は、指定する必要があります。
グローバルに識別されるロールは指定できません。グローバル・ロールは、ログイン時にデフォルトで使用可能になり、後で再度使用可能にすることはできません。
ALLを指定すると、現行のセッションに対して付与されているすべてのロールを使用可能にできます。ただし、EXCEPT句に任意に指定されているロールは除きます。
EXCEPT句に指定するロールは、ユーザーに直接付与されている必要があります。他のロールによってユーザーに付与されたものは無効です。
直接付与されているロール、および他のロールを介してユーザーに付与されているロールをEXCEPT句に指定した場合、そのロールの付与先のロールにより、そのロールは使用可能のままになります。
このオプションを使用して、ユーザーに直接付与されているパスワード付きのロールを使用可能にすることはできません。
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文を使用すると、現行のトランザクションを読取り専用または読み書き両用として設定したり、分離レベルを設定したり、指定したロールバック・セグメントにトランザクションを割り当てることができます。
TXロックを取得する操作によって、トランザクションが暗黙的に開始されます。
SELECT ... FOR UPDATE文が発行されたとき
SET TRANSACTION文またはDBMS_TRANSACTIONパッケージによってトランザクションが明示的に開始されたとき
COMMIT文またはROLLBACK文を発行すると、現行のトランザクションが明示的に終了されます。
SET TRANSACTION文によって実行される処理は、現行のトランザクションのみに影響します。他のユーザーまたは他のトランザクションには影響しません。COMMIT文またはROLLBACK文を発行すると、トランザクションは常に終了されます。現行のトランザクションはデータ定義言語（DDL）文の実行の前後でOracle Databaseによって暗黙的にコミットされます。
SET TRANSACTION文を使用する場合、トランザクションの先頭に記述する必要があります。ただし、SET TRANSACTION文が必要ないトランザクションもあります。
READ ONLY句を使用すると、現行のトランザクションを読取り専用に設定できます。この句では、トランザクション・レベルの読取り一貫性を設定します。
そのトランザクションの後続のすべての問合せでは、トランザクションの開始前にコミットされた変更のみが参照されます。読取り専用トランザクションは、他のユーザーが更新中の1つ以上の表に対して、複数の問合せを実行するレポートに役立ちます。
ユーザーSYSは、この句を使用できません。SYSによる問合せでは、SYSがトランザクションをREAD ONLYに設定した場合でも、トランザクション中の変更が戻されます。
読取り専用トランザクションは、次の文のみを使用できます。
READ WRITEを指定すると、現行のトランザクションを読み書き両用に設定できます。この句では、文レベルの読取り一貫性を設定します。これはデフォルトです。
同一トランザクション内では、読取り一貫性のレベル（トランザクション・レベルおよび文レベル）を切り替えることができません。
ISOLATION LEVEL句を使用すると、データベースを変更するトランザクションがどのように処理されるかを指定できます。
SERIALIZABLEを指定します。シリアライズ可能トランザクションにデータ操作言語（DML）が含まれている場合、そのDMLがシリアライズ可能トランザクションの開始時にコミットされなかったトランザクション内の更新済のリソースを更新しようとすると、そのDML文は正常に実行されません。
READ COMMITTEDが設定されています。別のトランザクションで行ロックを保持しておく必要があるDMLがトランザクションに指定されていると、DML文は行ロックが解除されるまで待ち状態になります。
USE ROLLBACK SEGMENTを指定すると、現行のトランザクションを、指定したロールバック・セグメントに割り当てることができます。この句によって、現行のトランザクションは暗黙的に読み書き両用トランザクションに設定されます。
パラレルDMLでは、複数のロールバック・セグメントが必要です。したがって、トランザクションにパラレルDML操作が含まれている場合、この句は無視されます。
NAME句を使用すると、現行のトランザクションに名前を割り当てることができます。この句は、分散データベース環境でインダウト・トランザクションを識別および変換する場合に便利です。string値の最大長は255バイトです。
分散トランザクションに対して名前を指定する場合、トランザクションのコミット時に、名前はコミットのコメントとなり、COMMIT文で明示的に指定した任意のコメントを上書きします。
次の文は、サンプルの注文入力スキーマ（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文を使用すると、クラスタからすべての行を削除できます。デフォルトでは、次の処理も実行されます。
MINEXTENTS記憶域パラメータで指定された領域は除く）の割当てが解除されます。
NEXT記憶域パラメータが、切捨て処理によってセグメントから最後に削除されたエクステントのサイズに設定されます。
クラスタを削除して再作成するより、TRUNCATE文で行を削除する方が効果的です。クラスタを削除して再作成した場合、そのクラスタに依存するオブジェクトが無効になり、クラスタに対するオブジェクト権限を再度付与する必要があります。また、表の索引とクラスタを再作成し、その記憶域パラメータを再指定する必要があります。切捨ての場合、このような影響はありません。
TRUNCATE CLUSTER文を使用すると、DELETE文を使用してすべての行を削除するよりも迅速に削除できます。特に、クラスタに索引およびその他の依存オブジェクトが多数ある場合に有効です。
|
参照:
|
クラスタを切り捨てるには、自分のスキーマ内にそのクラスタがあるか、またはDROP ANY TABLEシステム権限が必要です。
切り捨てるクラスタが設定されているスキーマと、そのクラスタの名前を指定します。なお、索引クラスタは切り捨てられますが、ハッシュ・クラスタは切り捨てられません。schemaを指定しない場合、クラスタは自分のスキーマ内にあるとみなされます。
クラスタを切り捨てた場合、そのクラスタにある表のすべての索引データも自動的に削除されます。
STORAGE句を使用すると、行の切捨てによって解放された領域をどのようにするかを指定できます。DROP STORAGE句およびREUSE STORAGE句は、対応する索引から削除されたデータの空き領域にも適用されます。
DROP STORAGEを指定すると、クラスタのMINEXTENTSパラメータで割り当てられた領域を除き、クラスタから削除された行からすべての領域の割当てを解除できます。この領域は、後で表領域内の他のオブジェクトで使用できます。また、NEXT記憶域パラメータが、切捨て処理によってセグメントから最後に削除されたエクステントのサイズに設定されます。これはデフォルトです。
REUSE STORAGEを指定すると、クラスタに割り当てられた削除行の領域を確保できます。STORAGEの値は、表またはクラスタを作成したときの値にリセットされません。この領域は、挿入操作または更新操作によってそのクラスタ内に作成される新規データによってのみ使用されます。記憶域パラメータは現行の設定のまま残ります。
切り捨てるオブジェクトに対して複数の空きリストを指定している場合は、REUSE STORAGE句によって、インスタンスへの空きリストのマッピングも削除され、最高水位標は第1エクステントの先頭までリセットされます。
次の文は、personnelクラスタ内の表のすべての行を削除しますが、空き領域は表に割り当てられたままにしておきます。
TRUNCATE CLUSTER personnel REUSE STORAGE;
この文では、personnelクラスタにある表のすべての索引データも削除されます。
TRUNCATE TABLEを使用すると、表からすべての行を削除できます。デフォルトでは、次の処理も実行されます。
MINEXTENTS記憶域パラメータで指定された領域は除く）の割当てが解除されます。
NEXT記憶域パラメータが、切捨て処理によってセグメントから最後に削除されたエクステントのサイズに設定されます。
表を削除して再作成するより、TRUNCATE TABLE文で行を削除する方が効果的です。表を削除して再作成した場合、その表に依存するオブジェクトが無効になり、表に対するオブジェクト権限を再度付与する必要があります。また、表の索引、整合性制約およびトリガーを再作成し、その記憶域パラメータを再指定する必要があります。切捨ての場合、このような影響はありません。
TRUNCATE TABLE文を使用すると、DELETE文を使用してすべての行を削除するよりも迅速に削除できます。特に、表にトリガー、索引およびその他の依存オブジェクトが多数ある場合に有効です。
|
参照:
|
表を切り捨てるには、自分のスキーマ内にその表があるか、またはDROP ANY TABLEシステム権限が必要です。
切り捨てる表が設定されているスキーマおよびその表の名前を指定します。クラスタを構成する表は、切り捨てることができません。schemaを指定しない場合、表が自分のクラスタに定義されているとみなされます。
tableの記憶域パラメータNEXTが、切捨て処理中にセグメントから最後に削除されたエクステントのサイズに変更されます。
tableに対する索引（ローカル索引のレンジ・パーティションとハッシュ・パーティション、およびローカル索引のサブパーティション）のUNUSABLEのインジケータも、自動的に切捨ておよびリセットされます。
tableが空でない場合は、表中の非パーティション索引およびグローバル・パーティション索引のすべてのパーティションにUNUSABLEのマークが付けられます。ただし、表が切り捨てられると索引も切り捨てられ、索引セグメントに対して新しい最高水位標が計算されます。この操作は、索引に対して新しいセグメントを作成することと同じです。このため、切捨て操作の最後に、索引が再度USABLEになります。
tableがパーティション化されている場合、各パーティションまたはサブパーティションのLOBデータ・セグメントおよびLOB索引セグメントと同様に、パーティションおよびサブパーティションも切り捨てられます。この文には、次の制限事項があります。
tableでドメイン索引が定義されている場合、索引および索引パーティションにIN_PROGRESSのマークを付けることはできません。
MATERIALIZED VIEW LOG句を使用すると、表が切り捨てられた場合に、この表に定義されているマテリアライズド・ビュー・ログを保存するか、または削除するかを指定できます。この句を使用した場合、マテリアライズド・ビューのマスター表を、エクスポートまたはインポートによって再編成できます。この場合、マスター表で定義された主キー・マテリアライズド・ビューを高速リフレッシュする機能は影響を受けません。主キー・マテリアライズド・ビューの連続高速リフレッシュをサポートする場合、マテリアライズド・ビュー・ログに主キー情報を記録する必要があります。
PRESERVEを指定すると、マスター表を切り捨てたときにマテリアライズド・ビュー・ログを保存できます。これはデフォルトです。
PURGEを指定すると、マスター表を切り捨てたときにマテリアライズド・ビュー・ログを削除できます。
STORAGE句を使用すると、行の切捨てによって解放された領域をどのようにするかを指定できます。DROP STORAGE句およびREUSE STORAGE句は、対応する索引から削除されたデータの空き領域にも適用されます。
DROP STORAGEを指定すると、表またはクラスタのMINEXTENTSパラメータで割り当てられた領域を除き、表から削除された行からすべての領域の割当てを解除できます。この領域は、後で表領域内の他のオブジェクトで使用できます。また、NEXT記憶域パラメータが、切捨て処理によってセグメントから最後に削除されたエクステントのサイズに設定されます。これはデフォルトです。
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 ANY TABLEシステム権限を持っている場合は、任意の表またはビューの実表の値を更新できます。
次の場合は、更新するオブジェクトに対するSELECTオブジェクト権限も必要です。
（DML_table_expression_clause::=、update_set_clause::=、where_clause::=、returning_clause::=、error_logging_clause::=を参照）
（partition_extension_clause::=、「SELECT」構文の項にあるsubquery::=、subquery_restriction_clause::=、table_collection_expression::=を参照）
文の実行計画を選択する場合に、オプティマイザに指示を与えるためのコメントを指定します。
UPDATEキーワードの直後にパラレル・ヒントを指定した場合、基礎となるスキャンおよびUPDATE操作の両方をパラレル化できます。
ONLY句は、ビューのみに適用されます。UPDATE句のビューが階層に属し、そのどのサブビューの行も変更しない場合は、ONLY構文を指定します。
更新するオブジェクトが含まれているスキーマを指定します。schemaを指定しない場合、オブジェクトは自分のスキーマ内にあるとみなされます。
更新する対象となる、表、ビュー、マテリアライズド・ビュー、または副問合せから戻された列の名前を指定します。表に対してUPDATE文を実行した場合、その表に対応付けられたUPDATEトリガーが起動します。
viewを指定した場合、ビューの実表が更新されます。ビューを定義する問合せに次のいずれかの要素が含まれる場合は、INSTEAD OFトリガーを除き、そのビューを更新することはできません。
DISTINCT演算子
GROUP BY、ORDER BY、MODEL、CONNECT BYまたはSTART WITH句
SELECT構文のリストにあるコレクション式
SELECT構文のリストにある副問合せ
WITH READ ONLYが指定された副問合せ
WITH CHECK OPTIONを指定してビューを作成した場合、実行結果がビューを定義する問合せの条件を満たす場合にのみ、ビューを更新できます。
tableまたはviewの実表に、1つ以上のドメイン索引列がある場合は、この文によって適切な索引タイプの更新ルーチンが実行されます。
参照:
更新対象のtable内にあるパーティションまたはサブパーティションの名前またはパーティション・キー値を指定します。パーティション表内の値を更新する場合は、パーティション名を指定する必要はありません。ただし、パーティション名を指定した方が、複雑なwhere_clauseを使用するよりも効果的な場合もあります。
オブジェクトが格納されているリモート・データベースへのデータベース・リンクの完全名または部分名を指定します。Oracle Databaseの分散機能を使用している場合にかぎり、データベース・リンクを使用してリモート・オブジェクトを更新できます。
dblinkを省略した場合、オブジェクトがローカル・データベース上にあるとみなされます。
subquery_restriction_clauseを使用すると、次のいずれかの方法で副問合せを制限できます。
WITH READ ONLYを指定すると、表またはビューを更新禁止にできます。
WITH CHECK OPTIONを指定すると、副問合せに含まれない行を生成する表またはビューの変更を禁止できます。この句をDML文の副問合せ内で使用する場合、FROM句内の副問合せには指定できますが、WHERE句内の副問合せには指定できません。
CHECK OPTION制約の名前を指定します。この識別子を省略した場合、その制約にSYS_Cnという形式の名前が自動的に割り当てられます。この場合のnは、その制約名をデータベース内で一意の名前にする整数です。
table_collection_expressionを使用すると、問合せおよびDML操作で、collection_expression値を表として扱うことができます。collection_expressionには、副問合せ、列、ファンクションまたはコレクション・コンストラクタのいずれかを指定できます。その形式にかかわらず、集合値（ネストした表型またはVARRAY型の値）を戻す必要があります。このようなコレクションの要素抽出プロセスをコレクション・ネスト解除といいます。
TABLE式を親表と結合する場合は、オプションのプラス（+）には大きな意味があります。+を指定すると、その2つの外部結合が作成され、コレクション式がNULLの場合でも、外部表の行が問合せで戻されるようになります。
table_collection_expressionを使用して、ある表の行を別の表の行を基にして更新できます。たとえば、四半期ごとの売上表を、年度ごとの売上表にまとめることができます。
文中で参照する表、ビューまたは副問合せの相関名（別名）を指定します。DML_table_expression_clauseがいずれかのオブジェクト型属性またはオブジェクト型メソッドを参照する場合、この別名が必要です。
この句には、次の制限事項があります。
tableまたはviewの実表に、IN_PROGRESSまたはFAILEDとマークされたドメイン索引がある場合は、この文は実行できません。
UNUSABLEとマークされている場合は、パーティションに挿入できません。
DML_table_expression_clauseの副問合せにはorder_by_clauseを指定できません。
UNUSABLEのマークが付いている索引、索引パーティションまたは索引サブパーティションを指定する場合、SKIP_UNUSABLE_INDEXESセッション・パラメータがTRUEに設定されていないかぎり、UPDATE文は正常に実行されません。update_set_clauseを使用すると、列の値を設定できます。
更新するオブジェクトの列の名前を指定します。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は正常に実行されません。
更新される行ごとに1行ずつ戻す副問合せを指定します。
update_set_clauseで1列のみを指定した場合、副問合せは1つの値のみを戻します。
update_set_clauseで複数の列を指定した場合、副問合せは指定した列の数の値を戻します。
subqueryがリモート・オブジェクトを参照する場合、参照がローカル・データベースのオブジェクトにループバックしないかぎり、UPDATEはパラレルで実行されます。ただし、DML_table_expression_clauseのsubqueryがリモート・オブジェクトを参照する場合は、UPDATEはシリアルで実行されます。
副問合せ内でflashback_query_clauseを使用すると、過去のデータでtableを更新できます。 この句の詳細は、「SELECT」の「flashback_query_clause」を参照してください。
対応する列に割り当てられた新しい値に変換する式を指定します。
DEFAULTを指定すると、以前に列のデフォルト値として指定した値を列に設定できます。対応する列に対してデフォルト値を指定していない場合、列にNULLが設定されます。
ビューを更新する場合は、DEFAULTを指定できません。
VALUE句を使用すると、オブジェクト表の行全体を指定できます。
この句は、オブジェクト表に対してのみ指定できます。
where_clauseを使用すると、指定した条件が真の行のみが更新されるように制限できます。この句を指定しない場合、表またはビューのすべての行が更新されます。conditionの構文は、第7章「条件」を参照してください。
where_clauseは、値を更新する行を決定します。where_clauseを指定しない場合、すべての行が更新されます。where_clauseの条件を満たす行ごとに、update_set_clauseの等号演算子（=）の左側にある列に、演算子の対応する右側の式の値が設定されます。式は行が更新される場合に評価されます。
この句を使用すると、DML文に影響される行を取り出すことができます。この句は、表、マテリアライズド・ビュー、および単一の実表を持つビューに指定できます。
returning_clauseを指定したDML文を単一行に実行すると、影響された行、ROWID、および処理された行へのREFを使用している列式が取り出され、ホスト変数またはPL/SQL変数に格納されます。
returning_clauseを指定したDML文を複数行に実行すると、式の値、ROWIDおよび処理された行に関連するREFがバインド配列に格納されます。
exprリストの各項目は、適切な構文で表す必要があります。
INTO句を指定すると、変更された行の値を、data_itemリストに指定する変数に格納できます。
取り出されたexpr値を格納するホスト変数またはPL/SQL変数を指定します。
RETURNINGリストの各式については、INTOリストに、対応する型に互換性があるPL/SQL変数またはホスト変数を指定する必要があります。
RETURNING句には、次の制限事項があります。
exprに次の制限事項があります。
exprリストに主キー列またはその他のNOT NULL列が含まれている場合、表にBEFORE UPDATEトリガーが定義されていると、UPDATE文は正常に実行されません。
returning_clauseを指定できません。
LONG型を取り出すことはできません。
INSTEAD OFトリガーが定義されたビューに対して指定することはできません。error_logging_clauseのUPDATE文での動作は、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_set_clauseの2つの形式
where_clause
UPDATE employees a SET department_id = (SELECT department_id FROM departments WHERE location_id = '2100'), (salary, commission_pct) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b WHERE a.department_id = b.department_id) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700);
このUPDATE文によって、次の処理が実行されます。
department_idをボンベイ（location_idは2100）の対応するdepartment_idに設定します。
次の文は、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オブジェクト参照ファンクションを使用します。
相関副問合せを使用してネストした表の行を更新する例は、「表のコレクション例:」を参照してください。
次の文は、更新された行の値を戻し、PL/SQL変数bnd1、bnd2、bnd3に結果を格納します。
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;
|
 Copyright © 1996, 2008, Oracle Corporation. All Rights Reserved. |
|