oracle环境下,在存储过程中写insert into 语句,插入的一条,使得该条的code_id列依次递增(之前可能有两条数据code_id分别为1、2),下面插入一条code_id就为3,依此类推.....求走过路过的大侠们帮忙。PROCEDURE insert_lc_code_hospital(
hospital IN varchar2
)
IS
BEGIN
insert into...
hospital IN varchar2
)
IS
BEGIN
insert into...
--oracle的自增长一般用触发器实现,给你个例子
--1.先创建序列
scott@YPCOST> create sequence orderNo_seq start with 100 increment by 1 maxvalue 999;序列已创建。scott@YPCOST> create table test(id number,name varchar2(20));表已创建。--2、再加触发器
scott@YPCOST> ed
已写入 file afiedt.bufcreate or replace trigger insert_tri
before insert on test
for each row
declare
begin
select orderNo_seq.nextval into :new.id from dual;
end;
scott@YPCOST> /触发器已创建scott@YPCOST> insert into test(name) values('tom');已创建 1 行。scott@YPCOST> commit;提交完成。scott@YPCOST> select * from test;ID NAME
-------------------- --------------------
100 tom
thinkS
values(tb_sequence_name.nextval, 'vaue2', value3);-- 或者:
insert into tb(col1, col2, col3)
select tb_sequence_name.nextval, tb2.col2, tb2.col3
from tb2;
PROCEDURE insert_lc_code_hospital(
hospital IN varchar2
)
IS
BEGIN
create SEQUENCE code_id INCREMENT BY 1
insert into lc_code(type_id,code_id,code_name,group_value,DC,is_default)
values(8,code_id.nextval,hospital,8,'N','Y');
END;
我这样写提示错误,就各位指点。
1 insert into t(id,name)
2* select t_seq.nextval, 'luoyoumou2' from dual
scott@RACDB>
scott@RACDB> /已创建 1 行。scott@RACDB> l
1 insert into t(id,name)
2* select t_seq.nextval, 'luoyoumou2' from dual
scott@RACDB> /已创建 1 行。scott@RACDB> /已创建 1 行。scott@RACDB> select * from t; ID NAME
---------- ----------------------------------------
1 luoyoumou1
1 luoyoumou2
2 luoyoumou2
3 luoyoumou2