create table test( id number(18,0), name varchar2(30)); create sequence test_seq start with 1 increment by 1 nomaxvalue nocycle;CREATE OR REPLACE PROCEDURE test_op_proc( v_name varchar2, v_sid out number) as begin select test_seq.nextval into v_sid from dual; insert into test(id,name) values (v_sid, v_name); commit; end; /set serveroutput on; var c_sid number; exec test_op_proc('luoyoumoou',:c_sid); print c_sid; select * from test;
create table test( id number(18,0), name varchar2(30)); create sequence test_seq start with 1 increment by 1 nomaxvalue nocycle;CREATE OR REPLACE PROCEDURE test_op_proc( v_name varchar2, v_sid out number) as begin -- select test_seq.nextval into v_sid from dual; -- Oracle 10g (用这条语句代替下一条语句) v_sid := test_seq.nextval; -- Oracle 11g (用这条语句代替上一条语句,以提高执行效率!) insert into test(id,name) values (v_sid, v_name); commit; end; /set serveroutput on; var c_sid number; exec test_op_proc('luoyoumoou',:c_sid); print c_sid; select * from test;
用returning 返回值 --先建立一个序列--returning set serveroutput on declare v_id int;begin insert into t1 values(s_id.nextval,'1') returning id into v_id; dbms_output.put_line(v_id);end;
insert into t1 values(s_id.nextval,'1') returning id into :v_id; 使用jdbc绑定:v_id即可得到insert的值。
如:
<insert id="insertProduct-ORACLE" parameterClass="com.domain.Product">
<selectKey resultClass="int" keyProperty="id" type="pre">
<![CDATA[SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL]]>
</selectKey>
<![CDATA[insert into PRODUCT (PRD_ID,PRD_DESCRIPTION) values(#id#,#description#)]]>
</insert>
你写段JDBC代码,通过查询语句去获取新插入的ID啊
select id from 表 where .....(插入新的值去获取该值对应的ID)
具体哪个方法忘了。
sequence.nextval -表示序列的下一个值
sequence.currval -表示序列的当前值可以这样实现你的需求
1. cid = sequence.nextval
2 insert (id,...) values(cid,...)
id number(18,0),
name varchar2(30));
create sequence test_seq start with 1 increment by 1 nomaxvalue nocycle;CREATE OR REPLACE PROCEDURE test_op_proc( v_name varchar2, v_sid out number)
as
begin
select test_seq.nextval into v_sid from dual;
insert into test(id,name) values (v_sid, v_name);
commit;
end;
/set serveroutput on;
var c_sid number;
exec test_op_proc('luoyoumoou',:c_sid);
print c_sid;
select * from test;
id number(18,0),
name varchar2(30));
create sequence test_seq start with 1 increment by 1 nomaxvalue nocycle;CREATE OR REPLACE PROCEDURE test_op_proc( v_name varchar2, v_sid out number)
as
begin
-- select test_seq.nextval into v_sid from dual; -- Oracle 10g (用这条语句代替下一条语句)
v_sid := test_seq.nextval; -- Oracle 11g (用这条语句代替上一条语句,以提高执行效率!)
insert into test(id,name) values (v_sid, v_name);
commit;
end;
/set serveroutput on;
var c_sid number;
exec test_op_proc('luoyoumoou',:c_sid);
print c_sid;
select * from test;
declare
v_id int;begin
insert into t1 values(s_id.nextval,'1') returning id into v_id;
dbms_output.put_line(v_id);end;
使用jdbc绑定:v_id即可得到insert的值。