INSERT

目的

INSERT文を使用すると、表、ビューの実表、パーティション表のパーティション、コンポジット・パーティション表のサブパーティション、オブジェクト表またはオブジェクト・ビューの実表に、行を追加できます。

前提条件

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

ビューの実表に行を挿入する場合、ビューが定義されているスキーマの所有者には、その実表に対するINSERTオブジェクト権限が必要です。また、他のユーザーのスキーマ内のビューに行を挿入する場合は、そのビューに対するINSERTオブジェクト権限が必要です。

INSERT ANY TABLEシステム権限があれば、任意の表または任意のビューの実表に行を挿入できます。

表がリモート・データベースにある場合は、行を挿入する表に対するREADまたはSELECTオブジェクト権限も必要です。

従来型INSERTおよびダイレクト・パスINSERT

表、パーティションまたはビューにデータを挿入するために使用するINSERT文には、従来型INSERTおよびダイレクト・パスINSERTの2種類があります。従来型INSERT文を発行すると、表の空き領域を再利用して挿入され、参照整合性制約が維持されます。ダイレクト・パス・インサートの場合、表の既存データの後に、挿入したデータが追加されます。データは、バッファ・キャッシュを回避してデータファイルに直接書き込まれます。既存データの空き領域は再利用されません。この代替手法を使用すると、挿入操作中のパフォーマンスが向上します。また、これは、Oracleのダイレクト・パス・ローダー・ユーティリティであるSQL*Loaderの機能に似ています。パラレル・モードで作成された表に挿入する場合は、ダイレクト・パス・インサートがデフォルトです。

データベースでのREDOデータおよびUNDOデータの生成方法は、従来型INSERTとダイレクト・パス・インサートのいずれを使用しているかに一部関係しています。

• 従来型INSERTでは、表とアーカイブ・ログのロギング設定に関係なく、データとメタデータの両方に対する変更に対して常に最大のREDOおよびUNDOが生成され、データベースのロギング設定が強制的に使用されます。

• ダイレクト・パス・インサートでは、メタデータの変更に対してREDOとUNDOの両方が生成されます。これらは操作のリカバリに必要であるためです。データの変更に大してはUNDOとREDOが次のように生成されます。

  • ダイレクト・パス・インサートでは、データの変更に対するUNDOの生成が常に回避されます。

  • データベースがARCHIVELOGまたはFORCE LOGGINGモードでない場合は、表のロギング設定に関係なく、データの変更に対するREDOは生成されません。

  • データベースがARCHIVELOGモード(ただしFORCE LOGGINGモードではない)の場合、ダイレクト・パスINSERTでは、LOGGING表のデータのREDOは生成されますが、NOLOGGING表のデータのREDOは生成されません。

  • データベースがARCHIVELOGモードかつFORCE LOGGINGモードの場合、ダイレクト・パスSQLでは、LOGGING表とNOLOGGING表の両方のデータのREDOが生成されます。

ダイレクト・パス・インサートには、次の制限があります。これらの制限に違反する場合、他にエラーがないかぎりメッセージが戻されず、従来型INSERTがシリアルで実行されます。

• DML文の有無にかかわらず、1つのトランザクションで複数のダイレクト・パス・インサート文を使用できます。ただし、あるDML文が特定の表、パーティションまたは索引を変更した後は、トランザクションの他のDML文は表、パーティションまたは索引にアクセスできません。

• 同じ表、パーティションまたは索引にアクセスする問合せは、ダイレクト・パス・インサート文の前であれば許可されますが、後は許可されません。

• シリアルまたはパラレル文が、同じトランザクションからダイレクト・パス・インサートによって変更された表にアクセスしようとする場合、エラーが戻され、文が拒否されます。

• 対象となる表は、クラスタにはできません。

• 対象となる表は、オブジェクト型列を含むことはできません。

• 索引構成表(IOT)がマッピング表を含んでいる場合、またはマテリアライズド・ビューによって参照されている場合、その索引構成表に対してダイレクト・パス・インサートを使用することはできません。

• 索引構成表(IOT)の1つのパーティション、1つのパーティションのみを含むパーティション化されたIOT、またはパーティション化されていないIOTへのダイレクト・パスINSERTは、IOTがパラレル・モードで作成されている場合またはAPPENDヒントやAPPEND_VALUESヒントを指定した場合でも、シリアルで実行されます。ただし、パーティション化されたIOTへのダイレクト・パス・インサート操作は、拡張パーティション名が使用されず、IOTに複数のパーティションが含まれているかぎり、パラレル・モードで実行されます。

• ターゲット表には、トリガーおよび参照整合性制約を定義できない。

• 対象となる表は、レプリケートできません。

• ダイレクト・パス・インサート文を含むトランザクションは、分散できません。

ダイレクト・パス・インサートによって挿入したデータを、挿入した直後に問合せまたは変更することはできません。問合せまたは変更しようとすると、ORA-12838エラーが発生します。新しく挿入したデータの読取りまたは変更を試みる前に、COMMIT文を発行する必要があります。

関連項目:

• ダイレクト・パス・インサートの詳細は、『Oracle Database管理者ガイド』を参照してください。

• SQL*Loaderの詳細は、『Oracle Databaseユーティリティ』を参照してください。

• ダイレクト・パスINSERTを使用して空の表への挿入を行うときの統計収集の詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください。

構文

insert::=

図insert.epsの説明

(single_table_insert::=、multi_table_insert::=)

single_table_insert::=

図single_table_insert.epsの説明

(insert_into_clause::=、values_clause::=、returning_clause::=、subquery::=、error_logging_clause::=)

insert_into_clause::=

図insert_into_clause.epsの説明

(DML_table_expression_clause::=)

values_clause::=

図values_clause.epsの説明

returning_clause::=

図returning_clause.epsの説明

multi_table_insert::=

図multi_table_insert.epsの説明

(insert_into_clause::=、values_clause::=、conditional_insert_clause::=、subquery::=、error_logging_clause::=)

conditional_insert_clause::=

図conditional_insert_clause.epsの説明

(insert_into_clause::=、values_clause::=)

DML_table_expression_clause::=を参照

図dml_table_expression_clause.epsの説明

(partition_extension_clause::=、subquery::=(SELECTの一部)、subquery_restriction_clause::=、table_collection_expression::=)

partition_extension_clause::=

図partition_extension_clause.epsの説明

subquery_restriction_clause::=

図subquery_restriction_clause.epsの説明

table_collection_expression::=

図table_collection_expression.epsの説明

error_logging_clause::=

図error_logging_clause.epsの説明

セマンティクス

hint

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

マルチテーブル・インサートの場合、対象となる表に対してPARALLELヒントを指定すると、表がPARALLELの指定付きで作成または変更されていなくても、マルチテーブル・インサート文全体がパラレル化されます。PARALLELヒントを指定しない場合、対象となるすべての表がPARALLELの指定付きで作成または変更されていないかぎり、挿入操作はパラレル化されません。

関連項目:

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

• 「マルチテーブル・インサートの制限事項」

single_table_insert

シングルテーブル・インサートの場合、明示的に値を指定する、または副問合せで値を検索することによって、表、ビューまたはマテリアライズド・ビューの1行に値を挿入します。

subqueryでflashback_query_clauseを使用すると、過去のデータをtableに挿入できます。この句の詳細は、「SELECT」の「flashback_query_clause」を参照してください。

シングルテーブル・インサートの制限事項

副問合せで値を検索する場合、副問合せのSELECT構文のリストにはINSERT文の列リストと同じ数の列が含まれている必要があります。列リストを指定しない場合は、副問合せで表の各列の値を指定する必要があります。

関連項目:

表への値の挿入: 例

insert_into_clause

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が指定された副問合せ
  • 結合(一部の例外を除く。詳細は、『Oracle Database管理者ガイド』を参照してください。)

• UNUSABLEのマークが付いている索引、索引パーティションまたは索引サブパーティションを指定する場合、SKIP_UNUSABLE_INDEXESセッション・パラメータがTRUEに設定されていないかぎり、INSERT文は正常に実行されません。SKIP_UNUSABLE_INDEXESセッション・パラメータの詳細は、「ALTER SESSION」を参照してください。

partition_extension_clause

挿入対象のtableまたはviewの実表内にあるパーティションまたはサブパーティションの名前またはパーティション・キー値を指定します。

挿入する行が特定のパーティションまたはサブパーティションにマップされない場合、エラーが戻されます。

ターゲットのパーティションおよびサブパーティションの制限事項

この句は、オブジェクト表またはオブジェクト・ビューでは無効です。

関連項目:

パーティション表と索引の参照

dblink

表またはビューがあるリモート・データベースへのデータベース・リンクの完全または部分的な名前を指定します。Oracle Databaseの分散機能を使用している場合にのみ、リモート表またはリモート・ビューに行を挿入できます。

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

ノート:

Oracle Database 12cリリース2 (12.2)以降では、INSERT文で、リモートのLOBロケータをバインド変数として指定できます。詳細は、『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』の「分散LOB」の章を参照してください。

関連項目:

• データベース・リンクの参照方法の詳細は、「スキーマ・オブジェクトの構文およびSQL文の構成要素」および「リモート・データベース内のオブジェクトの参照」を参照してください。

• リモート・データベースへの挿入: 例

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はデータベース内で制約名を一意にするための整数)。

関連項目:

WITH CHECK OPTION句の使用方法: 例

table_collection_expression

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

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

ノート:

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

関連項目:

表のコレクション: 例

t_alias

相関名を指定します。これは、文中で参照する表、ビュー、マテリアライズド・ビューまたは副問合せの別名です。

表の別名の制限事項

マルチテーブル・インサート中はt_aliasを指定できません。

column

表、ビューまたはマテリアライズド・ビューの列を指定します。挿入された行では、このリストにある各列にvalues_clauseまたは副問合せの値が代入されます。INVISIBLE列に値を割り当てる場合は、列をこのリストに含める必要があります。

このリストに1つ以上の列を指定しない場合、挿入された行の、指定しなかった列の値には、表の作成時または最終更新時に指定した列のデフォルト値が使用されます。指定しなかった列のいずれかにNOT NULL制約があり、デフォルト値がない場合、制約違反のエラーが発生してINSERT文がロールバックされます。列のデフォルト値の詳細は、「CREATE TABLE」を参照してください。

列リストを指定しない場合は、values_clauseまたは問合せに、表の列をすべて指定する必要があります。

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値を挿入できません。

  関連項目:

  • BFILE値の初期化の詳細およびBFILEへの挿入例については、「BFILENAME」を参照してください。

  • BFILEロケータの初期化の詳細は、『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』を参照してください。

• リスト・パーティション表に挿入する場合、1つのパーティションのpartition_key_valueリストに存在していないパーティション化キー列に値を挿入できません。

• ビューに挿入する場合は、DEFAULTを指定できません。

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

  関連項目:

  • XMLType表への値の挿入の詳細は、「SQL文でのXMLの使用方法」を参照してください。

  • 「置換可能な表および列への挿入: 例」、「TO_LOBファンクションを使用した挿入: 例」、「表への順序値の挿入: 例」および「バインド変数を使用した挿入: 例」を参照してください。

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句を使用してコレクション変数に複数の値を戻す場合は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

multi_table_insert

マルチテーブル・インサートでは、副問合せの評価によって戻された行から導出され、計算された行が1つ以上の表に挿入されます。

副問合せのSELECT構文のリストでは、表の別名は定義されていません。そのため、SELECT構文のリストに依存する句では、表の別名は参照できません。たとえば、式内のオブジェクト列を参照しようとしても、表の別名は参照できません。表の別名とともに式を使用する場合は、列の別名を使用して式をSELECT構文のリストに挿入してから、マルチテーブル・インサートのVALUES句またはWHEN条件で列の別名を参照する必要があります。

ALL into_clause

ALLの後に複数のinsert_into_clausesを指定すると、無条件のマルチテーブル・インサートを実行できます。副問合せによって戻される各行に対して、各insert_into_clauseが1回実行されます。

conditional_insert_clause

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 clause

指定された行に対して、WHEN句が真と評価されない場合、次のようになります。

• ELSE句を指定する場合、ELSE句に関連付けられたINTO句リストが実行されます。

• ELSE句を指定しない場合、その行に対して何も実行されません。

関連項目:

「マルチテーブル・インサート: 例」

マルチテーブル・インサートの制限事項

マルチテーブル・インサートには、次の制限事項があります。

• 表のみにマルチテーブル・インサートが実行でき、ビューまたはマテリアライズド・ビューには実行できません。

• リモート表には、マルチテーブル・インサートを実行できません。

• マルチテーブル・インサートの実行時、表のコレクション式は指定できません。

• マルチテーブル・インサートは、対象となる表が索引構成されている場合、または対象となる表にビットマップ索引が定義されている場合は、パラレル化されません。

• プラン・スタビリティは、マルチテーブル・インサート文にはサポートされません。

• マルチテーブル・インサート文のどの部分にも順序を指定することはできません。マルチテーブル・インサートは、単一の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」を参照してください。

• subqueryにORDER BY句が含まれる場合、属性クラスタリング表のプロパティを使用して指定された行順序はオーバーライドされます。

関連項目:

• 副問合せを持つ値の挿入: 例

• BFILEへの挿入例については、「BFILENAME」を参照してください。

• BFILEの初期化の詳細は、『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』を参照してください。

• 有効な式の構文の詳細は、「SQL式」および「SELECT」を参照してください。

error_logging_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エラーが記録されます。エラーが発生した行では、列の値とエラー・ロギング表の対応する列が、制御情報とともにログに記録されます。

関連項目:

• DBMS_ERRLOGパッケージのcreate_error_logプロシージャの使用方法は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。DMLエラー・ロギングに関する一般情報は、『Oracle Database管理者ガイド』を参照してください。

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

例

表への値の挿入: 例

次の文は、サンプル表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', '[e-mail]', 
      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', '[e-mail]', 
      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パッケージを使用してエラー・ロギング表を作成し、raises表にemployees表からのデータを移入します。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', '[e-mail]', 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', '[e-mail]', 
       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(40)
   );

最初のマルチテーブル・インサートでは、小口、中口および大口注文の表に対してのみ移入を行います。

INSERT ALL
   WHEN order_total <= 100000 THEN
      INTO small_orders
   WHEN order_total > 1000000 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;

単一文を使用した複数行の挿入: 例

次の文は、people、patientsおよびstaffという名前の3つの表を作成します。

CREATE TABLE people ( 
  person_id   INTEGER NOT NULL PRIMARY KEY, 
  given_name  VARCHAR2(100) NOT NULL, 
  family_name VARCHAR2(100) NOT NULL, 
  title       VARCHAR2(20), 
  birth_date  DATE 
);

CREATE TABLE patients ( 
  patient_id          INTEGER NOT NULL PRIMARY KEY REFERENCES people (person_id), 
  last_admission_date DATE 
);

CREATE TABLE staff ( 
  staff_id   INTEGER NOT NULL PRIMARY KEY REFERENCES people (person_id), 
  hired_date DATE 
);

次の文は、people表に行を挿入します。

INSERT INTO people 
VALUES (1, 'Dave', 'Badger', 'Mr', date'1960-05-01');

次の文は、birth_date列に指定された値がないため、エラーを戻します。

INSERT INTO people 
VALUES (2, 'Simon', 'Fox', 'Mr');

次の文は、people表に行を挿入します。

INSERT INTO people (person_id, given_name, family_name, title) 
VALUES (2, 'Simon', 'Fox', 'Mr');

次の文は、people表に1つの行を挿入し、DUAL表から静的値を選択してtitle列の値を移入します。

INSERT INTO people (person_id, given_name, family_name, title) 
VALUES (3, 'Dave', 'Frog', (SELECT 'Mr' FROM dual));

次の文は、SELECT文を使用してpeople表に複数の行を挿入します。

INSERT INTO people (person_id, given_name, family_name, title) 
  WITH names AS ( 
    SELECT 4, 'Ruth',     'Fox',      'Mrs'    FROM dual UNION ALL 
    SELECT 5, 'Isabelle', 'Squirrel', 'Miss'   FROM dual UNION ALL 
    SELECT 6, 'Justin',   'Frog',     'Master' FROM dual UNION ALL 
    SELECT 7, 'Lisa',     'Owl',      'Dr'     FROM dual 
  ) 
  SELECT * FROM names;

次の文は、これまでのDML操作をすべてロールバックします。

ROLLBACK;

次の文は、WHERE条件を指定したSELECT文を使用して、複数の行をpeople表に挿入します。

INSERT INTO people (person_id, given_name, family_name, title) 
  WITH names AS ( 
    SELECT 4, 'Ruth',     'Fox' family_name,      'Mrs'    FROM dual UNION ALL 
    SELECT 5, 'Isabelle', 'Squirrel' family_name, 'Miss'   FROM dual UNION ALL 
    SELECT 6, 'Justin',   'Frog' family_name,     'Master' FROM dual UNION ALL 
    SELECT 7, 'Lisa',     'Owl' family_name,      'Dr'     FROM dual 
  ) 
  SELECT * FROM names 
  WHERE  family_name LIKE 'F%';

次の文は、これまでのDML操作をすべてロールバックします。

ROLLBACK;

次の文は、INSERT ALL文を使用して、people表、patients表およびstaff表に複数の行を挿入します。

INSERT ALL 
  /* Every one is a person */ 
  INTO people (person_id, given_name, family_name, title) 
    VALUES (id, given_name, family_name, title) 
  INTO patients (patient_id, last_admission_date) 
    VALUES (id, admission_date) 
  INTO staff (staff_id, hired_date) 
    VALUES (id, hired_date) 
  WITH names AS ( 
    SELECT 4 id, 'Ruth' given_name, 'Fox' family_name, 'Mrs' title, 
           NULL hired_date, DATE'2009-12-31' admission_date 
    FROM   dual UNION ALL 
    SELECT 5 id, 'Isabelle' given_name, 'Squirrel' family_name, 'Miss' title , 
           NULL hired_date, DATE'2014-01-01' admission_date 
    FROM   dual UNION ALL 
    SELECT 6 id, 'Justin' given_name, 'Frog' family_name, 'Master' title, 
           NULL hired_date, DATE'2015-04-22' admission_date 
    FROM   dual UNION ALL 
    SELECT 7 id, 'Lisa' given_name, 'Owl' family_name, 'Dr' title, 
           DATE'2015-01-01' hired_date, NULL admission_date 
    FROM   dual 
  ) 
  SELECT * FROM names;

次の文は、これまでのDML操作をすべてロールバックします。

ROLLBACK;

次の文は、様々な条件を指定したINSERT ALL文を使用して、people表、patients表およびstaff表に複数の行を挿入します。

INSERT ALL 
  /* Everyone is a person, so insert all rows into people */ 
  WHEN 1=1 THEN 
    INTO people (person_id, given_name, family_name, title) 
    VALUES (id, given_name, family_name, title) 
  /* Only people with an admission date are patients */ 
  WHEN admission_date IS NOT NULL THEN 
    INTO patients (patient_id, last_admission_date) 
    VALUES (id, admission_date) 
  /* Only people with a hired date are staff */ 
  WHEN hired_date IS NOT NULL THEN 
    INTO staff (staff_id, hired_date) 
    VALUES (id, hired_date) 
  WITH names AS ( 
    SELECT 4 id, 'Ruth' given_name, 'Fox' family_name, 'Mrs' title, 
           NULL hired_date, DATE'2009-12-31' admission_date 
    FROM   dual UNION ALL 
    SELECT 5 id, 'Isabelle' given_name, 'Squirrel' family_name, 'Miss' title , 
           NULL hired_date, DATE'2014-01-01' admission_date 
    FROM   dual UNION ALL 
    SELECT 6 id, 'Justin' given_name, 'Frog' family_name, 'Master' title, 
           NULL hired_date, DATE'2015-04-22' admission_date 
    FROM   dual UNION ALL 
    SELECT 7 id, 'Lisa' given_name, 'Owl' family_name, 'Dr' title, 
           DATE'2015-01-01' hired_date, NULL admission_date 
    FROM   dual 
  ) 
  SELECT * FROM names;

Live SQL:

「単一文を使用した複数行の挿入」で、Oracle Live SQLに関連する例を参照および実行します。