UPDATE table_name
SET col1 = val1, col2 = val2, ...
[WHERE condition];
-- 方式1: 逐欄位指定 (最常用)
UPDATE test SET name='zhurhyme1', code='007' WHERE i_d='1';
-- 方式2: 元組賦值 (需子查詢)
UPDATE test SET (name, code) = (SELECT 'zhurhyme3','007' FROM dual) WHERE i_d='1';
-- 方式3: 元組賦值直接給值 (12c+ 支援)
UPDATE test SET (name, code) = ('zhurhyme2','007') WHERE i_d='1';
| SQL | 鎖定範圍 |
|---|---|
| SELECT * FROM test FOR UPDATE | 全表所有行 |
| SELECT * FROM test WHERE i_d=1 FOR UPDATE | 符合條件的行 |
| SELECT * FROM test a JOIN t ON a.i_d=t.dept_id FOR UPDATE | 兩表所有符合 JOIN 的行 |
| SELECT * FROM test a JOIN t ON a.i_d=t.dept_id WHERE a.i_d=1 FOR UPDATE | 符合條件的行 |
| SELECT * FROM test a JOIN t ON a.i_d=t.dept_id WHERE a.i_d=1 FOR UPDATE OF a.i_d | 僅鎖定 test 表符合條件的行,t 表不鎖定 |
僅鎖定指定表的指定欄位所在行,減少鎖衝突:
SELECT name, code FROM test WHERE i_d=1 FOR UPDATE OF name;
兩個 Session 同時執行:
DECLARE
CURSOR cur_test IS SELECT name,code FROM test WHERE i_d=1 FOR UPDATE OF name;
BEGIN
FOR rec IN cur_test LOOP
UPDATE test SET name='TTTT1' WHERE CURRENT OF cur_test;
END LOOP;
END;
/
若第一個 Session 未提交,第二個 Session 會阻塞;若雙方互相等待對方釋放 → 死鎖。
FOR UPDATE OF)