INSERT
目的
INSERT
文を使用すると、表、ビューの実表、パーティション表のパーティション、コンポジット・パーティション表のサブパーティション、オブジェクト表またはオブジェクト・ビューの実表に、行を追加できます。
前提条件
表に行を挿入する場合は、その表が自分のスキーマ内にある必要があります。自分のスキーマ内にない場合は、その表に対するINSERT
オブジェクト権限が必要です。
ビューの実表に行を挿入する場合、ビューが定義されているスキーマの所有者には、その実表に対するINSERT
オブジェクト権限が必要です。また、他のユーザーのスキーマ内のビューに行を挿入する場合は、そのビューに対するINSERT
オブジェクト権限が必要です。
INSERT
ANY
TABLE
システム権限があれば、任意の表または任意のビューの実表に行を挿入できます。
表がリモート・データベースにある場合は、行を挿入する表に対するREAD
またはSELECT
オブジェクト権限も必要です。
表、パーティションまたはビューにデータを挿入するために使用する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チューニング・ガイド』を参照してください。
構文
single_table_insert::=
(insert_into_clause::=、values_clause::=、returning_clause::=、subquery::=、error_logging_clause::=)
values_clause::=
multi_table_insert::=
(insert_into_clause::=、values_clause::=、conditional_insert_clause::=、subquery::=、error_logging_clause::=)
conditional_insert_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::=
error_logging_clause::=
セマンティクス
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」を参照してください。
挿入対象の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_C
n
という形式の制約名が割り当てられます(nはデータベース内で制約名を一意にするための整数)。
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およびラージ・オブジェクト開発者ガイド』を参照してください。
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', '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
パッケージを使用してエラー・ロギング表を作成し、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', '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(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に関連する例を参照および実行します。