不同模式下洗掉Oracle資料表的三個實體-有解無憂

首發微信公眾號：SQL資料庫運維

原文鏈接：https://mp.weixin.qq.com/s?__biz=MzI1NTQyNzg3MQ==&mid=2247485212&idx=1&sn=450e9e94fa709b5eeff0de371c62072b&chksm=ea37536cdd40da7a94e165ce4b4c6e70fb1360d51bed4b3566eee438b587fa231315d0a5a5b3&token=1491694448&lang=zh_CN#rd

之前總結了ALTER TABLE的五種用法：Oracle中ALTER TABLE的五種用法（點擊文字可跳轉），現在以實體的方式詳細講解下Oracle DROP TABLE陳述句洗掉表，

語法簡介

要將表移動到回收站或將其從資料庫中完全洗掉，可以使用DROP TABLE陳述句：

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

在這個陳述句中：

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

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

1. 基本的Oracle DROP TABLE示例

首先創建一個persons測驗表

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

其次，洗掉這個persons測驗表

DROP TABLE persons;

2. Oracle DROP TABLE CASCADE CONSTRAINTS示例

創建兩個名為brands和cars的新表：

--表1：汽車品牌
CREATE TABLE brands(
    brand_id NUMBER PRIMARY KEY,
    brand_name varchar2(50)
);
--表2：汽車
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提示以下錯誤：

這是因為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表中的外鍵約束，則不會看到任何回傳的行，

3.Oracle DROP TABLE PURGE示例

以下陳述句使用PURGE子句來洗掉cars表：

DROP TABLE cars purge;

擴展知識：批量洗掉多個資料表

Oracle不提供直接洗掉多個表的方法，但是，可以使用以下PL/SQL塊來執行此操作：

下面來測驗下，新建測驗表：

--新建測驗表
CREATE TABLE test_1(c1 VARCHAR2(50));
CREATE TABLE test_2(c1 VARCHAR2(50));
CREATE TABLE test_3(c1 VARCHAR2(50));

使用PL/SQL塊洗掉名稱以TEST_開頭的所有表，

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代碼

注意：如果在PLSQL Developer工具內執行以上陳述句時，出現ORA-06550 PLS-00103 報錯記錄，記得仔細檢查下是不是哪個陳述句分號“；”少了一個！

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/547728.html

標籤：其他

上一篇：MySQL基礎：約束

下一篇：SQL之連表查詢