Oracle DML 與 DCL 數據操作與控制語言深度解析

SQL 四大語言分類

• DQL (Data Query Language) - 查詢
• DML (Data Manipulation Language) - 操作
• DDL (Data Definition Language) - 定義
• DCL (Data Control Language) - 控制

本文聚焦 DML 與 DCL。

一、DML 語句

1. INSERT 新增資料

• 欄位清單可省略 (預設全欄位，順序依 COLUMN_ID)
• 值的數量/型別必須與欄位對應，支援隱含型別轉換
• NULL 關鍵字或空字串均可插入 NULL 值

-- 等價寫法
INSERT INTO customers(cust_id, state, post_code) VALUES ('Ariel', NULL, '94501');
INSERT INTO customers(cust_id, state, post_code) VALUES ('Ariel', , '94501');

2. UPDATE 修改資料

UPDATE order_rollup
SET (qty, price) = (SELECT SUM(qty), SUM(price) FROM order_lines WHERE customer_id='KOHL')
WHERE cust_id='KOHL' AND order_period=TO_DATE('01-Oct-2000');

3. DELETE 刪除資料

DELETE FROM po_lines
WHERE ship_to_state IN ('TX','NY','IL') AND order_date < ...;

4. TRUNCATE TABLE 快速清空表 (屬 DDL)

TRUNCATE TABLE [schema.]table [DROP|REUSE STORAGE];

與 DELETE 差異：

• 速度極快，不產生 UNDO，不可 Rollback
• 隱含 Commit，重設 High Water Mark 及索引
• 不觸發 DELETE Trigger
• 不可用於有 FK 參考的父表
• 清空後段大小歸零 (DROP STORAGE) 或保留 (REUSE STORAGE)

5. SELECT FOR UPDATE 列鎖定

查詢時加 FOR UPDATE 鎖定符合條件列，其他 Session 可查不可改。

6. LOCK TABLE 表鎖定

LOCK TABLE inventory IN EXCLUSIVE MODE;  -- 排他鎖
LOCK TABLE inventory IN SHARE MODE;      -- 共享鎖

7. 死結

雙向等待鎖釋放，Oracle 自動偵測拋出異常。

二、交易控制

範例：轉帳交易

BEGIN
  UPDATE checking SET balance=balance-5000 WHERE account='Kiesha';
  INSERT INTO checking_log VALUES (SYSDATE,'Transfer',-5000);
  UPDATE brokerage SET cash_balance=cash_balance+5000 WHERE account='Kiesha';
  INSERT INTO brokerage_log VALUES (SYSDATE,'Transfer',5000);
  COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK; END;

SAVEPOINT 部分回滾

BEGIN
  INSERT INTO atm_log VALUES ('Kiesha',SYSDATE,'Withdrawal','ATM54');
  SAVEPOINT atm_logged;
  UPDATE checking SET balance=balance-100 RETURN balance INTO new_bal;
  IF new_bal < 0 THEN
    ROLLBACK TO atm_logged;  -- 僅回滾扣款，保留 log
    COMMIT;
    RAISE insufficient_funds;
  END IF;
END;

一致性層級

• SET TRANSACTION ISOLATION LEVEL READ COMMITTED：Statement-level (預設)
• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE：Transaction-level
• SET TRANSACTION READ ONLY：唯讀交易，僅允許 SELECT/LOCK/SET ROLE/ALTER SYSTEM/ALTER SESSION

指定 Rollback Segment

SET TRANSACTION USE ROLLBACK SEGMENT rb_large;

大量作業預先指定大 Rollback Segment，避免動態擴展撐爆表空間。

三、使用者建立與屬性管理

驗證方式

• Database：資料庫驗證密碼 (預設)
• External：OS/網路驗證 (OPS$ 帳號)
• Global：目錄服務驗證 (X.509)

常用屬性設定

-- 建立使用者
CREATE USER piyush IDENTIFIED BY welcome
  DEFAULT TABLESPACE user_data
  TEMPORARY TABLESPACE temp_data
  QUOTA UNLIMITED ON user_data
  QUOTA 20M ON tools
  PROFILE app_user;

-- 修改屬性
ALTER USER manoj DEFAULT TABLESPACE dev1_data;
ALTER USER manoj QUOTA 2500K ON tools;
ALTER USER manoj PROFILE dev_profile;
ALTER USER manoj DEFAULT ROLE ALL EXCEPT salary_adm;
ALTER USER manoj PASSWORD EXPIRE;          -- 下次登入強制改密
ALTER USER ql ACCOUNT LOCK;                -- 鎖定帳號
ALTER USER ql ACCOUNT UNLOCK;              -- 解鎖

四、權限與角色體系

三類權限

• Object Privilege：物件層級 (SELECT, INSERT, UPDATE, DELETE, EXECUTE, ALTER, INDEX, REFERENCES...)
• System Privilege：系統層級 (CREATE TABLE, CREATE USER, ALTER SESSION...)
• Role：角色 (權限集合，便於管理)

建立角色

CREATE ROLE role_name [IDENTIFIED BY password | IDENTIFIED EXTERNALLY | IDENTIFIED GLOBALLY];

• 預設無密碼，建立即啟用
• IDENTIFIED BY 需 SET ROLE role_name IDENTIFIED BY pwd 啟用，適合應用程式內嵌安全

物件權限對照表 (節錄)

ALL 權限 = 該物件所有適用權限 (如 TABLE 的 ALL = SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,REFERENCES)。

系統權限授予語法

GRANT system_priv TO user|role|PUBLIC [WITH ADMIN OPTION];

注意：系統權限用 WITH ADMIN OPTION，物件權限用 WITH GRANT OPTION，混用會報錯。

角色權限特性

• 角色可授予角色/使用者
• PL/SQL 程式單元 (Procedure/Function/Package/Trigger) **不繼承角色權限**，必須直接授予物件權限給擁有者

授予/撤銷語法

GRANT role|sys_priv TO grantee [WITH ADMIN OPTION];
GRANT obj_priv ON object TO grantee [WITH GRANT OPTION];
REVOKE role|sys_priv FROM grantee;
REVOKE obj_priv ON object FROM grantee;

五、權限資料字典視圖

考試重點：DBA_TAB_PRIVS 包含表、函數、套件、佇列等所有物件權限，不限於表。