ORACLE_每日创建序列_使用方法实现

-- 使用参数获取今天的日期以及前一天的日期,首先查询序列,如果无则创建,如果有则删除之前序列
CREATE OR REPLACE FUNCTION "FUNC_SC_GET_SEQ" (name IN string,lastName IN string)
return number
Authid Current_User
IS
SEQ_NOT_FOUND EXCEPTION;
PRAGMA EXCEPTION_INIT(SEQ_NOT_FOUND, -2289);
PRAGMA AUTONOMOUS_TRANSACTION;
result number(12);
select_sql varchar2(1000);
c_type number(2);
V_NUM number;
begin
    select_sql:='select ' || name ||'_SEQ' || '.nextval from dual';
    execute immediate select_sql into result;
    select count(0) into V_NUM from user_sequences  where sequence_name  = lastName||'_SEQ' ;
     if V_NUM > 0 then   
     execute immediate 'DROP sequence '||lastName||'_SEQ';   
    end if;
    return result;
    exception
    when SEQ_NOT_FOUND then
        c_type := FUNC_SC_CREATE_SEQ(name);
        select_sql:='select ' ||name|| '_SEQ'||'.nextval from dual';
        execute immediate select_sql into result;
         select count(0) into V_NUM from user_sequences  where sequence_name  = lastName||'_SEQ' ;
         if V_NUM > 0 then   
         execute immediate 'DROP sequence '||lastName||'_SEQ';   
        end if;
        return  result ;
end  FUNC_SC_GET_SEQ;

CREATE OR REPLACE FUNCTION "FUNC_SC_CREATE__SEQ" (name IN string)
return number
Authid Current_User
IS 
create_sql varchar2(1000); 
begin
    create_sql:='create sequence '||name||'_seq' || ' maxvalue 999 start with 1 increment by 1';
    execute immediate create_sql;
        return 0;
    exception
    when NO_DATA_FOUND then
     create_sql:='create sequence '||name||'_seq' || ' maxvalue 999 start with 1 increment by 1';
     execute immediate create_sql;
             return 1;
end  FUNC_SC_CREATE__SEQ;

select FUNC_SC_GET_SEQ('20200101','20191231') from dual ;
-- 方法调用