【獨孤九劍】Oracle 實戰 (四) SQL DML 與 DDL 核心模式大全
Oracle SQL 分類：DML (資料操作)、DDL (定義)、TCL (交易控制)、SCL (會話控制)、系統控制、嵌入式 SQL。本章聚焦高頻 DML/DDL 實戰模式。
1. DML 核心模式
基本增刪改查
SELECT * FROM table1;
INSERT INTO table1(f1,f2,f3) VALUES (v1,v2,v3);
UPDATE table1 SET f1=v1 WHERE f2=v2;
DELETE FROM table1 WHERE f2=v2;
ROWNUM 特性與分頁
- ROWNUM 為查詢結果動態編號，從 1 起算，不可跳躍：可用
WHERE ROWNUM <= N、WHERE ROWNUM = 1，不可 WHERE ROWNUM > 1 或 WHERE ROWNUM = 2。
WHERE ROWNUM = 0 可取得表結構 (無資料列)。
標準分頁寫法
SELECT * FROM (
SELECT t.*, ROWNUM rn FROM table1 t WHERE ROWNUM <= :end_row
) WHERE rn >= :start_row;
聯結查詢
| 風格 | 語法 |
|---|
| 舊式 (+) | WHERE t1.id = t2.id(+) (LEFT JOIN), WHERE t1.id(+) = t2.id (RIGHT JOIN) |
| ANSI JOIN | FROM t1 JOIN t2 ON t1.id=t2.id / LEFT JOIN / RIGHT JOIN / FULL OUTER JOIN |
MERGE (UPSERT) 單陳述式合併更新/插入
-- 兩表合併
MERGE INTO target_table a
USING source_table b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.f1=b.f1, a.f2=b.f2+1
WHEN NOT MATCHED THEN
INSERT (id,f1,f2) VALUES (b.id,b.f1,b.f2);
-- 多表關聯作為來源
MERGE INTO table1 a
USING (SELECT t2.id, t2.f1, t2.f2, t3.f1 AS f3
FROM table2 t2 JOIN table3 t3 ON t2.id=t3.id) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.f1=b.f1, a.f2=b.f2+1;
2. DDL 常用操作
| 指令 | 用途 | 範例 |
|---|
| CREATE | 建立 TABLE, VIEW, PROCEDURE, FUNCTION, SEQUENCE... | CREATE TABLE t (id NUMBER PRIMARY KEY); |
| ALTER | 修改物件結構 | ALTER TABLE t ADD (col2 VARCHAR2(20)); |
| DROP | 刪除物件 | DROP TABLE t; |
| TRUNCATE | 快速清空表 (不可回滾, 釋放空間) | TRUNCATE TABLE t; |
| RENAME | 重命名物件 | RENAME old_name TO new_name; |
| GRANT/REVOKE | 授權/撤銷權限 | GRANT SELECT ON t TO user1; |
| COMMENT | 加註解 | COMMENT ON TABLE t IS '業務表'; |
| AUDIT/NOAUDIT | 啟用/停用稽核 | AUDIT SELECT ON t BY ACCESS; |
3. 交易控制 (TCL)
COMMIT; -- 提交
ROLLBACK; -- 回滾
SAVEPOINT sp1; -- 設定存檔點
ROLLBACK TO sp1; -- 回滾至存檔點
SET TRANSACTION READ ONLY; -- 唯讀交易
實戰提醒
- 大量刪除優先考慮
TRUNCATE 或分區截斷，避免大量 Undo/Redo 產生。
- MERGE 適用於 ETL 同步場景，單一 SQL 完成 INSERT/UPDATE，效能優於分開執行。
- DDL 隱含 COMMIT，謹慎在交易中穿插 DDL。
