select 'T'||to_char(sysdate,'yyyymmdd')||lpad(seq.nextval,4,0) from dual
scott@TBWORA> CREATE TABLE t_test( 2 seq varchar2(20) not null primary key, 3 name varchar2(50) 4 );表已创建。scott@TBWORA> scott@TBWORA> -- 序列号占默认8位宽度 scott@TBWORA> CREATE SEQUENCE t_test_seq start with 1 nocache maxvalue 99999999;序列已创建。scott@TBWORA> scott@TBWORA> CREATE OR REPLACE TRIGGER t_test_trig_autoinc 2 BEFORE INSERT ON t_test 3 FOR EACH ROW 4 BEGIN 5 IF (:new.seq IS NULL) THEN 6 SELECT 'T'||to_char(sysdate,'yyyymmdd')||lpad(t_test_seq.nextval,8,'0') INTO :new.seq FROM DUAL; 7 END IF; 8 END; 9 /触发器已创建scott@TBWORA> scott@TBWORA> insert into t_test(name) values('luoyoumou1');已创建 1 行。scott@TBWORA> insert into t_test(name) values('luoyoumou2');已创建 1 行。scott@TBWORA> insert into t_test(name) values('luoyoumou3');已创建 1 行。scott@TBWORA> insert into t_test(name) values('luoyoumou4');已创建 1 行。scott@TBWORA> scott@TBWORA> commit;提交完成。scott@TBWORA> scott@TBWORA> select * from t_test;SEQ NAME ------------------ -------------------- T2011081000000001 luoyoumou1 T2011081000000002 luoyoumou2 T2011081000000003 luoyoumou3 T2011081000000004 luoyoumou4
CREATE TABLE t_test( seq varchar2(20) not null primary key, name varchar2(50) );-- 序列号占默认8位宽度 CREATE SEQUENCE t_test_seq start with 1 nocache maxvalue 99999999;CREATE OR REPLACE TRIGGER t_test_trig_autoinc BEFORE INSERT ON t_test FOR EACH ROW BEGIN IF (:new.seq IS NULL) THEN SELECT 'T'||to_char(sysdate,'yyyymmdd')||lpad(t_test_seq.nextval,8,'0') INTO :new.seq FROM DUAL; END IF; END; /insert into t_test(name) values('luoyoumou1'); insert into t_test(name) values('luoyoumou2'); insert into t_test(name) values('luoyoumou3'); insert into t_test(name) values('luoyoumou4');commit;select * from t_test;
2 seq varchar2(20) not null primary key,
3 name varchar2(50)
4 );表已创建。scott@TBWORA>
scott@TBWORA> -- 序列号占默认8位宽度
scott@TBWORA> CREATE SEQUENCE t_test_seq start with 1 nocache maxvalue 99999999;序列已创建。scott@TBWORA>
scott@TBWORA> CREATE OR REPLACE TRIGGER t_test_trig_autoinc
2 BEFORE INSERT ON t_test
3 FOR EACH ROW
4 BEGIN
5 IF (:new.seq IS NULL) THEN
6 SELECT 'T'||to_char(sysdate,'yyyymmdd')||lpad(t_test_seq.nextval,8,'0') INTO :new.seq FROM DUAL;
7 END IF;
8 END;
9 /触发器已创建scott@TBWORA>
scott@TBWORA> insert into t_test(name) values('luoyoumou1');已创建 1 行。scott@TBWORA> insert into t_test(name) values('luoyoumou2');已创建 1 行。scott@TBWORA> insert into t_test(name) values('luoyoumou3');已创建 1 行。scott@TBWORA> insert into t_test(name) values('luoyoumou4');已创建 1 行。scott@TBWORA>
scott@TBWORA> commit;提交完成。scott@TBWORA>
scott@TBWORA> select * from t_test;SEQ NAME
------------------ --------------------
T2011081000000001 luoyoumou1
T2011081000000002 luoyoumou2
T2011081000000003 luoyoumou3
T2011081000000004 luoyoumou4
CREATE TABLE t_test(
seq varchar2(20) not null primary key,
name varchar2(50)
);-- 序列号占默认8位宽度
CREATE SEQUENCE t_test_seq start with 1 nocache maxvalue 99999999;CREATE OR REPLACE TRIGGER t_test_trig_autoinc
BEFORE INSERT ON t_test
FOR EACH ROW
BEGIN
IF (:new.seq IS NULL) THEN
SELECT 'T'||to_char(sysdate,'yyyymmdd')||lpad(t_test_seq.nextval,8,'0') INTO :new.seq FROM DUAL;
END IF;
END;
/insert into t_test(name) values('luoyoumou1');
insert into t_test(name) values('luoyoumou2');
insert into t_test(name) values('luoyoumou3');
insert into t_test(name) values('luoyoumou4');commit;select * from t_test;