Oracle 重置sequence2

最新推荐文章于 2022-03-17 09:56:36 发布

iteye_20812

最新推荐文章于 2022-03-17 09:56:36 发布

阅读量173

点赞数

分类专栏： Oracle 文章标签： Oracle

本文链接：https://blog.csdn.net/iteye_20812/article/details/82444266

版权

Oracle 专栏收录该内容

84 篇文章 1 订阅

订阅专栏


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;