Oracle截斷表

在本教學中，您將學習如何使用Oracle TRUNCATE TABLE語句更快更有效地從表中刪除所有數據(也叫截斷表)。

Oracle TRUNCATE TABLE語句簡介

如果要從表中刪除所有數據，可以使用不帶WHERE子句的DELETE語句，如下所示：

DELETE FROM table_name;

對於有少量行記錄的表，DELETE語句做得很好。 但是，當擁有大量行記錄的表時，使用DELETE語句刪除所有數據效率並不高。

Oracle引入了TRUNCATE TABLE語句，用於刪除大表中的所有行。

以下說明了Oracle TRUNCATE TABLE語句的語法：

TRUNCATE TABLE schema_name.table_name
[CASCADE]
[[ PRESERVE | PURGE] MATERIALIZED VIEW LOG ]]
[[ DROP | REUSE]] STORAGE ]

默認情況下，要從表中刪除所有行，請指定要在TRUNCATE TABLE子句中截斷的表的名稱：

TRUNCATE TABLE table_name;

在這種情況下，因為我們沒有明確指定模式名稱，所以Oracle假定從當前的模式中截斷表。
如果表通過外鍵約束與其他表有關系，則需要使用CASCADE子句：

TRUNCATE TABLE table_name
CASCADE;

在這種情況下，TRUNCATE TABLE CASCADE語句刪除table_name表中的所有行，並遞歸地截斷鏈中的關聯表。

請注意，TRUNCATE TABLE CASCADE語句需要使用ON DELETE CASCADE子句定義的外鍵約束才能工作。

通過MATERIALIZED VIEW LOG子句，可以指定在表上定義的物化視圖日誌是否在截斷表時被保留或清除。 默認情況下，物化視圖日誌被保留。

STORAGE子句允許選擇刪除或重新使用由截斷行和關聯索引(如果有的話)釋放的存儲。 默認情況下，存儲被刪除。

請注意，要截斷表，它必須在您自己的模式中，或者必須具有DROP ANY TABLE系統許可權。

Oracle TRUNCATE TABLE示例

下麵我們來看看使用TRUNCATE TABLE語句來刪除表的一些例子。

1. Oracle TRUNCATE TABLE簡單的例子

以下語句創建一個名為customers_copy的表，並從示例資料庫中的customers表複製數據：

CREATE TABLE customers_copy
AS
SELECT
  *
FROM
  customers;

要從customers_copy表中刪除所有行，請使用以下TRUNCATE TABLE語句：

TRUNCATE TABLE customers_copy;

2. Oracle TRUNCATE TABLE CASCADE示例

首先，創建用來演示的兩個表：quotations和quotation_items表：

CREATE TABLE quotations (
    quotation_no NUMERIC GENERATED BY DEFAULT AS IDENTITY,
    customer_id NUMERIC NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    PRIMARY KEY(quotation_no)
);

CREATE TABLE quotation_items (
    quotation_no NUMERIC,
    item_no NUMERIC ,
    product_id NUMERIC NOT NULL,
    qty NUMERIC NOT NULL,
    price NUMERIC(9 , 2 ) NOT NULL,
    PRIMARY KEY (quotation_no , item_no),
    CONSTRAINT fk_quotation FOREIGN KEY (quotation_no)
        REFERENCES quotations
        ON DELETE CASCADE
);

接下來，在這兩個表中插入一些行：

INSERT INTO quotations(customer_id, valid_from, valid_to)
VALUES(100, DATE '2017-09-01', DATE '2017-12-01');

INSERT INTO quotation_items(quotation_no, item_no, product_id, qty, price)
VALUES(1,1,1001,10,90.5);

INSERT INTO quotation_items(quotation_no, item_no, product_id, qty, price)
VALUES(1,2,1002,20,200.5);

INSERT INTO quotation_items(quotation_no, item_no, product_id, qty, price)
VALUES(1,3,1003,30, 150.5);

然後，截斷quotatios表：

TRUNCATE TABLE quotations;

執行語句失敗，Oracle返回以下錯誤：

SQL Error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys

要解決這個問題，可以將CASCADE子句添加到上面的TRUNCATE TABLE語句中：

TRUNCATE TABLE quotations CASCADE;

這個語句不僅從quotations表中刪除數據，而且還從quotation_items表中刪除數據。最後，驗證是否刪除了quotations和quotation_items中的數據：

SELECT
  *
FROM
  quotations;

SELECT
  *
FROM
  quotation_items;

請注意，如果沒有為fk_quotation約束指定ON DELETE CASCADE，則上面的TRUNCATE TABLE CASCADE語句將失敗。

在本教學中，我們學習了如何使用Oracle TRUNCATE TABLE語句更快更有效地從表中刪除所有數據。