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;

oracle insert into 获取自动增长列值

上一篇:什么是数据库?什么是关系数据库?什么是非关系型数据库?


下一篇:Mybatis根据Spring配置文件实现写不同的Sql语句