oracle select for update of ... update where current of ...
oracle syntax:select for update table.column[,table.column].. ]
当查询多张表时使用for update table.column[,table.column]..]子句可以锁定指定的表（行级锁），经测试可以同时锁定多张表（行级锁），不过对应使用where current of更新数据时会有问题，这时只能用for update table.column[,table.column]..]语句锁定一张表（行级锁），然后只能更新这张表
构造测试数据：
create table mtj(name varchar2(30),sex varchar2(1),age number(3));create table lux(name varchar2(30),sex varchar2(1),age number(3));insert into mtj values ('mtj','M',28);insert into mtj values ('mtj','M',28);insert into mtj values ('mtj','M',28);insert into mtj values ('mtj','M',28);insert into mtj values ('mtj','M',28);insert into mtj values ('mtj','M',28);insert into mtj values ('mtj','M',28);insert into lux values ('lux','M',26);insert into lux values ('lux','M',26);insert into lux values ('lux','M',26);insert into lux values ('lux','M',26);insert into lux values ('lux','M',26);insert into lux values ('lux','M',26);insert into lux values ('lux','M',26);insert into lux values ('lux','M',26);
测试脚本：
declarecursor cur_get_person is select m.name name_1 from mtj m, lux l where m.sex = l.sex for update of l.sex/* ,m.age*/;beginfor person in cur_get_person loop update lux l set l.age = 11 where current of cur_get_person; --update mtj m set m.age = 29 where current of cur_get_person;end loop;end;
另附tom对implicit cursor效率比explicit cursor快的解释
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1205168148688
页:
[1]