-- 使用触发器SQL> SQL> create table test(key int, code varchar(10)); Table created SQL> create sequence seq_test; Sequence created SQL> create trigger tri_test_id 2 before insert on test 3 for each row 4 begin 5 :new.key := seq_test.nextval; 6 end; 7 / Trigger created SQL> begin 2 insert into test(code) values('zhao'); 3 insert into test(code) values('qian'); 4 insert into test(code) values('sun'); 5 insert into test(code) values('li'); 6 end; 7 / PL/SQL procedure successfully completed SQL> select * from test ; KEY CODE --------------------------------------- ---------- 1 zhao 2 qian 3 sun 4 li SQL> drop table test purge; Table dropped SQL> drop sequence seq_test; Sequence droppedSQL>
(序列名)seq.nextval as key,
T.code
from T (表名); 这样,每次在PL/SQL中run一次后,key的内容都会发生改变,但是code不发生改变。需要每次运行后key的内容都不发生改变,请问应该怎么处理?
select rownum key , code from t 或select row_number() over(order by code) key , code from t
是会改变的,如果不想改变,只能再加一列,把这个 key 存到表中。
是会改变的,如果不想改变,只能再加一列,把这个 key 存到表中。
那么这需要修改表结构吗?应该怎么进行操作
SQL> create table test(key int, code varchar(10));
Table created
SQL> create sequence seq_test;
Sequence created
SQL> create trigger tri_test_id
2 before insert on test
3 for each row
4 begin
5 :new.key := seq_test.nextval;
6 end;
7 /
Trigger created
SQL> begin
2 insert into test(code) values('zhao');
3 insert into test(code) values('qian');
4 insert into test(code) values('sun');
5 insert into test(code) values('li');
6 end;
7 /
PL/SQL procedure successfully completed
SQL> select * from test ;
KEY CODE
--------------------------------------- ----------
1 zhao
2 qian
3 sun
4 li
SQL> drop table test purge;
Table dropped
SQL> drop sequence seq_test;
Sequence droppedSQL>
nextval返回增值后的sequence值
如果KEY存储了,那查询的时候原来的key内容肯定不变了。