Oracle刪除表


在本教學中,將學習如何使用Oracle DROP TABLE語句刪除現有的表。

Oracle DROP TABLE語句簡介

要將表移動到回收站或將其從資料庫中完全刪除,請使用DROP TABLE語句:

DROP TABLE schema_name.table_name
[CASCADE CONSTRAINTS | PURGE];

在這個語句中:

  • 首先,指出要在DROP TABLE子句之後刪除的表及其模式。如果不明確指定模式名稱,則該語句假定將從模式中刪除該表。
  • 其次,指定CASCADE CONSTRAINTS子句刪除參照表中主鍵和唯一鍵的所有參照完整性約束。 如果存在這種參照完整性約束,並且不使用此子句,Oracle將返回錯誤並停止刪除表。
  • 第三,如果想刪除表格並且一次釋放與之關聯的空間,指定PURGE子句。 通過使用PURGE子句,Oracle不會將表及其依賴物件放入回收站。

請注意,PURGE子句不允許您回滾或恢復刪除的表。 因此,如果不希望敏感資料出現在回收站中,這很有用。

Oracle DROP TABLE範例

我們來看看使用DROP TABLE語句的一些例子。

1. 基本的Oracle DROP TABLE範例

以下CREATE TABLE語句為演示建立persons表:

CREATE TABLE persons (
    person_id NUMBER,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(person_id)
);

以下範例從資料庫中刪除person表:

DROP TABLE persons;

2. Oracle DROP TABLE CASCADE CONSTRAINTS範例

以下語句建立兩個名為brandscars的新表:

CREATE TABLE brands(
    brand_id NUMBER PRIMARY KEY,
    brand_name varchar2(50)
);

CREATE TABLE cars(
    car_id NUMBER PRIMARY KEY,
    make VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year NUMBER NOT NULL,
    plate_number VARCHAR(25),
    brand_id NUMBER NOT NULL,

    CONSTRAINT fk_brand 
    FOREIGN KEY (brand_id) 
    REFERENCES brands(brand_id) ON DELETE CASCADE
);

在這些表中,每個品牌有一個或更多的汽車,而每輛汽車只有一個品牌。

以下語句嘗試刪除brands表:

DROP TABLE brands;

Oracle發出以下錯誤:

ORA-02449: unique/primary keys in table referenced by foreign keys

這是因為brands表的主鍵當前由cars表中的brand_id列參照。

以下語句返回cars表的所有外來鍵約束:

SELECT
    a.table_name,
    a.column_name,
    a.constraint_name,
    c.owner,
    c.r_owner,
    c_pk.table_name r_table_name,
    c_pk.constraint_name r_pk
FROM
    all_cons_columns a
JOIN all_constraints c ON
    a.owner = c.owner
    AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON
    c.r_owner = c_pk.owner
    AND c.r_constraint_name = c_pk.constraint_name
WHERE
    c.constraint_type = 'R'
    AND a.table_name = 'CARS';

要刪除brands表,必須使用CASCADE CONSTRAINTS子句,如下所示:

DROP TABLE brands CASCADE CONSTRAINTS;

這個語句不僅刪除了brands表,而且還刪除了cars表中的外來鍵約束fk_brand

如果再次執行語句以獲取cars表中的外來鍵約束,則不會看到任何返回的行。

Oracle DROP TABLE PURGE範例

以下語句使用PURGE子句來刪除cars表:

DROP TABLE cars purge;

一次刪除多個表

Oracle不提供直接刪除多個表的方法。 但是,可以使用以下PL/SQL塊來執行此操作:

BEGIN
  FOR rec IN
    (
      SELECT
        table_name
      FROM
        all_tables
      WHERE
        table_name LIKE 'TEST_%'
    )
  LOOP
    EXECUTE immediate 'DROP TABLE  '||rec.table_name || ' CASCADE CONSTRAINTS'
  END LOOP;
END;
/

PL/SQL塊將刪除名稱以TEST_開頭的所有表。

要測試此程式碼,可以先建立三個表:test_1test_2test_3,如下所示:

CREATE TABLE test_1(c1 VARCHAR2(50));
CREATE TABLE test_2(c1 VARCHAR2(50));
CREATE TABLE test_3(c1 VARCHAR2(50));

然後,執行上面的PL/SQL塊。應該看可以看到,上面建立的三個表均被刪除了。

在本教學中,您已學習如何使用Oracle DROP TABLE語句從資料庫中刪除表。