--1.先创建序列 scott@YPCOST> create sequence orderNo_seq start with 100 increment by 1 maxvalue 999;序列已创建。scott@YPCOST> create table test(id number,name varchar2(20));表已创建。--2、再加触发器 scott@YPCOST> ed 已写入 file afiedt.buf 1 create or replace trigger insert_tri 2 before insert on test 3 for each row 4 declare 5 id number; 6 begin 7 select orderNo_seq.nextval into id from dual; 8 :new.id:=to_char(sysdate,'yyyymmdd')||id; 9* end; scott@YPCOST> /触发器已创建scott@YPCOST> insert into test(name) values('tom');已创建 1 行。scott@YPCOST> commit;提交完成。scott@YPCOST> select * from test;ID NAME -------------------- -------------------- 20110110101 tom
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') || SEQ_A_NAME.NEXTVAL SEQ FROM DUAL;是这个效果不?-- Create sequence create sequence SEQ_A_NAME minvalue 0 maxvalue 999999999999999 start with 1 increment by 1 cache 20;
序列也可以,触发器也可以,看你怎么用了--创建表 create table IDENTITY ( IDENTITY VARCHAR2(12) not null, KEY VARCHAR2(10) );--创建触发器 create or replace trigger identity_trigger before insert on identity for each row declare -- local variables here identity_before varchar2(4); cnt number; begin select count(*) into cnt from identity; if cnt = 0 then :new.identity := to_char(sysdate,'yyyymmdd')||'0001'; else select max(substr(identity,-4,4)) into identity_before from identity; :new.identity := to_char(sysdate,'yyyymmdd')||trim(to_char(to_number(identity_before,'0000') + 1,'0000')); end if; --insert into identity (identity,key) values(:new.identity,:new.key); end identity_trigger; --插入行 insert into identity(identity) values('test');
scott@YPCOST> create sequence orderNo_seq start with 100 increment by 1 maxvalue 999;序列已创建。scott@YPCOST> create table test(id number,name varchar2(20));表已创建。--2、再加触发器
scott@YPCOST> ed
已写入 file afiedt.buf 1 create or replace trigger insert_tri
2 before insert on test
3 for each row
4 declare
5 id number;
6 begin
7 select orderNo_seq.nextval into id from dual;
8 :new.id:=to_char(sysdate,'yyyymmdd')||id;
9* end;
scott@YPCOST> /触发器已创建scott@YPCOST> insert into test(name) values('tom');已创建 1 行。scott@YPCOST> commit;提交完成。scott@YPCOST> select * from test;ID NAME
-------------------- --------------------
20110110101 tom
create sequence SEQ_A_NAME
minvalue 0
maxvalue 999999999999999
start with 1
increment by 1
cache 20;
create table IDENTITY
(
IDENTITY VARCHAR2(12) not null,
KEY VARCHAR2(10)
);--创建触发器
create or replace trigger identity_trigger
before insert on identity
for each row
declare
-- local variables here
identity_before varchar2(4);
cnt number;
begin
select count(*) into cnt from identity;
if cnt = 0 then
:new.identity := to_char(sysdate,'yyyymmdd')||'0001';
else
select max(substr(identity,-4,4)) into identity_before from identity;
:new.identity := to_char(sysdate,'yyyymmdd')||trim(to_char(to_number(identity_before,'0000') + 1,'0000'));
end if;
--insert into identity (identity,key) values(:new.identity,:new.key);
end identity_trigger;
--插入行
insert into identity(identity) values('test');