在SQLServer中,插入一条记录,获取该行的标识列非常简单
insert into table_name() values();select @@identity;
在C# ado.net中,直接sqlcommand类的executescalar()方法就能获取刚刚插入语句的标识列
Oracle中没有这么方便的方式,那么如何获取insert into后,返回的标识列的值呢?
涉及到两个知识点:
1.关于Oracle标识列的自增问题(sequence、trigger、nextval、currval)
2.execute immediate 执行DML(insert into)的问题
Oracle标识列的自增问题
1.在网上看的最多的就是采用创建sequence+trigger的方式
create sequence seq_Dishes minvalue 100 start with 100 increment by 1 nomaxvalue nocycle nocache; create or replace trigger TG_seq_Dishes before insert on Dishes for each row begin select seq_Dishes.nextval into :new.DishId from dual; end;
验证时,发现标识列每次自增是原来的两倍
原因:insert into时触发器将原来准备好的正确的标识列值赋给下表的dishid,但是在插入过程中,该标识列值就变成了seq_dishes.currval,结果又发现values中有一个新的seq_dishes.nextval,所以当然会将新的标识列值赋给dishid了。
insert into Dishes(dishid,DishName,UnitPrice,CategoryId) values(seq_dishes.nextval,‘西红柿‘,10,2);
如果执行下句,则会改变标识列的值,是当前查询到的结果变成seq_dishes.currval
select seq_dishes.nextval from dual;
2.不用触发器,则每次在insert语句中必须包含标识列字段,dishid(seq_dishes.nextval)
insert into Dishes(dishid,DishName,UnitPrice,CategoryId) values(seq_dishes.nextval,‘西红柿‘,10,2);
最后需要说明的是,任何对 CURRVAL 的引用返回指定序列的当前值,该值是最后一次对 NEXTVAL 的引用所返回的值。用 NEXTVAL生成一个新值以后,可以继续使用 CURRVAL 访问这个值,不管另一个用户是否增加这个序列,currval使用时,必须放在nextval所在的回话中,且先有nextval,后面用户才能使用currval调用。
注:Oracle的标识列自增非常执着,如匿名存储过程执行一半出错,但是执行了insert into语句,或者执行单个的insert into语句,再rollback,标识列都会更新。
declare idx number; begin insert into Dishes(DishName,UnitPrice,CategoryId) values(‘西红柿‘,10,2); select seq_dishes.currval into idx from dual; dbms_output.put_line(idx); commit; end;
execute immediate用于在块中执行SQL语句(execute immediate 执行DML语句,不会自动commit)
一般方式:
declare insrt_sql varchar2(200); begin insrt_sql := ‘insert into Dishes(DishName,UnitPrice,CategoryId) values(‘‘烧鸡‘‘,23,2)‘; execute immediate insrt_sql; commit; end;
通过占位符的方式传参,并执行insert语句:
declare insrt_sql varchar2(200); begin insrt_sql := ‘insert into Dishes(DishName,UnitPrice,CategoryId) values(:1,:2,:3)‘; execute immediate insrt_sql using ‘烧鸡‘,244,2; commit; end;
通过定义变量,赋值的方式:
declare insrt_sql varchar2(200); dishname varchar2(20):=‘烧鸡‘; price number := 200; idx number := 2; begin insrt_sql := ‘insert into Dishes(DishName,UnitPrice,CategoryId) values(‘|| ‘‘‘‘ || dishname || ‘‘‘‘ || ‘,‘ || price || ‘,‘|| idx ||‘)‘; execute immediate insrt_sql; commit; end;
将上面的匿名块封改成命名存储过程,并执行OK:
create or replace procedure usp_querysequence ( dishname varchar2, price number, idx number ) is insrt_sql varchar2(200); begin insrt_sql := ‘insert into Dishes(DishName,UnitPrice,CategoryId) values(‘; insrt_sql := insrt_sql || ‘‘‘‘ || dishname || ‘‘‘‘ || ‘,‘ || price || ‘,‘|| idx ||‘)‘; execute immediate insrt_sql; commit; end; execute usp_querysequence(‘烤鱼‘,250,3);
将insert语句变成参数,放入存储过程,并在匿名块中调用该存储过程:
create or replace procedure usp_querysequence ( insrt_sql varchar2 ) is
begin execute immediate insrt_sql; commit; end; declare insrt_sql varchar2(200); begin insrt_sql := ‘insert into Dishes(DishName,UnitPrice,CategoryId) values(‘‘烧鸡‘‘,23,2)‘; usp_querysequence(insrt_sql); end;
最后,oracle数据库insert后获取insert数据的自增id值:(ADO.net中,可以直接调用该存储过程)
create or replace procedure usp_querysequence ( insrt_sql in varchar2, SEQ_NAME in varchar2, newid out number ) is str_sql varchar(100); begin execute immediate insrt_sql; str_sql := ‘select ‘||SEQ_NAME||‘.CURRVAL from dual ‘ ; execute immediate str_sql into newid; commit; end;
程序模拟调用存储过程,执行OK
declare insrt_sql varchar2(200); seq_name varchar2(20) := ‘seq_dishes‘; newid number := 0; begin insrt_sql := ‘insert into Dishes(DishName,UnitPrice,CategoryId) values(‘‘烤肉‘‘,15,2)‘; usp_querysequence(insrt_sql,seq_name,newid); dbms_output.put_line(newid); end;