oracle 12c之前用sequence 和 trigger來產生自動成長的列

標籤：for   art   ar   new   sql   oracle   table   har   

SQL> create table scott.t1 (id number, val varchar2(8));

Table created.

SQL> CREATE SEQUENCE scott.t1_id_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE ;

Sequence created.

 

SQL> create or replace trigger scott.t1_id_trigger
2 before insert on scott.t1 for each row
3 begin
4 select scott.t1_id_seq.nextval into :new.id from dual;
5 end;
6 /

Trigger created.

 

SQL> insert into scott.t1(val) values(‘a‘);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.t1;

ID VAL
---------- ------------------------
1 a

SQL> insert into scott.t1(val) values(‘b‘);

1 row created.

SQL> select * from scott.t1;

ID VAL
---------- ------------------------
1 a
2 b

SQL> commit;

Commit complete.