本文介绍了一种使用 PL/SQL 过程来管理 Oracle 数据库中的表和序列的方法。通过两个示例过程，我们可以安全地删除指定名称的表或序列，前提是它们确实存在于当前用户的模式中。
1、创建表时，先判断表是否存在，如果存在则删除
create or replace procedure pr_droptable(
v_tablename in varchar2
)
as
v_count int;
begin
select count(1) into v_count from user_tables where table_name = upper(v_tablename);
if v_count > 0 then
execute immediate 'drop table ' || v_tablename;
end if;
end;
2、创建序列时，先判断序列是否存在，如果存在则删除
create or replace procedure pr_dropseq(
v_name in varchar2
)
as
v_count int;
begin
select count(1) into v_count from user_sequences a where sequence_name = upper(v_name);
if v_count > 0 then
execute immediate 'drop sequence ' || v_name;
end if;
end;
create or replace procedure pr_droptable(
v_tablename in varchar2
)
as
v_count int;
begin
select count(1) into v_count from user_tables where table_name = upper(v_tablename);
if v_count > 0 then
execute immediate 'drop table ' || v_tablename;
end if;
end;
2、创建序列时，先判断序列是否存在，如果存在则删除
create or replace procedure pr_dropseq(
v_name in varchar2
)
as
v_count int;
begin
select count(1) into v_count from user_sequences a where sequence_name = upper(v_name);
if v_count > 0 then
execute immediate 'drop sequence ' || v_name;
end if;
end;
扫一扫
849
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
