我的表里有个SEQUENCE名为SE1，我想用触发器在每天下午5点把SEQUENCE置为初始直（做流水号用），请问这个触发器应该怎么写呢？
---------------------------------------------------------------
方案一比较麻烦: 先用一个函数获取当前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 created
declare
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;