1 /*==============================================================*/
2 /* 创建数据类型 */
3 /*==============================================================*/
4 create or replace type varchar2varray is varray(100) of varchar2(40);
5 /
6
7
8 /*==============================================================*/
9 /* 创建用于获取所有序列名，并拼成字符串的函数 */
10 /*==============================================================*/
11 create or replace function f_getAllSequenceName_Str
12 return varchar2
13 is
14 cur_result sys_refcursor;
15 seq_name varchar2(100);
16 seq_name_str varchar2(100);
17 begin
18 seq_name_str := '';
19 open cur_result for select sequence_name from user_sequences;
20 loop
21 fetch cur_result into seq_name;
22 exit when cur_result%notfound;
23 --dbms_output.put_line(seq_name);
24 --将序列名连接起来，中间用|分隔
25 seq_name_str := seq_name_str || seq_name || '|';
26 end loop;
27 --dbms_output.put_line(seq_name_str);
28 --删除最后一个字符|
29 seq_name_str := rtrim(seq_name_str,'|');
30 --dbms_output.put_line(seq_name_str);
31 return seq_name_str;
32 end;
33 /
34
35
36 /*==============================================================*/
37 /* 创建用于分割序列字符串的函数 */
38 /*==============================================================*/
39 create or replace function sf_split_string (substring varchar2)
40 return varchar2varray
41 is
42 len integer := length(substring);
43 lastpos integer := 1 - len;
44 pos integer;
45 num integer;
46 i integer := 1;
47 ret varchar2varray := varchar2varray(null);
48 begin
49 loop
50 pos := instr(f_getAllSequenceName_Str, substring, lastpos + len);
51 if pos > 0 then
52 num := pos - (lastpos + len);
53 else
54 num := length(f_getAllSequenceName_Str) + 1 - (lastpos + len);
55 end if;
56
57 if i > ret.last then
58 ret.extend;
59 end if;
60
61 ret(i) := substr(f_getAllSequenceName_Str, lastpos + len, num);
62
63 exit when pos = 0;
64 lastpos := pos;
65 i := i + 1;
66 end loop;
67 return ret;
68 end;
69 /
70
71
72 /*==============================================================*/
73 /* 创建重置序列的存储过程 */
74 /*==============================================================*/
75 create or replace procedure seq_reset_test(split_str varchar2)
76 as
77 n number(10);
78 tsql varchar2(100);
79 ref_code sys_refcursor;
80 v_seqname varchar2(100);
81 begin
82 open ref_code for
83 select * from table (cast (sf_split_string(split_str) as varchar2varray));
84 loop
85 fetch ref_code into v_seqname;
86 exit when ref_code%notfound;
87 dbms_output.put_line(v_seqname);
88 tsql := 'select ' || v_seqname || '.nextval from dual';
89 execute immediate tsql into n;
90 --如果序列本身是初始状态则不进行数值计算
91 if n <> 1 then
92 n := -(n-1);
93 tsql := 'alter sequence ' || v_seqname || ' increment by ' || n;
94 execute immediate tsql;
95 tsql := 'select ' || v_seqname || '.nextval from dual';
96 execute immediate tsql into n;
97 tsql := 'alter sequence ' || v_seqname || ' increment by 1';
98 execute immediate tsql;
99 else
100 tsql := 'alter sequence ' || v_seqname || ' increment by ' || n;
101 execute immediate tsql;
102 end if;
103 end loop;
104 exception
105 when no_data_found then
106 dbms_output.put_line('not found data!');
107 when others then
108 dbms_output.put_line('unknow exception!');
109 end seq_reset_test;
110 /
111
112
113 /*==============================================================*/
114 /* 执行存储过程 */
115 /*==============================================================*/
116 execute seq_Reset_test('|')
117 /