萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle DML 與交易控制完整入門:INSERT/UPDATE/DELETE/MERGE、ACID、Commit/Rollback/Savepoint

DML (Data Manipulation Language) 操作表資料,交易控制確保資料一致性。本文涵蓋四大 DML 指令語法、MERGE 合併、交易 ACID 特性、隱性/顯性提交與回滾、Savepoint 部分回滾、SQL*Plus 自動提交設定。

INSERT 插入資料

INSERT INTO table [(col1, col2...)] VALUES (val1, val2...);
  • 欄位清單可省略,但需對應所有欄位順序與型態。
  • 子查詢插入:INSERT INTO dept1(id,name) SELECT deptno,dname FROM dept;

UPDATE 更新資料

UPDATE table SET col1=val1 [, col2=val2...] [WHERE condition];
  • 無 WHERE 則更新全表,慎用。
  • 子查詢更新:UPDATE emp SET sal = (SELECT AVG(sal) FROM emp) WHERE deptno=20;

DELETE 刪除資料

DELETE [FROM] table [WHERE condition];
  • 無 WHERE 刪除全表資料 (保留結構),非 DDL,可回滾。
  • 大量刪除考慮 TRUNCATE 或分批 DELETE ... WHERE rownum <= 10000 避免 Undo 爆滿。

MERGE 合併 (Upsert)

MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name=s.name, t.sal=s.sal
WHEN NOT MATCHED THEN
  INSERT (id,name,sal) VALUES (s.id,s.name,s.sal);
  • 單一陳述式完成「存在即更新、不存在即插入」。
  • 目標表欄位前綴不可加別名 (如 t.name 只能寫 name 在 SET/VALUES 子句中)。

交易控制 (Transaction Control)

ACID 特性

特性說明
Atomicity (原子性)全部成功或全部失敗,視為不可分割單元
Consistency (一致性)交易前後資料庫滿足所有完整性約束
Isolation (隔離性)並發交易互不干擾,Oracle 預設 READ COMMITTED
Durability (持久性)Commit 後永久生效,紅寫日誌保證

交易邊界

  • 開始:第一條 DML (INSERT/UPDATE/DELETE/MERGE) 或 SET TRANSACTION。
  • 結束 (隱性提交):DDL (CREATE/ALTER/DROP)、DCL (GRANT/REVOKE)、正常斷開連線、SQL*Plus EXIT。
  • 結束 (顯性):COMMIT (永久)、ROLLBACK (完全回滾)、ROLLBACK TO savepoint (部分回滾)。

Savepoint 部分回滾

INSERT INTO dept VALUES (10,'A','X');
SAVEPOINT sp1;
INSERT INTO dept VALUES (20,'B','Y');
SAVEPOINT sp2;
DELETE FROM dept WHERE deptno=10;
ROLLBACK TO sp1;  -- 僅保留第一筆 INSERT
COMMIT;

鎖定行為

  • DML 執行時對受影響列加 Row Exclusive (TX) 鎖,其他 Session 修改同列會阻塞。
  • 查詢 (SELECT) 不加鎖 (除非 FOR UPDATE),讀取一致性靠 Undo 多版本 (MVCC)。

SQL*Plus 自動提交 (AUTOCOMMIT)

SHOW AUTOCOMMIT;        -- 顯示 OFF / ON / IMMEDIATE / n
SET AUTOCOMMIT OFF;     -- 手動控制 (預設)
SET AUTOCOMMIT ON;      -- 每條 DML 後自動 COMMIT
SET AUTOCOMMIT 10;      -- 每 10 條 DML 自動 COMMIT
  • 僅影響 SQL*Plus 客戶端行為,不改變資料庫伺服器交易語意。
  • 批次腳本建議 SET AUTOCOMMIT OFF 並在最後統一 COMMIT。
copyright © 萬盛學電腦網 all rights reserved