oracle重置seq,oracle sequence语句重置方介绍

在开发过程中，可能会用到oracle sequence语句,本文以oracle sequence语句如何重置进行介绍，需要的朋友可以参考下

Oracle重置sequence语句1

Sql代码

DECLARE

n NUMBER(10 );

tsql VARCHAR2(100 );

p_seqName varchar2(20 );

BEGIN

p_seqName := 'SEQ_RUN_ID';

EXECUTE IMMEDIATE 'SELECT ' || p_seqName || '.NEXTVAL FROM dual ' INTO n;

n := - (n - 1);

tsql := 'alter sequence '|| p_seqName ||' increment by ' || n;

EXECUTE IMMEDIATE tsql;

EXECUTE IMMEDIATE 'SELECT ' || p_seqName || '.NEXTVAL FROM dual ' INTO n;

tsql := 'alter sequence '|| p_seqName ||' increment by 1' ;

EXECUTE IMMEDIATE tsql;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line( SQLERRM);

END;

Oracle重置sequence语句2

Sql代码

CREATE OR REPLACE PROCEDURE RESET_SEQUENCE(p_sSeqName IN VARCHAR2)

IS

n NUMBER(10 );

tsql VARCHAR2(100 );

BEGIN

EXECUTE IMMEDIATE 'SELECT ' || p_sSeqName || '.NEXTVAL FROM dual '

INTO n;

n := - (n - 1);

tsql := 'alter sequence ' || p_sSeqName || ' increment by ' || n;

EXECUTE IMMEDIATE tsql;

EXECUTE IMMEDIATE 'SELECT ' || p_sSeqName || '.NEXTVAL FROM dual '

INTO n;

tsql := 'alter sequence ' || p_sSeqName || ' increment by 1' ;

EXECUTE IMMEDIATE tsql;

EXCEPTION WHEN OTHERS THEN

NULL;

END RESET_SEQUENCE;