我在SQL Plus中执行select title from book for update of title where isbn = 'A001',然后再打开一个SQL Plus窗口执行update book set price = 20 where isbn = 'A001',更新无法完成,必须把select...for update of 提交了才能执行,请问为什么?这样的话select...for update of 和select...for update有什么区别?
调试欢乐多
ex: select a.col1 from a, b where a.col1 = b.col2 and ... for udpate
这个时候锁住了a,b两个表中满足条件的记录,而
select a.col1 from a, b where a.col1 = b.col2 and ... for update of a.col1
仅仅锁住了a表中满足条件的记录所以for update 和for update of 还是有区别的
锁定的是OF后面所写的栏位所在的表
写一个栏位和多个栏位的意义是一样的
cursor t_sor is
select title
from book
where isbn = 'A001'
for update of id NOWAIT;
begin
for v_sor in t_sor loop
delete from book WHERE CURRENT OF t_sor;
end loop;
end;以上语句作用于多用户操作同一记录时,防止等待现像.for update of 字段,可用于操作条件.
for update of 字段用于操作条件
不是可以通过where 条件来筛选记录么,而且虽然写了for update of 字段,但是事实上锁住的仍然是整条记录阿,并不是字段,如果其他session需要更新该条记录的其他字段(不含for update of 字段中的字段),仍然是被堵塞的猫猫这块理解的不是特别清楚
(id number,
name varchar2(10)
)create table b
(id number,
f_id number
) insert into a values(1,'张三');
insert into b values(1,0);
commit;declare
cursor t_sor is
select a.name
from a,b
where a.id = b.id
for update of a.id;
begin
for v_sor in t_sor loop
delete a
WHERE CURRENT OF t_sor;
--delete b
--WHERE CURRENT OF t_sor;
end loop;
end; you must make sure the row is not changed by another user before the update or delete a table;本人测试过程中,还发现以下错误:
SQL> declare
2 cursor t_sor is
3 select a.name
4 from a,b
5 where a.id = b.id
6 for update of a.id;
7 begin
8 for v_sor in t_sor loop
9 delete b
10 WHERE CURRENT OF t_sor;
11 end loop;
12 end;
13 /declare
cursor t_sor is
select a.name
from a,b
where a.id = b.id
for update of a.id;
begin
for v_sor in t_sor loop
delete b
WHERE CURRENT OF t_sor;
end loop;
end;ORA-01410: invalid ROWID
ORA-06512: at line 9游标中只对a表进行锁定操,但程序中对b表进行操用,oracle报ORA-01410: invalid ROWID.
where current of cursor_name不是用来表示游标取得的当前的纪录么
cursor t_sor is
select a.name
from a,b
where a.id = b.id
for update of a.id;
游标取得的是a表中的纪录,而delete b WHERE CURRENT OF t_sor;删除的是b表的数据,返回错误也是正常啊猜测where current of 取得游标指针指着的当前纪录的rowid,然后根据rowid进行update,delete之类的操作不知道是否正确,各位老大请帮忙指正
谢谢,谢谢
不是可以通过where 条件来筛选记录么,而且虽然写了for update of 字段,但是事实上锁住的仍然是整条记录阿,并不是字段,如果其他session需要更新该条记录的其他字段(不含for update of 字段中的字段),仍然是被堵塞的”
SQL> declare
2 cursor t_sor is
3 select a.name
4 from a,b
5 where a.id = b.id
6 for update of a.id,b.id;
7 begin
8 for v_sor in t_sor loop
9 delete a
10 WHERE CURRENT OF t_sor;
11 delete b
12 WHERE CURRENT OF t_sor;
13 end loop;
14 end;
15 /PL/SQL procedure successfully completed
猜测这样写可以增强程序的易读性吧