首发微信公众号: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_pkFROM all_cons_columns aJOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_nameJOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_nameWHERE 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 报错记录,记得仔细检查下是不是哪个语句分号“;”少了一个!