您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle SQL四大語言分類與DML/DDL核心操作實戰指南

編輯：Oracle 資料庫基礎

SQL四大語言分類架構

類別	全稱	核心指令	核心職能
DQL	Data Query Language	SELECT	資料查詢
DML	Data Manipulation Language	INSERT/UPDATE/DELETE/MERGE	資料操縱
DDL	Data Definition Language	CREATE/ALTER/DROP/TRUNCATE/RENAME	結構定義
DCL	Data Control Language	GRANT/REVOKE	權限控制

本文深度聚焦DML與DDL實戰操作。

一、DML核心操作詳細解析

1. INSERT資料插入

-- 完整欄位列表（推薦，結構變更不影響）
INSERT INTO customers(cust_id, state, post_code) 
VALUES ('ARIEL', NULL, '94501');

-- 省略欄位（按COLUMN_ID順序，風險高）
INSERT INTO customers VALUES ('ARIEL', NULL, '94501');

-- 子查詢插入
INSERT INTO dept_summary(deptno, emp_cnt, avg_sal)
SELECT deptno, COUNT(*), AVG(sal) FROM emp GROUP BY deptno;

關鍵規則： 值數量/型別與欄位對應、隱式轉換風險、NULL關鍵字與空字串語意不同。

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');

-- 多表更新（Oracle專有）
UPDATE (
  SELECT e.sal AS old_sal, b.bonus_rate
  FROM emp e, bonus_policy b
  WHERE e.deptno = b.deptno
) SET old_sal = old_sal * (1 + bonus_rate);

3. DELETE資料刪除

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

風險控制： 生產環境DELETE前必須先SELECT驗證、備份邏輯備份、考慮WHERE ROWNUM <= N分批刪除。

4. TRUNCATE TABLE：DDL層面的極速清空

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

特性	DELETE	TRUNCATE
語言類別	DML	DDL
速度	逐行刪除，產生UNDO/REDO	極快，重置High Water Mark
回滾	可ROLLBACK	不可回滾（隱式COMMIT）
觸發器	觸發DELETE觸發器	不觸發
高水位線	不重置	重置至初始
外鍵約束	受限制	父表不可TRUNCATE
空間釋放	DROP STORAGE預設釋放	REUSE STORAGE保留

5. SELECT FOR UPDATE：行級鎖定

SELECT * FROM emp WHERE deptno = 10 FOR UPDATE [NOWAIT|WAIT n|SKIP LOCKED];

鎖定查詢行，阻塞其他Session UPDATE/DELETE/SELECT FOR UPDATE，允許單純SELECT。

6. LOCK TABLE：表級鎖定

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

阻塞該表上所有DML操作，適用於批次維護窗口。

二、DDL核心操作與最佳實踐

物件建立與修改原則

命名規範： 統一前綴、有意義名稱、長度<30字元
儲存參數： 明確指定TABLESPACE、PCTFREE、INITRANS、STORAGE子句
約束策略： PK/FK/UK/NOT NULL/CHECK盡在建表時定義，啟用索引支援
註釋文檔： COMMENT ON TABLE/COLUMN ... IS '...' 建立數據字典

常見DDL操作模式

-- 增加欄位（線上操作，11g+支援DEFAULT值元數據更新）
ALTER TABLE emp ADD (email VARCHAR2(100) DEFAULT 'unknown@company.com');

-- 修改欄位（注意資料相容性）
ALTER TABLE emp MODIFY (salary NUMBER(10,2) NOT NULL);

-- 刪除欄位（12c+支援SET UNUSED延遲物理刪除）
ALTER TABLE emp DROP COLUMN old_flag;

-- 重命名
RENAME old_table TO new_table;
ALTER TABLE emp RENAME COLUMN ename TO emp_name;