分享一个简单回滚seq的过程


CREATE OR REPLACE PROCEDURE reset_sequence(v_seq_name_in   IN VARCHAR2,
v_init_value_in IN NUMBER) IS
v_seq_curr_value NUMBER;
v_increment_by   NUMBER;
v_min_value      NUMBER;
v_last_value     NUMBER;
BEGIN
-- get seq min value and increment
SELECT min_value, increment_by
INTO v_min_value, v_increment_by
FROM user_sequences
WHERE sequence_name = upper(v_seq_name_in);
-- get seq current value
EXECUTE IMMEDIATE 'select ' || v_seq_name_in || '.nextval from dual'
INTO v_seq_curr_value;
-- rollback seq
IF (v_init_value_in > v_min_value)
THEN
v_seq_curr_value := v_init_value_in - v_seq_curr_value - 1;
ELSIF (v_init_value_in = v_min_value)
THEN
v_seq_curr_value := v_init_value_in - v_seq_curr_value;
ELSE
v_seq_curr_value := v_min_value - v_seq_curr_value;
END IF;
EXECUTE IMMEDIATE 'alter sequence ' || v_seq_name_in || ' increment by ' ||
v_seq_curr_value || ' nocache';
EXECUTE IMMEDIATE 'select ' || v_seq_name_in || '.nextval from dual'
INTO v_seq_curr_value;
-- check if it is less than min value
LOOP
EXECUTE IMMEDIATE 'select ' || v_seq_name_in || '.currval from dual'
INTO v_last_value;
EXIT WHEN v_last_value >= v_init_value_in - v_increment_by;
EXECUTE IMMEDIATE 'select ' || v_seq_name_in || '.nextval from dual'
INTO v_last_value;
END LOOP;
EXECUTE IMMEDIATE 'alter sequence ' || v_seq_name_in || ' increment by ' ||
v_increment_by || ' cache 20';
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END reset_sequence;