Oracle 删除数据库中所有的对象

原创于 2014-02-21 14:40:00 发布

· 658 阅读

0 ·

版权

文章标签：

#oracle

oracle 专栏收录该内容

1 篇文章

订阅专栏

本文提供了一个Oracle PL/SQL函数示例，用于批量删除数据库模式中的所有对象类型，包括表、视图、包、序列、同义词及物化视图等。此函数通过游标遍历用户对象并执行删除操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


create or replace
FUNCTION                DROP_ALL_SCHEMA_OBJECTS RETURN NUMBER AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name
  from user_objects
  where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW')
  order by object_type;
cursor c_get_objects_type is
  select object_type, '"'||object_name||'"' obj_name
  from user_objects
  where object_type in ('TYPE');
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
    end loop;
    for object_rec in c_get_objects_type loop
      begin
        execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
      end;
    end loop;
  end;
  RETURN 0;
END DROP_ALL_SCHEMA_OBJECTS;

select DROP_ALL_SCHEMA_OBJECTS from dual;