Oracle UPDATE 更新語句完整指南：多欄位、子查詢、FOR UPDATE 鎖機制

基本語法

UPDATE table_name
SET col1 = val1, col2 = val2, ...
[WHERE condition];

實戰演示：Employee 表批次更新

測試資料 (同 DELETE 範例)

-- 將 New York 改為 L.A.
UPDATE Employee
SET City = 'L.A.'
WHERE City = 'New York';
-- 2 rows updated.

進階更新模式

1. 多欄位單一值更新

UPDATE test
SET name = 'zhurhyme1', code = '007'
WHERE i_d = '1';

2. 多欄位子查詢更新 (SET 子句必須為子查詢)

-- 錯誤寫法
UPDATE test SET (name, code) = ('zhurhyme2', '007') WHERE i_d = '1';  -- ORA-00971

-- 正確：使用子查詢
UPDATE test
SET (name, code) = (SELECT 'zhurhyme3', '007' FROM dual)
WHERE i_d = '1';

3. 從另一表同步更新 (相關子查詢)

UPDATE target t
SET (t.col1, t.col2) = (
  SELECT s.col1, s.col2 FROM source s WHERE s.id = t.id
)
WHERE EXISTS (SELECT 1 FROM source s WHERE s.id = t.id);

FOR UPDATE 鎖機制深度解析

`SELECT ... FOR UPDATE` 在查詢階段對符合條件的行加排他鎖 (行級鎖)，直到交易 `COMMIT/ROLLBACK` 釋放，防止併發修改衝突。

鎖定範圍變化

FOR UPDATE OF 精確控制

• 指定 `OF table.column` 可只鎖定該表該欄位所在行，避免不必要鎖定連接表。
• 鎖從 `OPEN CURSOR` 開始，到 `COMMIT/ROLLBACK` 結束，非 `CLOSE CURSOR` 釋放。

死鎖實戰重現

兩個 Session 同時對同一行執行 `FOR UPDATE` + `UPDATE ... WHERE CURRENT OF`：

-- Session 1
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;
  -- 故意不 COMMIT
END;
/

-- Session 2 (同時執行)
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 = 'TTTT2' WHERE CURRENT OF cur_test;
  END LOOP;
END;
/
-- Session 2 將阻塞等待 Session 1 釋放鎖
-- 若 Session 1 長時間不提交/回滾，Session 2 永久阻塞 → 應用層面死鎖

避免死鎖最佳實踐

1. 固定加鎖順序：所有交易按相同表、相同欄位順序加鎖。
2. 縮短交易長度：`FOR UPDATE` 查詢與後續 `UPDATE` 盡量在同一簡短交易中完成。
3. 使用 `NOWAIT` / `WAIT n`：

   SELECT ... FOR UPDATE NOWAIT;  -- 立即返回 ORA-00054
   SELECT ... FOR UPDATE WAIT 5;   -- 等待 5 秒後報錯

4. 避免長時間持有鎖的用戶互動：不要在 `FOR UPDATE` 後等待用戶輸入。

觸發器中調用存儲過程更新關聯表

CREATE OR REPLACE TRIGGER trigger1
BEFORE INSERT OR DELETE OR UPDATE ON triggertest
FOR EACH ROW
BEGIN
  testpro1;  -- 呼叫存儲過程
END;
/

CREATE OR REPLACE PROCEDURE testpro1 AS
BEGIN
  INSERT INTO tab1 (id, name, score) VALUES ('AAA', 'BBB', 200);
END;
/
-- 觸發器執行後，tab1 自動新增一筆

級聯更新範例

-- triggertest.name 變更時，同步更新 tab1.name
CREATE OR REPLACE TRIGGER trigger1
AFTER UPDATE OF name ON triggertest
FOR EACH ROW
BEGIN
  UPDATE tab1 SET name = :NEW.name WHERE name = :OLD.name;
END;
/
-- 執行：UPDATE triggertest SET name = '水兒' WHERE name = '月兒';
-- tab1 中 name='月兒' 的行同步改為 '水兒'