Select to_char(Sysdate,'yyyy') || lpad(test_seq.Nextval,3) into :new.ID From dual;
错了,改正 Select to_char(Sysdate,'yyyy') || lpad(test_seq.Nextval,3,'0') From dual;
樓上的寫法不完全應該這樣 ls_id varchar(3); ls_newid varchar(7);select Max(substr(id, -3, 3)) into ls_id from tableA where substr(id, 1, 4) = to_char(sysdate, 'yyyy'); if ls_id = '' then ls_newid := to_char(sysdate, 'yyyy') || '001'; else ls_newid := to_char(sysdate, 'yyyy') || to_char(to_number(ls_id) + 1, '099'); end if;
--创建序列 create sequence TESTSEQ minvalue 1 maxvalue 999 start with 1 increment by 1 cache 20 order;--创建插入触发器 create or replace trigger trigger_name before insert on tab_name for each row begin select to_char(sysdate,'YYYY')||lpad(to_char(testseq.nextval),3,'0') into :new.id from dual; end;
原来不用序列也可以 呵呵多谢诸位 结账了~ 经过修改调试,最后的结果供后来人参考: CREATE OR REPLACE TRIGGER "TEST_TRIGGER" BEFORE INSERT ON "TEST1" FOR EACH ROW declare temp_id varchar2(3); begin select Max(substr(id, 5, 3)) into temp_id from test2 where substr(id,1,4) = to_char(sysdate, 'yyyy'); if nvl(temp_id,'0')='0' then :new.id := to_char(sysdate, 'yyyy') || '001'; else :new.id := to_char(sysdate, 'yyyy') || lpad(to_number(temp_id) + 1,3,'0'); end if; end;
Select to_char(Sysdate,'yyyy') || lpad(test_seq.Nextval,3,'0') From dual;
ls_id varchar(3);
ls_newid varchar(7);select Max(substr(id, -3, 3)) into ls_id from tableA where substr(id, 1, 4) = to_char(sysdate, 'yyyy');
if ls_id = '' then
ls_newid := to_char(sysdate, 'yyyy') || '001';
else
ls_newid := to_char(sysdate, 'yyyy') || to_char(to_number(ls_id) + 1, '099');
end if;
create sequence TESTSEQ
minvalue 1
maxvalue 999
start with 1
increment by 1
cache 20
order;--创建插入触发器
create or replace trigger trigger_name
before insert on tab_name for each row
begin
select to_char(sysdate,'YYYY')||lpad(to_char(testseq.nextval),3,'0') into :new.id from dual;
end;
经过修改调试,最后的结果供后来人参考:
CREATE OR REPLACE TRIGGER "TEST_TRIGGER" BEFORE INSERT
ON "TEST1"
FOR EACH ROW
declare temp_id varchar2(3);
begin
select Max(substr(id, 5, 3)) into temp_id from test2 where substr(id,1,4) = to_char(sysdate, 'yyyy');
if nvl(temp_id,'0')='0' then
:new.id := to_char(sysdate, 'yyyy') || '001';
else
:new.id := to_char(sysdate, 'yyyy') || lpad(to_number(temp_id) + 1,3,'0');
end if;
end;