关于reset sequence

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) 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