ORACLE没有自增类型字段,你可以在表中创建一列,然后用触发器实现数据初始化。创建序列样本: create sequence SEQ_NAME minvalue 1 maxvalue 999999999999999999999999999 start with 1144 increment by 1 cache 20;触发器使用before insert将该字段新值格式化为YYYYMMDD-0000X
创建序列是必须的,触发器就不一定必须了。触发器示例如下:create or replace trigger triname before insert on table_name for each row declare num number; begin select seq_name.nextval into num from dual; :new.col := to_char(trunc(sysdate), 'yyyymmdd') ||'-'|| lpad(num, 5, '0'); end triname;如果不用触发器,就在insert的时候拼接字符串,但还是要应用序列。
SQL> create table test (id varchar2(20),name varchar2(16));表已创建。SQL> create sequence seq_test_id 2 start with 1 3 increment by 1;序列已创建。SQL> create or replace trigger trig_test 2 before insert on test 3 for each row 4 declare 5 -- local variables here 6 begin 7 SELECT to_char(SYSDATE,'yyyymmdd')||'-'||lpad(seq_test_id.NEXTVAL,5,'0' ) INTO :new.id FROM dual; 8 end trig_test; 9 /触发器已创建SQL> insert into test (name) values ('AC米兰');已创建 1 行。SQL> commit;提交完成。SQL> select * from test;ID NAME -------------------- ---------------- 20100324-00002 AC米兰SQL>
create sequence SEQ_NAME
minvalue 1
maxvalue 999999999999999999999999999
start with 1144
increment by 1
cache 20;触发器使用before insert将该字段新值格式化为YYYYMMDD-0000X
before insert on table_name
for each row
declare
num number;
begin
select seq_name.nextval into num from dual;
:new.col := to_char(trunc(sysdate), 'yyyymmdd') ||'-'|| lpad(num, 5, '0');
end triname;如果不用触发器,就在insert的时候拼接字符串,但还是要应用序列。
2 start with 1
3 increment by 1;序列已创建。SQL> create or replace trigger trig_test
2 before insert on test
3 for each row
4 declare
5 -- local variables here
6 begin
7 SELECT to_char(SYSDATE,'yyyymmdd')||'-'||lpad(seq_test_id.NEXTVAL,5,'0'
) INTO :new.id FROM dual;
8 end trig_test;
9 /触发器已创建SQL> insert into test (name) values ('AC米兰');已创建 1 行。SQL> commit;提交完成。SQL> select * from test;ID NAME
-------------------- ----------------
20100324-00002 AC米兰SQL>