Oracle实现列自增长
1、创建订单表myorder
将表中订单编号order_no自动增长
SQL> create table myorder
2 (order_no number,
3 order_name varchar2(20),
4 order_desc varchar2(20));
Table created.
2、创建序列seq_order_no
start with指定序列的起始值；maxvalue指定序列的最大值；minvalue指定最小值；nocycle表示序列达到最大值后不再循环；cache 100表示缓存100个序列值。
SQL> create sequence seq_order_no
2 start with 1
3 maxvalue 999999999999999
4 minvalue 1
5 nocycle
6 cache 100;
Sequence created.
3、创建触发器trigger_myorder
before insert表示插入前触发；for each row表示对每一行都触发；nextval表示取序列。
SQL> create trigger trigger_myorder
2 before insert on myorder
3 for each row
4 begin
5 select seq_order_no.nextval into :new.order_no from dual;
6 end;
7 /
Trigger created.
4、测试插入数据，并查看order_no是否自动增加
SQL> insert into myorder(order_name,order_desc) values ('ceshi1','ceshi1');
1 row created.
SQL> insert into myorder(order_name,order_desc) values ('ceshi2','ceshi2');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from myorder;
ORDER_NO ORDER_NAME ORDER_DESC
---------- -------------------- --------------------
1 ceshi1 ceshi1
2 ceshi2 ceshi2
