oracle下重置用户的所有序列

/*==============================================================*//* 创建数据类型 *//*==============================================================*/create or replace type varchar2varray is varray(100) of varchar2(40);//*==============================================================*//* 创建用于获取所有序列名，并拼成字符串的函数 *//*==============================================================*/create or replace function f_getAllSequenceName_Strreturn varchar2is cur_result sys_refcursor; seq_name varchar2(100); seq_name_str varchar2(100);begin seq_name_str := ''; open cur_result for select sequence_name from user_sequences; loop fetch cur_result into seq_name; exit when cur_result%notfound; --dbms_output.put_line(seq_name); --将序列名连接起来，中间用|分隔 seq_name_str := seq_name_str || seq_name || '|'; end loop; --dbms_output.put_line(seq_name_str); --删除最后一个字符| seq_name_str := rtrim(seq_name_str,'|'); --dbms_output.put_line(seq_name_str); return seq_name_str;end;//*==============================================================*//* 创建用于分割序列字符串的函数 *//*==============================================================*/create or replace function sf_split_string (substring varchar2) return varchar2varray is len integer := length(substring); lastpos integer := 1 - len; pos integer; num integer; i integer := 1; ret varchar2varray := varchar2varray(null);begin loop pos := instr(f_getAllSequenceName_Str, substring, lastpos + len); if pos > 0 then num := pos - (lastpos + len); else num := length(f_getAllSequenceName_Str) + 1 - (lastpos + len); end if; if i > ret.last then ret.extend; end if; ret(i) := substr(f_getAllSequenceName_Str, lastpos + len, num); exit when pos = 0; lastpos := pos; i := i + 1; end loop; return ret;end;//*==============================================================*//* 创建重置序列的存储过程 *//*==============================================================*/create or replace procedure seq_reset_test(split_str varchar2)as n number(10);tsql varchar2(100);ref_code sys_refcursor;v_seqname varchar2(100);begin open ref_code for select * from table (cast (sf_split_string(split_str) as varchar2varray)); loop fetch ref_code into v_seqname; exit when ref_code%notfound; dbms_output.put_line(v_seqname); tsql := 'select ' || v_seqname || '.nextval from dual'; execute immediate tsql into n; --如果序列本身是初始状态则不进行数值计算 if n <> 1 then n := -(n-1); tsql := 'alter sequence ' || v_seqname || ' increment by ' || n; execute immediate tsql; tsql := 'select ' || v_seqname || '.nextval from dual'; execute immediate tsql into n; tsql := 'alter sequence ' || v_seqname || ' increment by 1'; execute immediate tsql; else tsql := 'alter sequence ' || v_seqname || ' increment by ' || n; execute immediate tsql; end if; end loop;exception when no_data_found then dbms_output.put_line('not found data!'); when others then dbms_output.put_line('unknow exception!'); end seq_reset_test;//*==============================================================*//* 执行存储过程 *//*==============================================================*/execute seq_Reset_test('|')/