It is worth the easy way to reset a sequence without deleting the Rebuild method.

In general, the sequence in the actual development process is often used in an object, it is very convenient to generate a primary key, but sometimes we need to reset it to zero, usually in the way is deleted and recreated.

Here's another way to look at this:

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) as 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| | ' increment by ' | |  N  8 Execute immediate TSQL; 9 Execute immediate ' SELECT ' | | v_seqname| | '. Nextval from dual ' into N; tsql:= ' alter sequence ' | | v_seqname| | ' increment by 1 '; One by one execute immediate tsql; End Seq_reset; 13/

Procedure 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 has completed successfully.

Sql> select Seq_1.currval from dual;

Currval---------1

Sql>

This can be done by calling this procedure at any time to achieve the purpose of the sequence reset.

This stored procedure is written in a hurry and can be further refined, and is no longer described here.