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

[xiaoxiao1984]

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;

[小灰狼W]

记录学习

[小灰狼W]

坐个沙发，哈``

[shiyiwan]

1，2都被你抢了

[小灰狼W]

那是，占完沙发才有闲工夫回头

[jiaruimin11]

up

[yubin88]

学习

[zhaoweiting0609]

mark

[zhaoweiting0609]

问一下,用save point,和rollback point可不可以回滚seq?

[-无-为-]

学习了

SELECT min_value, increment_by
INTO v_min_value, v_increment_by
FROM user_sequences
WHERE sequence_name = upper(v_seq_name_in);

[freeupman]

学习了 果然是高手

[雨青]

Mark...