Oracle 常用 SQL 語法與資料庫物件完整參考手冊 (DML/DDL/SELECT/DCL)

一、資料操作語言 (DML)

1. INSERT 插入資料

-- 基本語法
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

-- 從另一張表複製
INSERT INTO table_name (col1, col2, ...) SELECT col1, col2, ... FROM other_table;

• 字串值用單引號 'GOOD DAY'，單引號本身雙寫轉義 ''。
• 日期可用 SYSDATE 或 TO_DATE('2001-08-01','YYYY-MM-DD')。
• 單次 INSERT 字串長度 ≤ 4000 bytes；超過請用 CLOB + DBMS_LOB。
• 自動遞增序列：
  CREATE SEQUENCE seq_name INCREMENT BY 1 START WITH 1 MAXVALUE 999999 CYCLE NOCACHE;
  插入時用 seq_name.NEXTVAL。

2. DELETE 刪除資料

DELETE FROM table_name WHERE condition;

• 注意：DELETE 不釋放資料區塊 (High Water Mark 不下降)，僅標記為 unused。
• 需清空大表並釋放空間請用 TRUNCATE TABLE table_name; (DDL、不可回滾、不觸發 Trigger)。

3. UPDATE 更新資料

UPDATE table_name SET col1=val1, col2=val2, ... WHERE condition;

• 未賦值欄位會變 NULL，建議先檢核非空。
• 長度超過定義會報錯。

4. 交易控制

• COMMIT 確認交易；ROLLBACK 回滾。
• 大量異動 (萬筆以上) 建議分批 COMMIT，避免 Undo Segment 爆滿導致效能劣化。

二、資料定義語言 (DDL)

常用資料型別

• CHAR(n) 固定長度字串
• VARCHAR2(n) 變動長度字串 (建議優先使用)
• NUMBER(p,s) 數值，p=總位數，s=小數位數
• DATE 日期時間 (含時分秒)

CREATE / ALTER / DROP 範例

-- 建表建議：NOT NULL 欄位前置、可 NULL 後置；可用 DEFAULT SYSDATE
CREATE TABLE t (id NUMBER(10) PRIMARY KEY, name VARCHAR2(50) NOT NULL, cdate DATE DEFAULT SYSDATE);

-- 修改表名
ALTER TABLE old_name RENAME TO new_name;

-- 新增欄位
ALTER TABLE t ADD (remark VARCHAR2(200));

-- 修改欄位定義
ALTER TABLE t MODIFY (name VARCHAR2(100));

-- 加主鍵/唯一鍵
ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (id);
ALTER TABLE t ADD CONSTRAINT uk_t_name UNIQUE (name);

-- 快取/非快取表
ALTER TABLE t CACHE;      -- 小表放入 Keep Buffer Pool
ALTER TABLE t NOCACHE;

-- 刪除表及所有約束
DROP TABLE t CASCADE CONSTRAINTS;

-- 清空表 (保留結構、釋放空間、不可回滾)
TRUNCATE TABLE t;

三、查詢語句 (SELECT)

基本架構

SELECT col1, col2, ...
FROM table1, [table2, ...]
WHERE condition
GROUP BY col1 [HAVING condition]
ORDER BY col1 [ASC|DESC], col2 [ASC|DESC];

常用函數

• 聚合：COUNT(*), MIN/MAX/AVG(col), DISTINCT(col)
• 日期格式：TO_CHAR(date_col, 'YYYY-MM-DD HH24:MI:SS')
• NVL(expr1, expr2)：expr1 為 NULL 回傳 expr2
• DECODE(val, v1, r1, v2, r2, ..., default)：類似 CASE WHEN
• LPAD(str, n, pad_str)：左補字元至指定長度

集合運算

• UNION (去重)、UNION ALL (保留重複)
• MINUS (差集)、INTERSECT (交集)

連接查詢

-- 內連接
SELECT ... FROM a, b WHERE a.id = b.id;

-- 外連接 (Oracle 老語法，建議改用 ANSI JOIN)
SELECT ... FROM a, b WHERE a.id = b.id(+);  -- 右外連接

模糊比對

WHERE INSTR(col, 'keyword') > 0
WHERE col LIKE 'keyword%' OR col LIKE '%keyword%'

ROWID

每列隱含 ROWID 唯一標識該列物理位置。

四、常用 Schema 物件

1. 索引 (INDEX)

CREATE INDEX idx_name ON table_name (col1, col2);
ALTER INDEX idx_name REBUILD;  -- 重建索引消除碎片

• 單表索引建議 ≤ 3 個 (特大表除外)；優先單欄索引，依執行計畫再考慮複合索引或函數索引。
• 字串索引長度上限約 DB_BLOCK_SIZE * 40% (8K block 約 3200 bytes)。

2. 視圖 (VIEW)

CREATE VIEW v_name AS SELECT ... FROM ...;
ALTER VIEW v_name COMPILE;

視圖本質是儲存的查詢，可簡化複雜關聯。

3. 同義詞 (SYNONYM)

CREATE SYNONYM syn_name FOR table_name;
CREATE SYNONYM syn_name FOR table_name@dblink_name;

4. 資料庫連結 (DATABASE LINK)

CREATE DATABASE LINK dblink_name
CONNECT TO user IDENTIFIED BY pwd
USING 'tns_connect_string';

• GLOBAL_NAMES=TRUE 時 DB Link 名稱必須等於遠端 GLOBAL_NAME (SELECT * FROM GLOBAL_NAME;)。
• 查詢遠端表：SELECT ... FROM table@dblink;

五、權限控制 (DCL)

系統權限角色

• CONNECT (基本連線、建表等)
• RESOURCE (建立程序/觸發器/型別等)
• DBA (完整管理權限)

物件權限

GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON table_name TO user1, user2;
GRANT CONNECT, RESOURCE TO user_name;

REVOKE SELECT ON table_name FROM user_name;
REVOKE CONNECT, RESOURCE FROM user_name;