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

Oracle SQL 語法與數據對象參考手冊：DML、DDL、SELECT 核心語法與最佳實踐

編輯：Oracle 資料庫基礎

一、數據操作語言 (DML)

1. INSERT

-- 單行顯式列
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

-- 子查詢插入
INSERT INTO table_name (col1, col2, ...)
SELECT col1, col2, ... FROM another_table WHERE ...;

-- 多表插入 (12c+)
INSERT ALL
  INTO t1 (c1) VALUES (v1)
  INTO t2 (c2) VALUES (v2)
SELECT ... FROM dual;

字符串用單引號：'GOOD DAY'，單引號轉義：''（兩個單引號）。
日期：SYSDATE 或 TO_DATE('2024-07-09','YYYY-MM-DD')。
長字符串 (>4000) 用 CLOB + DBMS_LOB。
自增主鍵：seq_name.NEXTVAL（見自增列文章）。

2. DELETE

DELETE FROM table_name WHERE condition;

-- 全表清空（高速、釋放空間、DDL、不可回滾）
TRUNCATE TABLE table_name;

DELETE 只標記塊未用，不釋放空間，產生 UNDO/REDO。
大表分批 DELETE ... WHERE rownum <= 10000 循環 + COMMIT。

3. UPDATE

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

未賦值列變 NULL，建議前置非空檢查。
長度超限報錯，建議前置長度校驗。

4. 事務控制與鎖行為

DML 隱式獲取行級鎖（TX），COMMIT 釋放，ROLLBACK 回滾。
大批量操作限定範圍（建議 < 1 萬行/批），避免 UNDO 爆滿、鎖升級、響應超時。
分段提交模式：
LOOP DELETE ... WHERE rownum <= 10000; EXIT WHEN SQL%ROWCOUNT = 0; COMMIT; END LOOP;

二、數據定義語言 (DDL)

1. CREATE

-- 常用類型
col_name CHAR(n)           -- 固定長度，補空格
col_name VARCHAR2(n)       -- 變長，不補空格
col_name NUMBER(p,s)       -- p 總位數，s 小數位
col_name DATE              -- 日期時間（秒級）
col_name TIMESTAMP         -- 高精度時間戳
col_name CLOB / BLOB       -- 大對象

-- 創建表最佳實踐
CREATE TABLE orders (
  order_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- 12c+
  cust_id    NUMBER NOT NULL REFERENCES customers(cust_id),
  order_dt   DATE DEFAULT SYSDATE NOT NULL,
  amount     NUMBER(12,2) CHECK (amount > 0),
  status     VARCHAR2(20) DEFAULT 'NEW' CHECK (status IN ('NEW','PAID','SHIPPED','CANCELLED')),
  remarks    VARCHAR2(4000)
) TABLESPACE users PCTFREE 10;

2. ALTER

-- 重命名表
ALTER TABLE old_name RENAME TO new_name;
-- 或 RENAME old_name TO new_name;

-- 增加列
ALTER TABLE table_name ADD (col_name datatype [DEFAULT val]);

-- 修改列
ALTER TABLE table_name MODIFY (col_name datatype [DEFAULT val] [NULL|NOT NULL]);

-- 添加約束
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (col);
ALTER TABLE table_name ADD CONSTRAINT uk_name UNIQUE (col);
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES ref_table(ref_col);

-- 緩存控制
ALTER TABLE table_name CACHE;      -- 全表掃描放入 LRU 熱端
ALTER TABLE table_name NOCACHE;    -- 默認行為

3. DROP

DROP TABLE table_name CASCADE CONSTRAINTS;  -- 連同約束一起刪

4. TRUNCATE

TRUNCATE TABLE table_name;  -- 保留結構，瞬間清空，DDL

三、查詢語句 (SELECT)

1. 基礎結構

SELECT col1, col2, ...
FROM table1 [alias1]
[JOIN table2 alias2 ON alias1.col = alias2.col]
[WHERE condition]
[GROUP BY col1, col2 ...]
[HAVING condition]
[ORDER BY col1 [ASC|DESC], col2 ...]
[FETCH FIRST n ROWS ONLY];  -- 12c+ 分頁標準語法

2. 常用函數

函數	說明
`COUNT(*)`	行數（含 NULL）
`COUNT(col)`	非 NULL 行數
`MIN/MAX/AVG/SUM`	聚合
`DISTINCT(col)`	去重
`TO_CHAR(date, 'fmt')`	日期轉字符
`NVL(expr1, expr2)`	expr1 為 NULL 返回 expr2
`DECODE(val, v1, r1, v2, r2, ..., default)`	等值分支（Oracle 專有）
`CASE WHEN cond THEN res ... ELSE def END`	標準分支（推薦）
`LPAD(str, n, pad)`	左填充至 n 長度
`ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)`	分組排序編號（去重、Top-N）