create or replace procedure p_isof_exists_sequence(v_tablename in varchar2)
as
v_cnt number(1);
v_sql varchar2(100);
cursor c_seqname
is
--将表名暂替换成序列名填入到变量 v_Seqname,v_Seqname_2,v_Seqname_A,v_Seqname_C,v_Seqname_M
select replace(upper(v_tablename),'TBL_','SEQ_') seq_name from dual
union all
select replace(upper(v_tablename),'TBL_','SQ2_') from dual
union all
select replace(upper(v_tablename),'TBL_','SQ2_')||'_A' from dual
union all
select replace(upper(v_tablename),'TBL_','SQ2_')||'_C' from dual
union all
select replace(upper(v_tablename),'TBL_','SQ2_')||'_M' from dual;
c_row c_seqname%rowtype;
begin
--判断数据库中是否存在序列,count(1)只可能为0 或 1
for c_row in c_seqname
loop
select count(1) into v_cnt from All_Sequences t where t.SEQUENCE_NAME = c_row.seq_name;
--若存在则删除,若不存在则无视
if v_cnt = 1 then
--v_result := 'sequence exists';
v_sql := 'drop sequence '||c_row.seq_name;
Execute IMMEDIATE v_sql;
/* else
v_result := 'sequence not exists';*/
end if;
end loop;
end;
存储过程(参照)
最新推荐文章于 2023-04-12 16:07:13 发布