方案一比较麻烦: 先用一个函数获取当前id最大数,把数以参形式传递到过程中。 create function get_num return number as num number; begin select to_number(id) into num from table_name; return num; end; / create or replace procedure pro(p_num in number) is str varchar2(50); num number; begin str:='alter sequence idno increment by -'||(p_num-1); --p_num是获取当前序列最大值 execute immediate str; select idno.nextval into num from dual; str:='alter sequence idno increment by 1; execute immediate str; end; /Procedure createddeclare jobno number; begin dbms_job.submit(jobno,'pro(get_num);',sysdate,'sysdate+17/24+1'); commit; end; /
方案二: create procedure pro as str varchar2(50); begin str:='drop sequence idno'; execute immediate str; str:='CREATE SEQUENCE idno INCREMENT BY 1 START WITH 1 MAXVALUE 100 MINVALUE 1 NOCACHE NOORDER'; execute immediate str; end; / declare jobno number; begin dbms_job.submit(jobno,'pro;',sysdate,'sysdate+17/24+1'); commit; end; /注意:以上方法都要有权限: grant create sequence to user; grant drop sequence to user; grant alter sequence to user;
begin
dbms_job.submit(:jobnum,'pro;',trunc(sysdate)+17/24,'trunc(sysdate)+17/24+1');
submit;
end;
还有,SEQUENCE怎么把它置为初始直呢?
create function get_num
return number
as
num number;
begin
select to_number(id) into num from table_name;
return num;
end;
/
create or replace procedure pro(p_num in number)
is
str varchar2(50);
num number;
begin
str:='alter sequence idno increment by -'||(p_num-1); --p_num是获取当前序列最大值
execute immediate str;
select idno.nextval into num from dual;
str:='alter sequence idno increment by 1;
execute immediate str;
end;
/Procedure createddeclare
jobno number;
begin
dbms_job.submit(jobno,'pro(get_num);',sysdate,'sysdate+17/24+1');
commit;
end;
/
方案二:
create procedure pro
as
str varchar2(50);
begin
str:='drop sequence idno';
execute immediate str;
str:='CREATE SEQUENCE idno INCREMENT BY 1 START WITH 1
MAXVALUE 100 MINVALUE 1
NOCACHE NOORDER';
execute immediate str;
end;
/
declare
jobno number;
begin
dbms_job.submit(jobno,'pro;',sysdate,'sysdate+17/24+1');
commit;
end;
/注意:以上方法都要有权限:
grant create sequence to user;
grant drop sequence to user;
grant alter sequence to user;