存储过程重置SEQUENCE值从新开始。

CREATE OR REPLACE PROCEDURE RESET_SEQUENCE(
    v_SeqName     IN  VARCHAR2,
    v_sqlcode     OUT NUMBER,
    v_sqlerrm     OUT VARCHAR2)
IS
    Num           NUMBER(10);
    SQL_STR     VARCHAR2(100);
BEGIN
    v_sqlcode := 0;
    v_sqlerrm := 'successful';

    EXECUTE IMMEDIATE 'SELECT ' || v_SeqName || '.NEXTVAL FROM dual ' INTO Num;
    Num := - (Num);

    SQL_STR := 'alter sequence '|| v_SeqName ||' increment by ' || Num ;
    EXECUTE IMMEDIATE SQL_STR;

    EXECUTE IMMEDIATE 'SELECT ' || v_SeqName || '.NEXTVAL FROM dual ' INTO Num;
    SQL_STR := 'alter sequence '|| v_SeqName ||' increment by 1';
    EXECUTE IMMEDIATE SQL_STR;

EXCEPTION
  WHEN OTHERS THEN
    v_sqlcode := SQLCODE;
    v_sqlerrm := SQLERRM;
END;

注意空格，影响调用失败。

转载于:https://www.cnblogs.com/sherlockhomles/p/4661252.html