DML (Data Manipulation Language) 操作表資料,交易控制確保資料一致性。本文涵蓋四大 DML 指令語法、MERGE 合併、交易 ACID 特性、隱性/顯性提交與回滾、Savepoint 部分回滾、SQL*Plus 自動提交設定。
INSERT INTO table [(col1, col2...)] VALUES (val1, val2...);
INSERT INTO dept1(id,name) SELECT deptno,dname FROM dept;UPDATE table SET col1=val1 [, col2=val2...] [WHERE condition];
UPDATE emp SET sal = (SELECT AVG(sal) FROM emp) WHERE deptno=20;DELETE [FROM] table [WHERE condition];
TRUNCATE 或分批 DELETE ... WHERE rownum <= 10000 避免 Undo 爆滿。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 子句中)。| 特性 | 說明 |
|---|---|
| Atomicity (原子性) | 全部成功或全部失敗,視為不可分割單元 |
| Consistency (一致性) | 交易前後資料庫滿足所有完整性約束 |
| Isolation (隔離性) | 並發交易互不干擾,Oracle 預設 READ COMMITTED |
| Durability (持久性) | Commit 後永久生效,紅寫日誌保證 |
SET TRANSACTION。EXIT。COMMIT (永久)、ROLLBACK (完全回滾)、ROLLBACK TO 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;
FOR UPDATE),讀取一致性靠 Undo 多版本 (MVCC)。SHOW AUTOCOMMIT; -- 顯示 OFF / ON / IMMEDIATE / n SET AUTOCOMMIT OFF; -- 手動控制 (預設) SET AUTOCOMMIT ON; -- 每條 DML 後自動 COMMIT SET AUTOCOMMIT 10; -- 每 10 條 DML 自動 COMMIT
SET AUTOCOMMIT OFF 並在最後統一 COMMIT。