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个回答

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 语句应该没法运行把

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

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问