sql – 使用SELECT锁定Oracle … FOR UPDATE OF

发布时间：2020-12-12 06:51:01 所属栏目：MsSql教程来源：网络整理

导读：我从桌子FOO和BAR中选择.我想锁定正在返回的FOO的记录,但我不想锁定BAR的记录. cursor c_foobar is select foo.*,bar.* fromfoo,barwhere foo.id = bar.foo_idfor update of what should I put here? 好像我需要指定单独的列,但我希望锁定整个foo记录.例如我

我从桌子FOO和BAR中选择.我想锁定正在返回的FOO的记录,但我不想锁定BAR的记录.

cursor c_foobar is 
select foo.*,bar.* from
foo,bar
where foo.id = bar.foo_id
for update of <what should I put here?>

好像我需要指定单独的列,但我希望锁定整个foo记录.例如我希望我可以这样做：

cursor c_foobar is
select foo.*,bar
where foo.id = bar.foo_id
for update of foo

我是否必须在更新部分中枚举foo的每一列以便将它们全部锁定？或者我可以随意选择foo中的任何列,即使那些不是它的主键,它会锁定整个记录吗？

解决方法

从 the 10G PL/SQL documentation开始：

When querying multiple tables,you can
use the FOR UPDATE clause to confine
row locking to particular tables. Rows
in a table are locked only if the FOR
UPDATE OF clause refers to a column
in that table. For example,the
following query locks rows in the
employees table but not in the
departments table:

DECLARE
  CURSOR c1 IS SELECT last_name,department_name FROM employees,departments
    WHERE employees.department_id = departments.department_id 
          AND job_id = 'SA_MAN'
      FOR UPDATE OF salary;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!