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快的解释
[url]http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1205168148688[/url]