本文介绍了一个Oracle PL/SQL过程，该过程用于批量删除数据库中的所有表及其相关外键约束。通过游标和动态SQL实现了自动化操作，极大地提高了数据库清理工作的效率。
create or replace procedure pro_droptable is
cursor cur is select table_name from user_tables;
drop_sql varchar2(1000);
begin
/**删除所有的外键**/
for c in (select 'ALTER TABLE '||TABLE_NAME||' DROP CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
/** 删除表**/
for tbname in cur loop
begin
drop_sql:='drop table '||tbname.table_name;
execute immediate drop_sql;
end;
end loop;
end pro_droptable;
cursor cur is select table_name from user_tables;
drop_sql varchar2(1000);
begin
/**删除所有的外键**/
for c in (select 'ALTER TABLE '||TABLE_NAME||' DROP CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
/** 删除表**/
for tbname in cur loop
begin
drop_sql:='drop table '||tbname.table_name;
execute immediate drop_sql;
end;
end loop;
end pro_droptable;
扫一扫
646
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
