Detailed description of how to reset a sequence from a specified number in oracle

Source: Internet
Author: User

Resets the oracle sequence from a specified number.
Copy codeThe Code is as follows:
Declare
N number (10 );
V_startnum number (10): = 10000001; -- start from the number
V_step number (10): = 1; -- step
Tsql varchar2 (200 );
V_seqname varchar2 (200): = 'mip _ JF_SEQUENCE '; -- sequence name
Begin
Execute immediate 'select' | v_seqname | '. nextval from dual' into n;
N: = v_startnum-n-v_step; -- Starting from 10000001
Tsql: = 'alter sequence '| v_seqname | 'crement by' | n;
Execute immediate tsql;
Execute immediate 'select' | v_seqname | '. nextval from dual' into n;
Tsql: = 'alter sequence '| v_seqname | 'crement by' | v_step;
Execute immediate tsql;
End;

It is worthwhile to simply reset the sequence without deleting and recreating the sequence. Generally speaking, sequences are often used in actual development. It is very convenient to use them to generate primary keys, but sometimes we need to reset them to zero, the common method is to delete the file and recreate it.
Next, let's look at another method.:
Copy codeThe Code is as follows:
SQL> create sequence seq_1 increment by 1 start with 1 maxvalue 999999999;
The sequence has been created.
SQL> create or replace procedure seq_reset (v_seqname varchar2)
2 n number (10 );
3 tsql varchar2 (100 );
4 begin
5 execute immediate 'select' | v_seqname | '. nextval from dual' into n;
6 n: =-(n-1 );
7 tsql: = 'alter sequence '| v_seqname | 'crement by' | n;
8 execute immediate tsql;
9 execute immediate 'select' | v_seqname | '. nextval from dual' into n;
10 tsql: = 'alter sequence '| v_seqname | 'crement by 1 ';
11 execute immediate tsql;
12 end seq_reset;
13/
The process has been created.
SQL> select seq_1.nextval from dual;
NEXTVAL
---------
2
SQL>/
NEXTVAL
---------
3
SQL>/
NEXTVAL
---------
4
SQL>/
NEXTVAL
---------
5
SQL> exec seq_reset ('seq _ 1 ');
The PL/SQL process is successfully completed.
SQL> select seq_1.currval from dual;
CURRVAL
---------
1
SQL>

In this way, you can call this process at any time to reset the sequence.
The writing of this stored procedure is too hasty and can be further improved.
Oracle reset sequence (reconstruction mode not deleted)
In Oracle, when auto-incrementing sequence is reset to the initial 1, it is deleted and re-built. This method has many drawbacks. Functions and stored procedures dependent on it will become invalid and need to be re-compiled.
However, there is a clever way to do this without deleting it. You can use the step size parameter to first find the nextval of sequence, remember to change the increment value to the negative value (which goes in turn), and then change it back.
Assume the sequence name to be modified: seq_name
1. select seq_name.nextval from dual; // assume that the expected result is 5656.
2. alter sequence seq_name increment by-5655; // Note:-(n-1)
3. select seq_name.nextval from dual; // run the following command again and set it to 1.
4. alter sequence seq_name increment by 1; // restore
You can write a stored procedure. The following is a complete stored procedure, and then you can call the parameter passing function:
Copy codeThe Code is as follows:
Create or replace procedure seq_reset (v_seqname varchar2) as n number (10 );
Tsql varchar2 (100 );
Begin
Execute immediate 'select' | v_seqname | '. nextval from dual' into n;
N: =-(n-1 );
Tsql: = 'alter sequence '| v_seqname | 'crement by' | n;
Execute immediate tsql;
Execute immediate 'select' | v_seqname | '. nextval from dual' into n;
Tsql: = 'alter sequence '| v_seqname | 'crement by 1 ';
Execute immediate tsql;
End seq_reset;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.