2 echo5222 Echo5222 于 2015.06.05 16:19 提问

oracle 存储过程 游标的问题

create or replace procedure test_procedure(planetimetableid in integer , gid in integer ,addpeo in integer,peoCount in integer
) is
cursor a_cursor is
select id from seats_info where id in(select id from (select t.*,rownum rn from seats_info t where t.istate ='0' and t.planetimetableid =planetimetableid
and t.isdelete = '0' ) where rn<=peoCount) for update;
begin
for a_cur in a_cursor loop
update seats_info set istate = '1',lastudpdate = sysdate,lastudppeoid =addpeo where id = a_cur.id;
insert into passenger_info(gid, seatsid, addpeo)values(gid,a_cur.id ,addpeo );
insert into test(sid,pid, cou)values(a_cur.id,planetimetableid,peoCount);
end loop;
commit;
-- ReturnValue:=1;
end test_procedure;

单独执行查询语句时 select id from seats_info where id in(select id from (select t.*,rownum rn from seats_info t where t.istate ='0' and t.planetimetableid =63
and t.isdelete = '0' ) where rn<=2) 能返回正确的数据
但是在存储过程中游标取出来的a_cui.id 数据不正确

求大神解答~~~帮忙看看哪里写错了

3个回答

edouardzyc
edouardzyc   2015.06.05 17:44

select id from seats_info where id in(select id from (select t.*,rownum rn from seats_info t where t.istate ='0' and t.planetimetableid =planetimetableid
and t.isdelete = '0' ) where rn<=peoCount) for update;

你这句话干嘛要 for update 你这样锁表了
后面的 update seats_info 语句应该没法运行把

frank_20080215
frank_20080215   2015.06.06 10:18

被锁住了,一个正常,一个脏读。 for update使用了。
好好阅读数据库的悲观锁、乐观锁的内容

datou431
datou431   Rxr 2015.06.10 15:29

阅读数据库的悲观锁、乐观锁的内容

Csdn user default icon
上传中...
上传图片
插入图片