Oracle删除表

作者：--    发布时间：2019-11-20

在本教程中，将学习如何使用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示例

以下语句创建两个名为brands和cars的新表：

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_1，test_2和test_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语句从数据库中删除表。