oracle insert into 获取自动增长列值

在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;