sequence在工作中使用比较频繁,对于Insert中插入的值,如果只需要它能够自动递增,这个时候sequence就派上用场了。 但是既然sequence的值需要递增就有可能会达到最大值。比如sequence从1开始递增,递增幅度为1,最大值为100,那么很快就会达到最大值。我们可以指定sequence的值。这个时候可以使用删除,重建的方式,但是这种方式相对来说影响范围较大,相关的存储过程,函数,同义词都会失效。可以通过更为灵活的方式来reset sequence. 一种方式相对直接,简洁。就是修改sequence的属性,把increment的值调高点,这样每次递增的幅度就大,然后循环递增,知道递增的值接近目标值,然后修改Increment的值为1. pl/sql的实现如下,比如我们要修改account_1sq的值,把它修改为10001000,就可以这样来修改。
alter sequence ACCOUNT_1SQ increment by 69 nocache;
alter sequence ACCOUNT_1SQ increment by 1 nocache;
declare
LastValue integer;
begin
loop
select ACCOUNT_1SQ.currval into LastValue from dual;
exit when LastValue >= 10001000 - 1;
select ACCOUNT_1SQ.nextval into LastValue from dual;
end loop;
end;
/
alter sequence ACCOUNT_1SQ increment by 1 cache 20;
当然了这种方式还是存在不少的缺点。 一来是耦合度较高。在代码里面嵌入了太多的细节。pl/sql脚本从头到尾都是account_1sq相关。 reset的值和递增的幅度也得考虑周到。要不就可能出问题,导致reset的值达不到要求。 如果要修改序列的一些属性,在最后需要恢复。所以我们得时刻记得sequence的细节信息。 可以使用下面的改进脚本来修复上面的不足。这个脚本需要几个参数。对象类型(比如sequence或者table),对象名称(sequence的名称),sequence的值(需要修改的值)
WHENEVER SQLERROR EXIT 5
DEFINE OBJTYPE="&1"
DEFINE OBJNAME="&2"
DEFINE SEQVALUE="&3"
SET ECHO OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON
SET LINESIZE 100
SET PAGESIZE 0
SET TERMOUT ON
SET VERIFY OFF
DECLARE CURSOR C1 IS
SELECT
DISTINCT SQ.SEQUENCE_NAME
FROM
USER_SEQUENCES SQ,
USER_COL_COMMENTS TB
WHERE
TB.COMMENTS = SQ.SEQUENCE_NAME
AND UPPER('&OBJTYPE') = 'TABLE'
AND TB.TABLE_NAME = UPPER('&OBJNAME')
UNION
SELECT
UPPER('&OBJNAME') SEQUENCE_NAME
FROM
DUAL
WHERE
UPPER('&OBJTYPE') = 'SEQUENCE' ;
sql_tab_name USER_COL_COMMENTS.TABLE_NAME%TYPE ;
sql_col_name USER_COL_COMMENTS.COLUMN_NAME%TYPE ;
sql_seq_name USER_SEQUENCES.SEQUENCE_NAME%TYPE ;
old_val NUMBER := 0;
new_val NUMBER := 0;
max_val NUMBER := 0;
seq_max_val NUMBER := 0;
old_min_val NUMBER := 0;
old_inc NUMBER := 0;
new_inc NUMBER := 0;
seq_cur INTEGER ;
seq_fld INTEGER ;
seq_val INTEGER ;
seq_cur_rows INTEGER ;
seq_fld_rows INTEGER ;
seq_val_rows INTEGER ;
found_seq_ind CHAR(1) := 'Y' ;
old_new_diff INTEGER ;
seq_cycle CHAR(1) := 'N' ;
BEGIN
DBMS_OUTPUT.ENABLE(2000000);
OPEN C1;
LOOP
FETCH C1 INTO
sql_seq_name ;
EXIT WHEN C1%NOTFOUND ;
BEGIN
found_seq_ind := 'Y' ;
SELECT
MIN_VALUE,
MAX_VALUE,
INCREMENT_BY,
CYCLE_FLAG
INTO
old_min_val,
seq_max_val,
old_inc,
seq_cycle
FROM
USER_SEQUENCES
WHERE
SEQUENCE_NAME = sql_seq_name ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Sequence: '||sql_seq_name||' is not exists.');
found_seq_ind := 'N' ;
WHEN OTHERS THEN
EXIT ;
END ;
------------------------------------------------------------
--Get Max val from all tables that related to this sequence.
------------------------------------------------------------
IF ( found_seq_ind = 'Y' )
THEN
IF ( UPPER('&OBJTYPE') = 'TABLE' )
THEN
seq_fld := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE (seq_fld, 'SELECT COLUMN_NAME,TABLE_NAME FROM USER_COL_COMMENTS '||
' WHERE COMMENTS '||' = '||':sql1 ',DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(seq_fld, 'sql1' ,sql_seq_name);
DBMS_SQL.DEFINE_COLUMN (seq_fld, 1, sql_col_name, 30 );
DBMS_SQL.DEFINE_COLUMN (seq_fld, 2, sql_tab_name, 30 );
seq_fld_rows := DBMS_SQL.EXECUTE (seq_fld);
max_val := old_min_val ;
LOOP
IF DBMS_SQL.FETCH_ROWS (seq_fld) > 0
THEN
DBMS_SQL.COLUMN_VALUE ( seq_fld, 1, sql_col_name ) ;
DBMS_SQL.COLUMN_VALUE ( seq_fld, 2, sql_tab_name ) ;
seq_val := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE (seq_val, 'SELECT GREATEST(NVL(MAX('||sql_col_name||'),0),'||max_val||') FROM '||
sql_tab_name|| ' WHERE '||sql_col_name||
' NOT IN (999999999,888888888) ',DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN (seq_val, 1, max_val);
seq_val_rows := DBMS_SQL.EXECUTE (seq_val);
IF DBMS_SQL.FETCH_ROWS (seq_val) > 0
THEN
DBMS_SQL.COLUMN_VALUE ( seq_val, 1, max_val ) ;
END IF ;
DBMS_SQL.CLOSE_CURSOR (seq_val) ;
ELSE
DBMS_SQL.CLOSE_CURSOR (seq_fld) ;
EXIT ;
END IF ;
END LOOP ;
ELSE
max_val := &SEQVALUE ;
END IF ;
------------------------------------------------------------
-- Foreach sequence get the nextvalue. --
------------------------------------------------------------
seq_cur := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE (seq_cur, 'SELECT '||sql_seq_name||'.NEXTVAL FROM DUAL',DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN (seq_cur, 1, old_val );
seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);
IF DBMS_SQL.FETCH_ROWS (seq_cur) > 0
THEN
DBMS_SQL.COLUMN_VALUE ( seq_cur, 1, old_val );
DBMS_SQL.CLOSE_CURSOR (seq_cur);
ELSE
DBMS_SQL.CLOSE_CURSOR (seq_cur);
END IF ;
------------------------------------------------------------
-- Select sequences differences. --
------------------------------------------------------------
IF ( max_val > old_val )
THEN
old_new_diff := max_val - old_val ;
ELSE
old_new_diff := max_val - ( old_val - old_min_val );
END IF;
------------------------------------------------------------
-- Foreach sequence set the nextvalue with the new increment.
------------------------------------------------------------
IF ( old_new_diff <> 0 ) AND ( (old_new_diff + old_val) < seq_max_val )
THEN
---------------------------------------------
--setting NOCYCLE for the sequence allow us
--to set sequence on MIN_VAL.
---------------------------------------------
IF ( seq_cycle = 'Y' )
THEN
seq_cur := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE (seq_cur, 'ALTER SEQUENCE '||sql_seq_name||' NOCYCLE ',DBMS_SQL.V7);
seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);
DBMS_SQL.CLOSE_CURSOR (seq_cur);
END IF;
---------------------------------------------
--set the nextvalue with the new increment.
---------------------------------------------
seq_cur := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE (seq_cur, 'ALTER SEQUENCE '||sql_seq_name||' INCREMENT BY '||old_new_diff||' ',DBMS_SQL.V7);
seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);
DBMS_SQL.CLOSE_CURSOR (seq_cur);
---------------------------------------------
--Get the nextvalue with the new increment.
---------------------------------------------
seq_cur := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE (seq_cur, 'SELECT '||sql_seq_name||'.NEXTVAL FROM DUAL',DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN (seq_cur, 1, new_val );
seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);
IF DBMS_SQL.FETCH_ROWS (seq_cur) > 0
THEN
DBMS_SQL.COLUMN_VALUE ( seq_cur, 1, new_val );
DBMS_SQL.CLOSE_CURSOR (seq_cur);
ELSE
DBMS_SQL.CLOSE_CURSOR (seq_cur);
END IF ;
DBMS_OUTPUT.PUT_LINE('Resetting sequence: '||sql_seq_name||' to: '||new_val);
--------------------------------------------------
--set the nextvalue with the original increment.
--------------------------------------------------
seq_cur := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE (seq_cur, 'ALTER SEQUENCE '||sql_seq_name||' INCREMENT BY '||old_inc||' ',DBMS_SQL.V7);
seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);
DBMS_SQL.CLOSE_CURSOR (seq_cur);
---------------------------------------------
--setting original CYCLE for the sequence --
---------------------------------------------
IF ( seq_cycle = 'Y' )
THEN
seq_cur := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE (seq_cur, 'ALTER SEQUENCE '||sql_seq_name||' CYCLE ',DBMS_SQL.V7);
seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);
DBMS_SQL.CLOSE_CURSOR (seq_cur);
END IF;
END IF ;
END IF;
END LOOP ;
CLOSE C1 ;
END;
/
执行脚本的时候,比如脚本名称为reset_seq.sql 可以这样执行。 @reset_seq.sql sequence account_1sq 10000010