问题如 http://www.iteye.com/wiki/problem/34022 知道上 所说
1 先建立序列
create sequence testseq increment by 1 start with 1 maxvalue 999;
2 采用Oracle 的函数
select to_char(sysdate,'yyyymmdd')||lpad(testseq.nextval,3,'0') from dual
3 写存储过程 让序列归零
create or replace procedure houseNoProce
as
n number(10);
tsql varchar2(100);
Begin
select testseq.nextval into n from dual;
n:=-(n-1);
tsql:='alter sequence testseq increment by'|| n;
execute immediate tsql;
select testseq.nextval into n from dual;
tsql:='alter sequence testseq increment by 1';
execute immediate tsql;
end;
4 建立 job
variable housenojob number;
BEGIN
DBMS_JOB.