ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Counting Backwards to Reset a Sequence Value[akadia]

Counting Backwards to Reset a Sequence Value[akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-06-18 09:03:04 0 删除 编辑

Consider a sequence named "MY_SEQ" which has been created to generate automatic numbers. Somebody, by mistake, has tried to access the sequence from SQL*Plus and consequently the value has been increased to e.g. 32. Our objective is now to reset the value to 10 so that next created number will have a value of 11.

  • Find out the INCREMENT BY parameter for the sequence with the following query

SELECT increment_by
FROM user_sequences
WHERE sequence_name = 'MY_SEQ' ;

This will return 1. It is not always necessary to run the above query to find out the INCREMENT BY value. You can find it out by looking at the column data for which the sequence is used.

  • Alter the sequence with a negative INCREMENT BY value to count backwards

ALTER SEQUENCE my_seq INCREMENT BY -1;

  • Run the PL/SQL block below to reset the sequence value to a desired number:

DECLARE
temp NUMBER(10);
BEGIN
WHILE(TRUE)
LOOP
SELECT MY_SEQ.NEXTVAL
INTO temp
FROM DUAL;
IF (temp = 10) THEN
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

  • Again reset the INCREMENT BY value to original

ALTER SEQUENCE my_seq INCREMENT BY 1;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/936/viewspace-60596/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    268
  • 访问量
    171182