oracle select for update of ... update where current of ...

小马迷途

oracle syntax:
select for update [of [schema.]table.column[,[schema.]table.column].. ][nowait]

当查询多张表时使用for update [of [schema.]table.column[,[schema.]table.column]..]子句可以锁定指定的表（行级锁），经测试可以同时锁定多张表（行级锁），不过对应使用where current of更新数据时会有问题，这时只能用for update [of [schema.]table.column[,[schema.]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);

测试脚本：
declare  cursor 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*/;begin  for 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