| Oracle® Database SQL言語リファレンス 11gリリース2 (11.2) B56299-08 |
|
 前 |
 次 |
INSERT文を使用すると、表、ビューの実表、パーティション表のパーティション、コンポジット・パーティション表のサブパーティション、オブジェクト表またはオブジェクト・ビューの実表に、行を追加できます。
前提条件
表に行を挿入する場合は、その表が自分のスキーマ内にある必要があります。自分のスキーマ内にない場合は、その表に対するINSERTオブジェクト権限が必要です。
ビューの実表に行を挿入する場合、ビューが定義されているスキーマの所有者には、その実表に対するINSERTオブジェクト権限が必要です。また、他のユーザーのスキーマ内のビューに行を挿入する場合は、そのビューに対するINSERTオブジェクト権限が必要です。
INSERT ANY TABLEシステム権限があれば、任意の表または任意のビューの実表に行を挿入できます。
表がリモート・データベースにある場合は、行を挿入する表に対するSELECTオブジェクト権限も必要です。
表、パーティションまたはビューにデータを挿入するために使用するINSERT文には、従来型INSERTおよびダイレクト・パスINSERTの2種類があります。従来型INSERT文を発行すると、表の空き領域を再利用して挿入され、参照整合性制約が維持されます。ダイレクト・パスINSERTの場合、表の既存データの後に、挿入したデータが追加されます。データは、バッファ・キャッシュを回避してデータファイルに直接書き込まれます。既存データの空き領域は再利用されません。この代替手法を使用すると、挿入操作中のパフォーマンスが向上します。また、これは、Oracleのダイレクト・パス・ローダー・ユーティリティであるSQL*Loaderの機能に似ています。パラレル・モードで作成された表に挿入する場合は、ダイレクト・パスINSERTがデフォルトです。
データベースでのREDOデータおよびUNDOデータの生成方法は、従来型とダイレクト・パスINSERTのいずれを使用しているかに一部関係しています。
従来型INSERTでは、表とアーカイブ・ログのロギング設定に関係なく、データとメタデータの両方に対する変更に対して常に最大のREDOおよびUNDOが生成され、データベースのロギング設定が強制的に使用されます。
ダイレクト・パスINSERTでは、メタデータの変更に対してREDOとUNDOの両方が生成されます。これらは操作のリカバリに必要であるためです。データの変更に大してはUNDOとREDOが次のように生成されます。
ダイレクト・パスINSERTでは、データの変更に対するUNDOの生成が常に回避されます。
データベースがARCHIVELOGまたはFORCE LOGGINGモードでない場合は、表のロギング設定に関係なく、データの変更に対するREDOは生成されません。
データベースがARCHIVELOGモード(ただしFORCE LOGGINGモードではない)の場合、ダイレクト・パスINSERTでは、LOGGING表のデータのREDOは生成されますが、NOLOGGING表のデータのREDOは生成されません。
データベースがARCHIVELOGモードかつFORCE LOGGINGモードの場合、ダイレクト・パスSQLでは、LOGGING表とNOLOGGING表の両方のデータのREDOが生成されます。
ダイレクト・パスINSERTには、次の制限があります。これらの制限に違反する場合、他にエラーがないかぎりメッセージが戻されず、従来型INSERTがシリアルで実行されます。
DML文の有無にかかわらず、1つのトランザクションで複数のダイレクト・パスINSERT文を使用できます。ただし、あるDML文が特定の表、パーティションまたは索引を変更した後は、トランザクションの他のDML文は表、パーティションまたは索引にアクセスできません。
同じ表、パーティションまたは索引にアクセスする問合せは、ダイレクト・パスINSERT文の前であれば許可されますが、後は許可されません。
シリアルまたはパラレル文が、同じトランザクションからダイレクト・パスINSERTによって変更された表にアクセスしようとする場合、エラーが戻され、文が拒否されます。
対象となる表は、クラスタにはできません。
対象となる表は、オブジェクト型列を含むことはできません。
索引構成表(IOT)がパーティション化されていない場合、マッピング表を含んでいる場合、またはマテリアライズド・ビューによって参照されている場合、その索引構成表に対してダイレクト・パス・インサートを使用することはできません。
索引構成表(IOT)の1つのパーティションまたは1つのパーティションのみを含むパーティション化されたIOTへのダイレクト・パスINSERTは、IOTがパラレル・モードで作成されている場合またはAPPENDヒントやAPPEND_VALUESヒントを指定した場合でも、シリアルで実行されます。ただし、パーティション化されたIOTへのダイレクト・パスINSERT操作は、拡張パーティション名が使用されず、IOTに複数のパーティションが含まれているかぎり、パラレル・モードで実行されます。
対象となる表には、トリガーまたは参照整合性制約を定義できません。
対象となる表は、レプリケートできません。
ダイレクト・パスINSERT文を含むトランザクションは、分散できません。
ダイレクト・パス・インサートによって挿入したデータを、挿入した直後に問合せまたは変更することはできません。問合せまたは変更しようとすると、ORA-12838エラーが発生します。新しく挿入したデータの読取りまたは変更を行う前に、COMMIT文を発行しておく必要があります。
|
関連項目:
|
構文
insert::=
(single_table_insert ::=、multi_table_insert ::=を参照)
(insert_into_clause ::=、values_clause ::=、returning_clause::=、subquery::=、error_logging_clause ::=を参照)
(DML_table_expression_clause::=を参照)
(insert_into_clause ::=、values_clause ::=、conditional_insert_clause ::=、subquery::=、error_logging_clause ::=を参照)
(insert_into_clause ::=、values_clause ::=を参照)
DML_table_expression_clause::=
(partition_extension_clause::=、subquery::=(「SELECT」の項)、subquery_restriction_clause::=、table_collection_expression ::=を参照)
partition_extension_clause::=
subquery_restriction_clause::=
table_collection_expression ::=
セマンティクス
hint
文の実行計画を選択する場合に、オプティマイザに指示を与えるためのコメントを指定します。
マルチテーブル・インサートの場合、対象となる表に対してPARALLELヒントを指定すると、表がPARALLELの指定付きで作成または変更されていなくても、マルチテーブル・インサート文全体がパラレル化されます。PARALLELヒントを指定しない場合、対象となるすべての表がPARALLELの指定付きで作成または変更されていないかぎり、挿入操作はパラレル化されません。
シングルテーブル・インサートの場合、明示的に値を指定する、または副問合せで値を検索することによって、表、ビューまたはマテリアライズド・ビューの1行に値を挿入します。
副問合せでflashback_query_clauseを使用すると、過去のデータをtableに挿入できます。この句の詳細は、「SELECT」の「flashback_query_clause」を参照してください。
シングルテーブル・インサートの制限事項: 副問合せで値を検索する場合、副問合せのSELECT構文のリストにはINSERT文の列リストと同じ数の列が含まれている必要があります。列リストを指定しない場合は、副問合せで表の各列の値を指定する必要があります。
INSERT INTO句を使用すると、データを挿入する対象となる表またはオブジェクトを指定できます。
DML_table_expression_clause
INTO DML_table_expression_clauseを使用すると、データを挿入するオブジェクトを指定できます。
schema 表、ビューまたはマテリアライズド・ビューが含まれているスキーマを指定します。schemaを指定しない場合、オブジェクトは自分のスキーマ内にあるとみなされます。
table | view | materialized_view | subquery 行を挿入する表またはオブジェクト表の名前、ビューまたはオブジェクト・ビューの名前、マテリアライズド・ビューの名前、あるいは副問合せから戻された列の名前を指定します。ビューまたはオブジェクト・ビューを指定した場合、そのビューの実表に行が挿入されます。
読取り専用マテリアライズド・ビューには行を挿入できません。書込み可能なマテリアライズド・ビューに行を挿入した場合、基礎となるコンテナ表にその行が挿入されます。ただし、その挿入操作は次のリフレッシュ操作によって上書きされます。マテリアライズド・ビュー・グループ内の更新可能なマテリアライズド・ビューに行を挿入した場合、対応する行もマスター表に挿入されます。
挿入される値がオブジェクト表に対するREFの場合、およびそのオブジェクト表に主キー・オブジェクト識別子がある場合、REFを挿入する列は、オブジェクト表に対する参照整合性制約またはSCOPE制約を持つREF列である必要があります。
tableまたはviewの実表に、1列以上のドメイン索引がある場合は、この文が適切な索引タイプの挿入ルーチンを実行します。
表に対してINSERT文を発行した場合、その表に対して定義されているINSERTトリガーが起動します。
|
関連項目: これらのルーチンの詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。 |
DML_table_expression_clauseの制限事項: この句には、次の制限事項があります。
tableまたはviewの実表に、IN_PROGRESSまたはFAILEDとマークされたドメイン索引がある場合は、この文は実行できません。
関係する索引パーティションがUNUSABLEとマークされている場合は、パーティションに挿入できません。
DML_table_expression_clauseのsubqueryのORDER BY句に関して、順序付けは、挿入された行または表の各エクステント内のみに保証されています。既存の行に関連する新しい行の順序付けは保証されていません。
WITH CHECK OPTIONを指定してビューを作成した場合、ビューを定義する問合せを満たす行のみビューに挿入されます。
単一の実表を使用してビューを作成した場合、行をビューに挿入し、returning_clauseを使用してその行の値を取り出せます。
ビューを定義する問合せに次のいずれかの構造体が含まれる場合は、INSTEAD OFトリガーを使用する場合を除いて、そのビューに行を挿入できません。
DISTINCT演算子GROUP BY、ORDER BY、MODEL、CONNECT BYまたはSTART WITH句SELECT構文のリストにあるコレクション式SELECT構文のリストにある副問合せWITH READ ONLYが指定された副問合せUNUSABLEのマークが付いている索引、索引パーティションまたは索引サブパーティションを指定する場合、SKIP_UNUSABLE_INDEXESセッション・パラメータがTRUEに設定されていないかぎり、INSERT文は正常に実行されません。SKIP_UNUSABLE_INDEXESセッション・パラメータの詳細は、「ALTER SESSION」を参照してください。
partition_extension_clause 挿入先のtableまたはviewの実表内にあるパーティションまたはサブパーティションの名前またはパーティション・キー値を指定します。
挿入する行が特定のパーティションまたはサブパーティションにマップされない場合、エラーが戻されます。
ターゲットのパーティションおよびサブパーティションの制限事項: この句は、オブジェクト表またはオブジェクト・ビューでは無効です。
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制約の名前を指定します。この識別子を省略した場合は、Oracleによって自動的にSYS_Cnという形式の制約名が割り当てられます(nはデータベース内で制約名を一意にするための整数)。
table_collection_expressionを使用すると、問合せおよびDML操作で、collection_expression値を表として扱うことができます。collection_expressionには、副問合せ、列、ファンクションまたはコレクション・コンストラクタのいずれかを指定できます。その形式にかかわらず、集合値(ネストした表型またはVARRAY型の値)を戻す必要があります。このようなコレクションの要素抽出プロセスをコレクション・ネスト解除といいます。
TABLEコレクション式を親表と結合する場合は、オプションのプラス(+)には大きな意味があります。+を指定すると、その2つの外部結合が作成され、コレクション式がNULLの場合でも、外部表の行が問合せで戻されるようになります。
|
注意: 以前のリリースのOracleでは、collection_expressionが副問合せの場合、table_collection_expressionをTHE subqueryと表現していました。現在、このような表現方法は非推奨になっています。 |
t_alias
相関名を指定します。これは、文中で参照する表、ビュー、マテリアライズド・ビューまたは副問合せの別名です。
表の別名の制限事項: t_aliasは、マルチテーブル・インサートに指定できません。
表、ビューまたはマテリアライズド・ビューの列を指定します。挿入された行では、このリストにある各列にvalues_clauseまたは副問合せの値が代入されます。
このリストに1つ以上の列を指定しない場合、挿入された行の、指定しなかった列の値には、表の作成時または最終更新時に指定した列のデフォルト値が使用されます。指定しなかった列のいずれかにNOT NULL制約があり、デフォルト値がない場合、制約違反のエラーが発生してINSERT文がロールバックされます。列のデフォルト値の詳細は、「CREATE TABLE」を参照してください。
列リストを指定しない場合は、values_clauseまたは問合せに、表の列をすべて指定する必要があります。
シングルテーブル・インサート操作の場合、表またはビューに挿入する値の行を指定します。なお、値は、列リスト内の各列についてvalues_clauseに指定する必要があります。列リストを指定しない場合、values_clauseまたは副問合せで、表の各列の値を指定する必要があります。
マルチテーブル・インサート操作の場合、values_clauseの各式は、副問合せのSELECT構文のリストによって戻される列を参照する必要があります。values_clauseを省略すると、副問合せのSELECT構文のリストによって挿入する値が決定されるため、insert_into_clauseに対応する列リストと同じ列数を含んでいる必要があります。insert_into_clauseで列リストを指定しない場合、対象となる表のすべての列に対する値を計算した行を指定する必要があります。
どちらの挿入操作の場合も、insert_into_clauseで列リストを指定すると、リストの各列に値の句または副問合せからの対応する値が割り当てられます。values_clauseの任意の値に対してDEFAULTを指定できます。表またはビューの対応する列に対してデフォルト値を指定すると、その値が挿入されます。対応する列に対してデフォルト値を指定しない場合、NULLが挿入されます。有効な式の構文の詳細は、「SQL式」および「SELECT」を参照してください。
挿入値の制限事項: この値には、次の制限事項があります。
BFILEロケータをNULLに、またはディレクトリ名およびファイル名に初期化するまで、BFILE値を挿入できません。
|
関連項目:
|
リスト・パーティション表に挿入する場合、1つのパーティションのpartition_key_valueリストに存在していないパーティション化キー列に値を挿入できません。
ビューに挿入する場合は、DEFAULTを指定できません。
RAW列に文字列リテラルを挿入する場合、後続の問合せ中にRAW列にある索引は使用されずに、全表スキャンが行われます。
|
関連項目:
|
returning_clause
この句を使用すると、DML文に影響される行を取り出すことができます。この句は、表、マテリアライズド・ビュー、および単一の実表を持つビューに指定できます。
returning_clauseを指定したDML文を単一行に実行すると、影響された行、ROWID、および処理された行へのREFを使用している列式が取り出され、ホスト変数またはPL/SQL変数に格納されます。
returning_clauseを指定したDML文を複数行に実行すると、式の値、ROWIDおよび処理された行に関連するREFがバインド配列に格納されます。
expr exprリストの各項目は、適切な構文で表す必要があります。
INTO INTO句を指定すると、変更された行の値を、data_itemリストに指定する変数に格納できます。
data_item data_itemはそれぞれ、取り出したexpr値を格納するためのホスト変数またはPL/SQL変数です。
RETURNINGリストの各式については、INTOリストに、対応する型に互換性があるPL/SQL変数またはホスト変数を指定する必要があります。
制限事項: RETURNING句には、次の制限事項があります。
exprに次の制限事項があります。
UPDATE文およびDELETE文の場合、各exprは、単純式または単一セットの集計ファンクション式である必要があります。1つのreturning_clause内に単純式と単一セットの集計ファンクション式を混在させることはできません。INSERT文の場合、各exprは単純式である必要があります。INSERT文のRETURNING句では、集計ファンクションはサポートされていません。
単一セットの集計ファンクション式をDISTINCTキーワードに含めることはできません。
exprのリストに主キー列または他のNOT NULL列が含まれている場合は、表にBEFORE UPDATEトリガーが定義されていると、UPDATE文の実行に失敗します。
マルチテーブル・インサートではreturning_clauseを指定できません。
パラレルDMLまたはリモート・オブジェクトにはこの句を使用できません。
LONG型を取り出すことはできません。
INSTEAD OFトリガーが定義されたビューに対して指定することはできません。
|
関連項目: BULK COLLECT句を使用してコレクション変数に複数の値を戻す場合は、 |
マルチテーブル・インサートでは、副問合せの評価によって戻された行から導出され、計算された行が1つ以上の表に挿入されます。
副問合せのSELECT構文のリストでは、表の別名は定義されていません。そのため、SELECT構文のリストに依存する句では、表の別名は参照できません。たとえば、式内のオブジェクト列を参照しようとしても、表の別名は参照できません。表の別名とともに式を使用する場合は、列の別名を使用して式をSELECT構文のリストに挿入してから、マルチテーブル・インサートのVALUES句またはWHEN条件で列の別名を参照する必要があります。
ALL into_clause
ALLの後に複数のinsert_into_clausesを指定すると、無条件のマルチテーブル・インサートを実行できます。副問合せによって戻される各行に対して、各insert_into_clauseが1回実行されます。
conditional_insert_clauseを指定すると、条件付きのマルチテーブル・インサートを実行できます。各insert_into_clauseは、それに対応するWHEN条件によってフィルタ処理されます。この条件によって、insert_into_clauseが実行されるかどうかが決まります。WHEN条件の各式は、副問合せのSELECT構文のリストによって戻される列を参照する必要があります。1つのマルチテーブル・インサートには、最大127個のWHEN句を指定できます。
ALL ALL(デフォルト値)を指定すると、他のWHEN句の評価結果にかかわらず、各WHEN句が評価されます。条件が真と評価された各WHEN句に対して、対応するINTO句リストが実行されます。
FIRST FIRSTを指定すると、文で指定されている順序で各WHEN句が評価されます。真と評価された最初のWHEN句に対して、対応するINTO句が実行され、指定された行に対する後続のWHEN句はスキップされます。
ELSE句 指定された行に対して、WHEN句が真と評価されない場合、次のようになります。
ELSE句を指定する場合、ELSE句に関連付けられたINTO句リストが実行されます。
ELSE句を指定しない場合、その行に対して何も実行されません。
マルチテーブル・インサートの制限事項: マルチテーブル・インサートには、次の制限事項があります。
表のみにマルチテーブル・インサートが実行でき、ビューまたはマテリアライズド・ビューには実行できません。
リモート表には、マルチテーブル・インサートを実行できません。
マルチテーブル・インサートの実行時、TABLEコレクション式は指定できません。
マルチテーブル・インサートは、対象となる表が索引構成されている場合、または対象となる表にビットマップ索引が定義されている場合は、パラレル化されません。
プラン・スタビリティは、マルチテーブル・インサート文にはサポートされません。
マルチテーブル・インサート文のどの部分にも順序を指定することはできません。マルチテーブル・インサートは、単一のSQL文とみなされます。したがって、NEXTVALを初めて参照するときに、その次の番号が生成された後、この番号と同じ番号が、この文の後続のすべての参照で戻されます。
subquery
表に挿入される行を戻す副問合せを指定します。副問合せによって、INSERT文の対象となる表を含む任意の表、ビューおよびマテリアライズド・ビューを参照できます。副問合せで選択された行が1行もない場合、表に行は挿入されません。
subqueryをTO_LOBファンクションと組み合せて、LONG列にある値を、同じ表の異なる列または別の表にあるLOB値に変換できます。
ビュー内でLONG値を別の列のLOB値に移行する場合、実表内で移行を実行してからビューにLOB列を追加する必要があります。
リモート表のLONG値をローカル表のLOB値に移行する場合、TO_LOBファンクションを使用してリモート表で移行を実行してから、INSERT ... subquery操作を実行して、リモート表からローカル表にLOB値をコピーする必要があります。
副問合せを使用する挿入の注意事項: 副問合せを使用した挿入には、次の注意事項があります。
subqueryが、既存のマテリアライズド・ビューと部分的または完全に同じビューを戻す場合、subqueryに指定された1つ以上の表のかわりにマテリアライズド・ビューがクエリー・リライトに使用されることがあります。
|
関連項目: マテリアライズド・ビューおよびクエリー・リライトの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。 |
subqueryがリモート・オブジェクトを参照する場合、参照がローカル・データベース上でオブジェクトにループバックしないかぎり、INSERTはパラレルで実行されます。ただし、DML_table_expression_clauseのsubqueryがリモート・オブジェクトを参照する場合は、INSERT操作はシリアルで実行されます。詳細は、「parallel_clause」を参照してください。
|
関連項目:
|
error_logging_clauseを使用すると、DMLエラーと、その影響を受ける行のログ列値を取得して、エラー・ロギング表に保存できます。
INTO table エラー・ロギング表の名前を指定します。この句を省略すると、DBMS_ERRLOGパッケージで生成されたデフォルトの名前が割り当てられます。エラー・ログ表のデフォルトの名前は、DML操作の対象となっている表の名前の最初の25文字を、ERR$_の後に加えたものです。
simple_expression 文のタグとして使用する値を指定します。エラー・ロギング表内のエラーは、この文で識別することになります。この式には、テキスト・リテラル、数値リテラル、一般のSQL式(バインド変数など)のいずれかを指定できます。テキスト・リテラルに変換する場合は、TO_CHAR(SYSDATE)のようなファンクション式も使用できます。
REJECT LIMIT この句を使用すると、記録するエラーの数の上限値を整数で指定できます。エラーの数がこの値を超えると、文が終了し、その文によって変更された内容がロールバックされます。この拒否の制限のデフォルトは0(ゼロ)です。パラレルDML操作の場合、拒否の制限はパラレル・サーバーごとに適用されます。
DMLエラー・ロギングの制限事項:
次の状態では、文の実行が失敗しロールバックは実行されますが、エラー・ロギング機能は起動されません。
遅延制約の違反。
一意制約違反または一意索引違反を発生する、ダイレクト・パスのINSERTまたはMERGE操作。
一意制約違反または一意索引違反を発生する、更新操作のUPDATEまたはMERGE。
エラー・ロギング表では、LONG、LOBまたはオブジェクト型の列のエラーは追跡できません。ただし、DML操作を行う表に、これらの型の列を含めることができます。
対応するエラー・ロギング表を、未サポートの型の列を含めるために作成または修正する場合、その列の名前が、ターゲットのDML表の未サポートの列に対応していると、DML文が解析時にエラーになります。
エラー・ロギング表に未サポートの列型が含まれない場合、エラー数が拒否の制限に達するまで、すべてのDMLエラーが記録されます。エラーが発生した行では、列の値とエラー・ロギング表の対応する列が、制御情報とともにログに記録されます。
|
関連項目:
|
例
表への値の挿入例: 次の文は、サンプル表departmentsに行を挿入します。
INSERT INTO departments VALUES (280, 'Recreation', 121, 1700);
manager_id列のデフォルト値が121としてdepartments表が作成された場合、次の文を発行できます。
INSERT INTO departments VALUES (280, 'Recreation', DEFAULT, 1700);
次の文は、employees表に6つの列で構成される行を挿入します。NULLまたは科学表記の数値を設定されている列がそれぞれ1つ含まれています。
INSERT INTO employees (employee_id, last_name, email,
hire_date, job_id, salary, commission_pct)
VALUES (207, 'Gregory', 'pgregory@example.com',
sysdate, 'PU_CLERK', 1.2E3, NULL);
次の文は、前述の例と同じ結果を表しますが、DML_table_expression_clauseにある副問合せを使用します。
INSERT INTO
(SELECT employee_id, last_name, email, hire_date, job_id,
salary, commission_pct FROM employees)
VALUES (207, 'Gregory', 'pgregory@example.com',
sysdate, 'PU_CLERK', 1.2E3, NULL);
副問合せを持つ値の挿入例: 次の文は、歩合給が給与の25%を超える従業員をbonuses表(「表へのマージ例:」で作成)にコピーします。
INSERT INTO bonuses SELECT employee_id, salary*1.1 FROM employees WHERE commission_pct > 0.25;
エラー・ロギングによる表への挿入例: 次の文は、サンプル・スキーマhr内にraises表を作成し、DBMS_ERRLOGパッケージを使用してエラー・ロギング表を作成して、employees表のデータをraises表に移入します。raisesのチェック制約に違反する挿入操作があると、その行をerrlogで参照できます。発生したエラーが10を超えると、その文は異常終了し、挿入された内容はロールバックされます。
CREATE TABLE raises (emp_id NUMBER, sal NUMBER
CONSTRAINT check_sal CHECK(sal > 8000));
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('raises', 'errlog');
INSERT INTO raises
SELECT employee_id, salary*1.1 FROM employees
WHERE commission_pct > .2
LOG ERRORS INTO errlog ('my_bad') REJECT LIMIT 10;
SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal FROM errlog;
ORA_ERR_MESG$ ORA_ERR_TAG$ EMP_ID SAL
--------------------------- -------------------- ------ -------
ORA-02290: check constraint my_bad 161 7700
(HR.SYS_C004266) violated
リモート・データベースへの挿入例: 次の文は、データベース・リンクremoteがアクセスできるデータベース上の、ユーザーhrが所有するemployees表に行を挿入します。
INSERT INTO employees@remote
VALUES (8002, 'Juan', 'Fernandez', 'juanf@example.com', NULL,
TO_DATE('04-OCT-1992', 'DD-MON-YYYY'), 'SH_CLERK', 3000,
NULL, 121, 20);
順序値の挿入例: 次の文は、departments_seq順序の次の値を持つ行を、departments表に挿入します。
INSERT INTO departments VALUES (departments_seq.nextval, 'Entertainment', 162, 1400);
バインド変数を使用した挿入例: 次の文は、出力バインド変数bnd1およびbnd2に挿入された行の値を戻します。バインド変数は最初に宣言しておく必要があります。
INSERT INTO employees
(employee_id, last_name, email, hire_date, job_id, salary)
VALUES
(employees_seq.nextval, 'Doe', 'john.doe@example.com',
SYSDATE, 'SH_CLERK', 2400)
RETURNING salary*12, job_id INTO :bnd1, :bnd2;
置換可能な表および列への挿入例: 次の例は、persons表(「置換可能な表および列のサンプル:」で作成)に挿入します。最初の文は、ルート型person_tを使用します。2番目の挿入は、person_tのサブタイプemployee_tを使用し、3番目の挿入はemployee_tのサブタイプpart_time_emp_tを使用します。
INSERT INTO persons VALUES (person_t('Bob', 1234));
INSERT INTO persons VALUES (employee_t('Joe', 32456, 12, 100000));
INSERT INTO persons VALUES (
part_time_emp_t('Tim', 5678, 13, 1000, 20));
次の例は、books表(「置換可能な表および列のサンプル:」で作成)に挿入します。属性値の指定が、置換可能な表の例と同一であることに注意してください。
INSERT INTO books VALUES (
'An Autobiography', person_t('Bob', 1234));
INSERT INTO books VALUES (
'Business Rules', employee_t('Joe', 3456, 12, 10000));
INSERT INTO books VALUES (
'Mixing School and Work',
part_time_emp_t('Tim', 5678, 13, 1000, 20));
組込みファンクションおよび条件を使用して、置換可能な表および列からデータを抽出することができます。例は、「TREAT」、「SYS_TYPEID」および「IS OF type条件」を参照してください。
TO_LOBファンクションを使用した挿入例: 次の例は、LONGデータを次のlong_tab表にあるLOB列にコピーします。
CREATE TABLE long_tab (pic_id NUMBER, long_pics LONG RAW);
まず、LOBを持つ表を作成します。
CREATE TABLE lob_tab (pic_id NUMBER, lob_pics BLOB);
次に、INSERT ... SELECTを使用して、LONG列のすべての行にあるデータを、新しく作成したLOB列にコピーします。
INSERT INTO lob_tab SELECT pic_id, TO_LOB(long_pics) FROM long_tab;
移行が問題なく終了したことを確認したら、long_pics表を削除できます。別の方法として、表が他の列を含む場合、次のように入力して表からLONG列を削除できます。
ALTER TABLE long_tab DROP COLUMN long_pics;
マルチテーブル・インサートの例: 次の例はマルチテーブル・インサートの構文を使用して、サンプル表sh.salesに、異なる構造の入力表からデータを挿入します。
|
注意: 例を簡潔に示すために表の列が無視されているため、sales表のNOT NULL制約は使用禁止になっています。 |
入力表は次のように構成されています。
SELECT * FROM sales_input_table;
PRODUCT_ID CUSTOMER_ID WEEKLY_ST SALES_SUN SALES_MON SALES_TUE SALES_WED SALES_THU SALES_FRI SALES_SAT
---------- ----------- --------- ---------- ---------- ---------- -------------------- ---------- ----------
111 222 01-OCT-00 100 200 300 400 500 600 700
222 333 08-OCT-00 200 300 400 500 600 700 800
333 444 15-OCT-00 300 400 500 600 700 800 900
マルチテーブル・インサートの文を次に示します。
INSERT ALL
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_input_table;
sales表には次の行のみが存在するものとします。内容は次のとおりです。
SELECT * FROM sales
ORDER BY prod_id, cust_id, time_id;
PROD_ID CUST_ID TIME_ID C PROMO_ID QUANTITY_SOLD AMOUNT COST
---------- ---------- --------- - ---------- ------------- ---------- ----------
111 222 01-OCT-00 100
111 222 02-OCT-00 200
111 222 03-OCT-00 300
111 222 04-OCT-00 400
111 222 05-OCT-00 500
111 222 06-OCT-00 600
111 222 07-OCT-00 700
222 333 08-OCT-00 200
222 333 09-OCT-00 300
222 333 10-OCT-00 400
222 333 11-OCT-00 500
222 333 12-OCT-00 600
222 333 13-OCT-00 700
222 333 14-OCT-00 800
333 444 15-OCT-00 300
333 444 16-OCT-00 400
333 444 17-OCT-00 500
333 444 18-OCT-00 600
333 444 19-OCT-00 700
333 444 20-OCT-00 800
333 444 21-OCT-00 900
次の例は、複数の表に挿入します。販売員に様々なサイズの注文に関する情報を提供するとします。小口、中口、大口および特別注文についての表を作成し、これらの表にサンプル表oe.ordersのデータを移入します。
CREATE TABLE small_orders
(order_id NUMBER(12) NOT NULL,
customer_id NUMBER(6) NOT NULL,
order_total NUMBER(8,2),
sales_rep_id NUMBER(6)
);
CREATE TABLE medium_orders AS SELECT * FROM small_orders;
CREATE TABLE large_orders AS SELECT * FROM small_orders;
CREATE TABLE special_orders
(order_id NUMBER(12) NOT NULL,
customer_id NUMBER(6) NOT NULL,
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
credit_limit NUMBER(9,2),
cust_email VARCHAR2(30)
);
最初のマルチテーブル・インサートでは、小口、中口および大口注文の表に対してのみ移入を行います。
INSERT ALL
WHEN order_total <= 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total <= 200000 THEN
INTO medium_orders
WHEN order_total > 200000 THEN
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id
FROM orders;
large_orders表への挿入のかわりに、ELSE句を使用しても、同じ結果が得られます。
INSERT ALL
WHEN order_total <= 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total <= 200000 THEN
INTO medium_orders
ELSE
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id
FROM orders;
次の例は、前述の例と同様、小口、中口および大口注文の表に挿入し、件数が290,000を超える注文をspecial_orders表に挿入します。この表は、文を単純にするために列の別名を使用する方法も示しています。
INSERT ALL
WHEN ottl <= 100000 THEN
INTO small_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 100000 and ottl <= 200000 THEN
INTO medium_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 200000 THEN
into large_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 290000 THEN
INTO special_orders
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;
最後の例は、FIRST句を使用して、件数が290,000を超える注文をspecial_orders表に挿入し、これらの注文をlarge_orders表から削除します。
INSERT FIRST
WHEN ottl <= 100000 THEN
INTO small_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 100000 and ottl <= 200000 THEN
INTO medium_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 290000 THEN
INTO special_orders
WHEN ottl > 200000 THEN
INTO large_orders
VALUES(oid, ottl, sid, cid)
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;
