结合网上例子,写了一个小的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;