建立
create sequence seq_1 increment by 1 start with 1 maxvalue 999999999;
建立重置存储过程
SQL> create or replace procedure seq_reset(v_seqname varchar2) as
2 n number(10);
3 tsql varchar2(100);
4 begin
5 execute immediate 'select '||v_seqname||'.nextval from dual' into n;
6 n:=-(n-1);
7 tsql:='alter sequence '||v_seqname||' increment by '|| n;
8 execute immediate tsql;
9 execute immediate 'select '||v_seqname||'.nextval from dual' into n;
10 tsql:='alter sequence '||v_seqname||' increment by 1';
11 execute immediate tsql;
12 end seq_reset;
13 /
JOB
declare
job1 integer;
begin
sys.dbms_job.submit(job1 , 'PROCEDURE__NAME(参数);', to_date('20050603 01:00:00', 'yyyymmdd hh24:mi:ss'), -- 计划的初次执行日期,请根据具体情况设定
'to_date(to_char(sysdate+1,''yyyymmdd'')||''01:00:00'',''yyyymmdd hh24:mi:ss'')');
commit;
end;