改变Oracle Sequence的NEXTVAL

haklilanqt

结合网上例子，写了一个小的Procedure，来实现Upgrade 序列的NEXTVAL
CREATE OR REPLACE PROCEDURE proc_upgradesequence (v_seqname VARCHAR2, -- 序列的名称
v_newnum NUMBER) IS -- 需要的NEXTVAL
v_error        VARCHAR2 (1000);
ldebug         VARCHAR2 (200);
v_currval      NUMBER;
BEGIN
ldebug := '1. Get sequnce current value';
EXECUTE IMMEDIATE 'select ' || v_seqname || '.nextval from dual' INTO v_currval;
ldebug := '2. Alter this sequence nocache';
EXECUTE IMMEDIATE 'alter sequence ' || v_seqname || ' nocache';
ldebug := '3. Alter this sequence current value';
EXECUTE IMMEDIATE 'alter SEQUENCE ' || v_seqname || ' increment by ' || TO_CHAR (v_newnum - v_currval - 1) || ' nocache';
ldebug := '4. Get this sequence next value';
EXECUTE IMMEDIATE 'select ' || v_seqname || '.nextval from dual' INTO v_currval;
ldebug := '5. Recover this original sequence increment step';
EXECUTE IMMEDIATE 'alter SEQUENCE ' || v_seqname || ' increment by 1 nocache';
EXCEPTION
WHEN OTHERS THEN
v_error := SQLERRM;
DBMS_OUTPUT.put_line(v_error);
END;