DELETE
目的
-
非パーティション表またはパーティション表
-
ビューの非パーティション実表またはパーティション実表
-
書込み可能なマテリアライズド・ビューの非パーティション・コンテナ表またはパーティション・コンテナ表
-
更新可能なマテリアライズド・ビューの非パーティション・マスター表またはパーティション・マスター表
前提条件
表から行を削除する場合、その表が自分のスキーマ内にある必要があります。自分のスキーマ内にない場合は、その表に対するDELETEオブジェクト権限が必要です。
更新可能なマテリアライズド・ビューから行を削除する場合、そのマテリアライズド・ビューが自分のスキーマ内にある必要があります。自分のスキーマ内にない場合は、そのマテリアライズド・ビューに対するDELETEオブジェクト権限が必要です。
ビューの実表から行を削除する場合、そのビューが含まれるスキーマの所有者には、その実表に対するDELETEオブジェクト権限が必要です。また、他のスキーマ内にビューが存在している場合は、そのビューに対するDELETEオブジェクト権限が必要です。
DELETE ANY TABLEシステム権限を持っている場合、任意の表、表パーティションまたは任意のビューの実表から行を削除できます。
リモート・データベースのオブジェクトの行を削除するには、オブジェクトのREADまたはSELECTオブジェクト権限も必要です。
SQL92_SECURITY初期化パラメータがTRUEに設定され、DELETE操作がwhere_clauseの列などの表の列を参照する場合、行を削除するオブジェクトのSELECTオブジェクト権限も必要です。
表のファンクション索引が無効な状態にあるとき、表から行を削除できません。まず、ファンクション索引を検証する必要があります。
構文
delete::=
(DML_table_expression_clause::=、where_clause::=、returning_clause::=、error_logging_clause::=)
DML_table_expression_clause::=を参照
(partition_extension_clause::=、subquery::=、subquery_restriction_clause::=、table_collection_expression::=)
partition_extension_clause::=
subquery_restriction_clause::=
table_collection_expression::=
where_clause::=
returning_clause::=
error_logging_clause::=
セマンティクス
from_clause
FROM句を使用すると、行を削除するデータベース・オブジェクトを指定できます。
ONLY構文は、ビューのみに関連します。FROM句のビューがビューの階層に属し、そのいずれのサブビューからも行を削除しない場合は、ONLY句を使用します。
DML_table_expression_clause
この句を使用すると、データを削除するオブジェクトを指定できます。
schema
表またはビューが含まれているスキーマを指定します。schemaを指定しない場合、表またはビューは自分のスキーマにあるとみなされます。
table | view | materialized view | subquery
行を削除する表、ビュー、マテリアライズド・ビュー、列または副問合せの結果の列の名前を指定します。
更新可能なビューから行を削除すると、実表から行が削除されます。
読取り専用マテリアライズド・ビューからは行を削除できません。書込み可能なマテリアライズド・ビューから行を削除する場合、基礎となるコンテナ表から行が削除されます。ただし、その削除は次のリフレッシュ操作で上書きされます。マテリアライズド・ビュー・グループ内の更新可能なマテリアライズド・ビューから行を削除する場合、マスター表の対応する行も削除されます。
table、viewの実表またはmaterialized_viewのマスター表に、1列以上のドメイン索引列が含まれる場合は、この文によって適切な索引タイプの削除ルーチンが実行されます。
関連項目:
これらのルーチンの詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
表に対してDELETE文を実行すると、その表に定義されているDELETEトリガーが起動されます。
行の削除によって解放される表や索引のすべての領域は、表および索引によって引き続き保持されます。
partition_extension_clause
オブジェクト内にある削除対象のパーティションまたはサブパーティションの名前またはパーティション・キー値を指定します。
パーティション・オブジェクトから値を削除する場合、そのパーティション名を指定する必要はありません。ただし、パーティション名を指定した方が、複雑なwhere_clauseより効率が上がることがあります。
関連項目:
「パーティション表と索引の参照」および「パーティションからの行の削除: 例」を参照してください。
dblink
オブジェクトが格納されているリモート・データベースへのデータベース・リンクの完全名または部分名を指定します。Oracle Databaseの分散機能を使用している場合にのみ、リモート・オブジェクトから行を削除できます。
ノート:
Oracle Database 12cリリース2 (12.2)以降、DELETE文は、バインド変数としてリモートLOBロケータを受け入れます。詳細は、『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』の「分散LOB」の章を参照してください。
関連項目:
データベース・リンクの参照方法に関する「リモート・データベース内のオブジェクトの参照」、および「リモート・データベースからの行の削除: 例」を参照してください。
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
table_collection_expressionを使用すると、問合せおよびDML操作で、collection_expression値を表として扱うことができます。collection_expressionには、副問合せ、列、ファンクションまたはコレクション・コンストラクタのいずれかを指定できます。その形式にかかわらず、集合値(ネストした表型またはVARRAY型の値)を戻す必要があります。このようなコレクションの要素抽出プロセスをコレクション・ネスト解除といいます。
TABLEコレクション式を親表と結合する場合は、オプションのプラス(+)には大きな意味があります。+を指定すると、その2つの外部結合が作成され、コレクション式がNULLの場合でも、外部表の行が問合せで戻されるようになります。
ノート:
以前のリリースのOracleでは、collection_expressionが副問合せの場合、table_collection_expressionをTHE subqueryと表現していました。現在、このような表現方法は非推奨になっています。
相関副問合せでtable_collection_expressionを使用すると、他の表に存在する値で行を削除できます。
関連項目:
collection_expression
行を削除するオブジェクトからネストした表の列を選択する副問合せを指定します。
dml_table_expression_clause句の制限事項
この句には、次の制限事項があります。
-
viewまたはmaterialized_viewのtable(実表またはマスター表)にIN_PROGRESSまたはFAILEDのマークが付いたドメイン索引がある場合、この文は実行できません。 -
関係する索引パーティションが
UNUSABLEとマークされている場合は、パーティションに挿入できません。 -
DML_table_expression_clauseの副問合せでは、ORDERBY句を指定できません。 -
ビューを定義する問合せに次のいずれかの構造体が含まれている場合は、
INSTEADOFトリガーを使用する場合を除いて、ビューから行を削除できません。- 集合演算子
DISTINCT演算子- 集計ファンクションまたは分析ファンクション
GROUPBY、ORDERBY、MODEL、CONNECTBYまたはSTARTWITH句SELECT構文のリストにあるコレクション式SELECT構文のリストにある副問合せWITH READ ONLYが指定された副問合せ- 結合(一部の例外を除く。詳細は、『Oracle Database管理者ガイド』を参照してください。)
UNUSABLEのマークが付いている索引、索引パーティションまたは索引サブパーティションを指定する場合、SKIP_UNUSABLE_INDEXES初期化パラメータがtrueに設定されていないかぎり、DELETE文は正常に実行されません。
関連項目:
t_alias
文中で参照する表、ビュー、マテリアライズド・ビュー、副問合せまたはコレクション値の相関名を指定します。DML_table_expression_clauseがいずれかのオブジェクト型属性またはオブジェクト型メソッドを参照する場合、この別名が必要です。通常、別名は相関問合せを持つDELETE文で使用されます。
where_clause
where_clauseを使用すると、条件を満たす行のみを削除できます。この条件は、行を削除するオブジェクトを参照したり、副問合せを含むことができます。Oracle Databaseの分散機能を使用している場合にのみ、リモート・オブジェクトから行を削除できます。conditionの構文は、「条件」を参照してください。
この句がリモート・オブジェクトを参照するsubqueryを含む場合、参照がローカル・データベース上でオブジェクトにループバックしないかぎり、DELETEはパラレルで実行されます。ただし、DML_table_expression_clauseのsubqueryがリモート・オブジェクトを参照する場合は、DELETE操作はシリアルで実行されます。詳細は、「CREATE TABLE」の「parallel_clause」を参照してください。
dblinkを省略した場合、表またはビューがローカル・データベース上にあるとみなされます。
where_clauseを省略した場合、オブジェクトのすべての行が削除されます。
returning_clause
この句を使用すると、削除された列から値を戻すことができるため、DELETE文の後でSELECT文を実行する必要がなくなります。
この句を使用すると、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句の制限事項
RETURNING句には、次の制限事項があります。
-
exprに次の制限事項があります。-
UPDATE文およびDELETE文の場合、各exprは、単純式または単一セットの集計ファンクション式である必要があります。1つのreturning_clause内に単純式と単一セットの集計ファンクション式を混在させることはできません。INSERT文の場合、各exprは単純式である必要があります。INSERT文のRETURNING句では、集計ファンクションはサポートされていません。 -
単一セットの集計ファンクション式を
DISTINCTキーワードに含めることはできません。
-
-
exprのリストに主キー列または他のNOTNULL列が含まれている場合は、表にBEFOREUPDATEトリガーが定義されていると、UPDATE文の実行に失敗します。 -
マルチテーブル・インサートでは
returning_clauseを指定できません。 -
パラレルDMLまたはリモート・オブジェクトにはこの句を使用できません。
-
LONG型を取り出すことはできません。 -
INSTEADOFトリガーが定義されたビューに対して指定することはできません。
関連項目:
-
BULKCOLLECT句を使用してコレクション変数に複数の値を戻す場合は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
error_logging_clause
error_logging_clauseのDELETE文での動作は、INSERT文の場合と同じです。詳細は、「INSERT」文の「error_logging_clause」を参照してください。
関連項目:
例
行の削除: 例
次の文は、language_id列の値がARの、サンプル表oe.product_descriptionsからすべての行を削除します。
DELETE FROM product_descriptions WHERE language_id = 'AR';
次の文は、サンプル表hr.employeesから歩合率が10%未満の購買係を削除します。
DELETE FROM employees WHERE job_id = 'SA_REP' AND commission_pct < .2;
次の文は前述の例と同じ結果を表しますが、副問合せを使用します。
DELETE FROM (SELECT * FROM employees) WHERE job_id = 'SA_REP' AND commission_pct < .2;
リモート・データベースからの行の削除: 例
次の文は、データベース・リンクremoteからアクセス可能なデータベース上のユーザーhrが所有するlocations表から、指定された行を削除します。
DELETE FROM hr.locations@remote WHERE location_id > 3000;
ネストした表の行の削除: 例
ネストした表の行の削除例は、「表のコレクション: 例」を参照してください。
パーティションからの行の削除: 例
次の例は、sh.sales表のパーティションsales_q1_1998から行を削除します。
DELETE FROM sales PARTITION (sales_q1_1998) WHERE amount_sold > 1000;
RETURNING句の使用例:
次の例は、削除された行からsalary列を戻し、その結果をバインド配列:bnd1に格納します。バインド配列は事前に宣言しておく必要があります。
DELETE FROM employees
WHERE job_id = 'SA_REP'
AND hire_date + TO_YMINTERVAL('01-00') < SYSDATE
RETURNING salary INTO :bnd1;表からのデータの削除: 例
次の文は、product_price_historyという名前の表を作成し、この表にデータを挿入します。
CREATE TABLE product_price_history (
product_id INTEGER NOT NULL,
price INTEGER NOT NULL,
currency_code VARCHAR2(3 CHAR) NOT NULL,
effective_from_date DATE NOT NULL,
effective_to_date DATE,
CONSTRAINT product_price_history_pk
PRIMARY KEY (product_id, currency_code, effective_from_date)
) PARTITION BY RANGE (effective_from_date) (
PARTITION p0 VALUES less than (DATE'2015-01-02'),
PARTITION p1 VALUES less than (DATE'2015-01-03'),
PARTITION p2 VALUES less than (DATE'2015-01-04')
);
INSERT INTO product_price_history
WITH prices AS (
SELECT 1, 100, 'USD', DATE'2015-01-01', DATE'2015-01-02'
FROM dual UNION ALL
SELECT 1, 60, 'GBP', DATE'2015-01-01', DATE'2015-01-02'
FROM dual UNION ALL
SELECT 1, 110, 'EUR', DATE'2015-01-01', DATE'2015-01-02'
FROM dual UNION ALL
SELECT 1, 101, 'USD', DATE'2015-01-02', DATE'2015-01-03'
FROM dual UNION ALL
SELECT 1, 62, 'GBP', DATE'2015-01-02', DATE'2015-01-03'
FROM dual UNION ALL
SELECT 1, 109, 'EUR', DATE'2015-01-02', DATE'2015-01-03'
FROM dual UNION ALL
SELECT 1, 105, 'USD', DATE'2015-01-03', NULL
FROM dual UNION ALL
SELECT 1, 61, 'GBP', DATE'2015-01-03', NULL
FROM dual UNION ALL
SELECT 1, 107, 'EUR', DATE'2015-01-03', NULL
FROM dual UNION ALL
SELECT 2, 30, 'USD', DATE'2015-01-01', DATE'2015-01-03'
FROM dual UNION ALL
SELECT 2, 33, 'USD', DATE'2015-01-03', NULL
FROM dual UNION ALL
SELECT 3, 100, 'GBP', DATE'2015-01-03', NULL
FROM dual
)
SELECT *
FROM prices;次の文は、表product_price_historyからproduct_idが3の行を削除します。
DELETE FROM product_price_history WHERE product_id = 3;
次のプロシージャは、product_idが2で、effective_to_dateがNULLの行をproduct_price_historyから削除します。
DECLARE
currency product_price_history.currency_code%TYPE;
BEGIN
DELETE product_price_history
WHERE product_id = 2
AND effective_to_date IS NULL
returning currency_code INTO currency;
dbms_output.Put_line(currency);
END;
USD 次の文は、currency_codeがEURの行を表product_price_historyから削除します。
DELETE (SELECT * FROM product_price_history) WHERE currency_code = 'EUR';
次の文は、副問合せを使用してproduct_price_historyから行を削除します。
DELETE product_price_history pp
WHERE (product_id, currency_code, effective_from_date)
IN (SELECT product_id, currency_code, Max(effective_from_date)
FROM product_price_history
GROUP BY product_id, currency_code);次の文は、パーティションを使用してproduct_price_historyから行を削除します。
DELETE product_price_history partition (p1);
次の文は、表の情報を表示します。
SELECT * FROM product_price_history; PRODUCT_ID PRICE CUR EFFECTIVE EFFECTIVE ---------- ---------- --- --------- --------- 1 100 USD 01-JAN-15 02-JAN-15 1 60 GBP 01-JAN-15 02-JAN-15
次の文は、product_price_historyからすべての行を削除します。
DELETE product_price_history;
Live SQL:
「表からのデータの削除」で、Oracle Live SQLに関連する例を参照および実行します。