oracle sequence语句重置方介绍

213 阅读 0 评论 141 点赞

我是靠谱客的博主俊逸钢铁侠，最近开发中收集的这篇文章主要介绍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; 