存储过程：修改当前序列值_修改序列值

在Oracle中，序列(SEQUENCE) 是可以产生一组等间隔的数值的一种生成器。常用来生成表的主键或者其他有规律或自增/减的字段值。

当前项目是用来生成表的主键，由于某些原因，当前的某些序列值小于了对应表的id值，导致继续使用序列生成id，会造成id重复。所以需要修改当前的序列值，使其大于对应表id。

修改序列值的思路，可分解为如下：

1. oracle中，当前序列值无法直接修改

2. 通过查询当前序列的方式，可以使当前序列值递增一次
   select 序列名.nextval from dual;

3. 修改当前序列值，可以通过修改 序列递增值 ，然后再 查询一次当前序列值使其递增一次，到达修改的目的
   alter sequence 序列名 increment by 5; //修改当前序列递增值
   select 序列名.nextval from dual; //查询序列，使其自增

   当前递增值 改为 5，然后再查询一次，即可以让当前序列值增加5。但注意最后要将递增值改回来
   1

4. 也可以通过 循环查询 当前序列值，每循环一次，序列就递增一次。

note：

dual是一张只有一行一列的oracle虚拟表，也叫伪表，一般用于补充语句完整性。
eg：
查询当前时间，可以通过 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 得到。
但执行sql的时候，必须满足sql语法定义，所以， select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; 就可以补充完整语法。
1
2
3
4

针对当前情况，处理逻辑是：

1. 遍历所有表，查询每张表的最大id、序列、序列递增值；
2. 如果当前表 id 小于序列值，则进行修改序列值，否则跳过；
3. 修改序列值：首先判断 id 与序列的差值，然后将 序列差值+序列递增值 作为新的 序列递增值；
4. 执行一次查询序列，使序列自增一次。此时序列递增值已经修改，所以自增后，序列已经大于了 id 值，可以正常自增生成id了；
5. 改回自增值为原来的值。

完整的案例如下：

declare
v_tablename VARCHAR2(64); -- 当前表名
v_maxId number;		-- 当前表 最大id
v_sql VARCHAR2(1024);   -- 查最大id sql
v_sql_updateSeq VARCHAR2(1024);  -- 更新递增值 sql
v_sql_currentSeq VARCHAR2(1024);  -- 查当前表 序列值、递增值
v_sql_addSeq VARCHAR2(1024);  -- 更新当前序列值
v_count number;  -- 判断表是否有id字段的临时标识
v_current_seq number;  -- 当前表的序列值
v_current_increment number;  -- 当前表的递增值
v_temp_add number;  -- 当前表的修改后递增值
v_hasSeq number;  -- 是否存在该序列表的标识 >0 为有
v_tempData number;  -- 临时存放，只是为了select动态sql能够顺利执行，并不拿来使用

cursor tablename_cur is select table_name from user_tables;
rowtypes user_tables%rowtype;

BEGIN
	for rowtypes in tablename_cur loop
	v_tablename := rowtypes.table_name;
	
	select count(1) into v_count from cols where table_name = v_tablename and column_name = 'ID';
	if v_count > 0 then 
		-- 查当前表最大id
		v_sql := 'select max(ID) from '||v_tablename;
		execute immediate v_sql into v_maxId;
		exit when tablename_cur%notfound;

		if v_maxId is not null then 

			-- 递增当前序列
			v_sql_addSeq := 'select '||v_tablename||'_SEQ.nextval from dual';

			-- 查当前表序列值、递增值

			select count(1) into v_hasSeq from user_sequences where sequence_name = v_tablename||'_SEQ';

			if v_hasSeq > 0 then 

			select last_number into v_current_seq from user_sequences where sequence_name = v_tablename||'_SEQ';
			select increment_by into v_current_increment from user_sequences where sequence_name = v_tablename||'_SEQ';

				-- 判断当前表序列值是否小于id,是则修改
				if v_maxId > v_current_seq then 
					-- 计算新递增值
					v_temp_add := v_maxId - v_current_seq + v_current_increment;

dbms_output.put_line(v_tablename||'--'||v_maxId||'--'||v_current_seq||'--'||v_current_increment||'--'||v_temp_add||'             '||v_sql_addSeq);

					-- 修改递增值
					v_sql_updateSeq := 'alter sequence '||v_tablename||'_SEQ increment by '||v_temp_add;
					execute immediate v_sql_updateSeq;

					-- 再查询一次当前序列，修改序列值 
					execute immediate v_sql_addSeq into v_tempData;

					-- 将递增值恢复
					v_sql_updateSeq := 'alter sequence '||v_tablename||'_SEQ increment by '||v_current_increment;
					execute immediate v_sql_updateSeq;

			select last_number into v_current_seq from user_sequences where sequence_name = v_tablename||'_SEQ';
			select increment_by into v_current_increment from user_sequences where sequence_name = v_tablename||'_SEQ';


dbms_output.put_line(v_tablename||'--'||v_maxId||'--'||v_current_seq||'--'||v_current_increment||'--'||v_temp_add);

				end if;

			end if;

		end if;
		
		
			
	end if;
	end loop;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

declare 可直接作为sql执行
create or replace procedure 是编写存储过程的方法，编写完保存后，还需要进行调用