1、方式一:使用序列和触发器
- SQL> CREATE SEQUENCE test_sequence2
- 2 increment by 1 -- 每次递增1
- 3 start with 1 -- 从1开始
- 4 nomaxvalue -- 没有最大值
- 5 minvalue 1 -- 最小值=1
- 6 NOCYCLE; -- 不循环
- Sequence created.
- SQL> CREATE TABLE test_create_tab2 (
- 2 id INT,
- 3 val VARCHAR(10),
- 4 PRIMARY KEY (id)
- 5 );
- Table created.
- SQL> CREATE OR REPLACE TRIGGER BeforeTestCreate2Insert
- 2 BEFORE INSERT ON test_create_tab2
- 3 FOR EACH ROW
- 4 BEGIN
- 5 SELECT test_sequence2.nextval INTO :new.id FROM dual;
- 6 END;
- 7 /
- Trigger created.
- SQL> INSERT INTO test_create_tab2(val) VALUES ('NO id');
- 1 row created.
- SQL> INSERT INTO test_create_tab2(id, val) VALUES (1, 'id no use');
- 1 row created.
- SQL> SELECT * FROM test_create_tab2;
- ID VAL
- ---------- --------------------
- 1 NO id
- 2 id no use