萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 核心 SQL 語法與資料物件完整參考手冊

一、資料操作語言 (DML)

1. INSERT 插入資料

-- 基本語法
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);
-- 從另一張表複製
INSERT INTO table_name (col1, col2, ...) SELECT col1, col2, ... FROM other_table;

關鍵細節:

  • 字串值必須用單引號,單引號本身用兩個單引號跳脫:'O''Reilly'
  • 日期用 SYSDATE 或 TO_DATE('2024-01-15','YYYY-MM-DD')
  • 超過 4000 字元請改用 CLOB + DBMS_LOB
  • 自增主鍵:先 CREATE SEQUENCE seq_tbl INCREMENT BY 1 START WITH 1 MAXVALUE 999999,再 INSERT ... VALUES (seq_tbl.NEXTVAL, ...)

2. DELETE 刪除資料

DELETE FROM table_name WHERE condition;

注意:DELETE 不釋放空間(只標記 unused)。大量刪除全表請用 TRUNCATE TABLE table_name;(不可回滾、瞬間釋放空間)。

3. UPDATE 更新資料

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

陷阱:未賦值欄位會變 NULL;長度超限報錯。建議先驗證再執行。

4. 交易控制

  • COMMIT:確認寫入
  • ROLLBACK:復原
  • 單次 DML 建議 < 1 萬筆;大批次請分批 COMMIT,避免 UNDO 爆滿導致效能驟降。

二、資料定義語言 (DDL)

常用資料型別

型別說明
CHAR(n)固定長度字串,不足補空白
VARCHAR2(n)變長字串,建議優先使用
NUMBER(p,s)p=總位數,s=小數位
DATE日期時間,精確到秒
TIMESTAMP含毫秒/時區
CLOB/BLOB大物件

CREATE TABLE 範例

CREATE TABLE emp (
  empno    NUMBER(6)    PRIMARY KEY,
  ename    VARCHAR2(50) NOT NULL,
  hiredate DATE         DEFAULT SYSDATE,
  deptno   NUMBER(3)    REFERENCES dept(deptno)
) TABLESPACE users;

ALTER TABLE 常用操作

-- 重命名表
ALTER TABLE old_name RENAME TO new_name;
-- 新增欄位
ALTER TABLE tbl ADD (col_name datatype [DEFAULT val] [CONSTRAINT ...]);
-- 修改欄位定義
ALTER TABLE tbl MODIFY (col_name new_datatype);
-- 加主鍵/唯一鍵
ALTER TABLE tbl ADD CONSTRAINT pk_tbl PRIMARY KEY (col);
ALTER TABLE tbl ADD CONSTRAINT uk_tbl_col UNIQUE (col);
-- 快取/不快取表
ALTER TABLE tbl CACHE | NOCACHE;

DROP / TRUNCATE

DROP TABLE tbl CASCADE CONSTRAINTS;  -- 連同約束一併刪除
TRUNCATE TABLE tbl;                  -- 保留結構、釋放空間、不可回滾

三、查詢語句 (SELECT) 精華

基本架構

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

常用函數速查

函數用途範例
NVL(expr1, expr2)expr1 為 NULL 回傳 expr2NVL(comm, 0)
DECODE(val, v1, r1, v2, r2, ..., default)多值對照DECODE(deptno, 10, 'ACCT', 20, 'R&D', 'OTHER')
LPAD(str, n, pad)左補字元至長度 nLPAD('5', 6, '0') → '000005'
TO_CHAR(date, 'fmt')日期轉字串TO_CHAR(hiredate,'YYYY-MM-DD HH24:MI:SS')
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)分組排序編號去重、Top-N 查詢必備

集合運算

  • UNION:合併並去重
  • UNION ALL:合併不去重(效能佳)
  • MINUS:差集
  • INTERSECT:交集

JOIN 語法

-- 內連接(ANSI 標準,建議)
SELECT ... FROM a JOIN b ON a.id = b.id;

-- 外連接(Oracle 舊式 (+) 與 ANSI 對照)
SELECT ... FROM a, b WHERE a.id = b.id(+);   -- 左外連
SELECT ... FROM a LEFT JOIN b ON a.id = b.id; -- 同上,可讀性佳

模糊比對

WHERE col LIKE 'abc%'        -- 以 abc 開頭
WHERE col LIKE '%abc%'       -- 含 abc
WHERE INSTR(col, 'abc') > 0  -- 同 LIKE '%abc%'

ROWID

每列隱含唯一物理位址,可用於極速單列存取:SELECT * FROM tbl WHERE ROWID = '...'。

四、常見 Schema 物件

1. 索引 (INDEX)

CREATE INDEX idx_tbl_col ON tbl (col1, col2);
ALTER INDEX idx_tbl_col REBUILD [ONLINE];

原則:單表索引 ≤ 3 個;優先單欄;依執行計畫建立複合/函數索引。字串索引長度上限約 DB_BLOCK_SIZE * 40%(8K 塊約 3.2K)。

2. 視圖 (VIEW)

CREATE VIEW v_emp_dept AS
SELECT e.empno, e.ename, d.dname
FROM   emp e JOIN dept d ON e.deptno = d.deptno;
ALTER VIEW v_emp_dept COMPILE;

視圖不佔儲存空間,僅儲存查詢定義,簡化複雜 JOIN。

3. 同義詞 (SYNONYM)

CREATE SYNONYM emp_syn FOR emp;                    -- 私有
CREATE PUBLIC SYNONYM emp_syn FOR emp;             -- 公用
CREATE SYNONYM emp_syn FOR emp@dblink;             -- 遠端表

4. 資料庫連結 (DATABASE LINK)

CREATE DATABASE LINK dblink_name
CONNECT TO usr IDENTIFIED BY pwd
USING 'tns_alias';
-- 查詢遠端
SELECT * FROM emp@dblink_name;

GLOBAL_NAMES=TRUE 時,DB Link 名稱必須等於遠端 GLOBAL_NAME(SELECT * FROM GLOBAL_NAME 查詢)。

五、權限管理 (DCL)

系統權限角色

  • CONNECT:建立 Session
  • RESOURCE:建立表/序列/程序等
  • DBA:完整管理權限

物件權限

GRANT SELECT, INSERT, UPDATE, DELETE ON tbl TO user1, user2;
GRANT ALL ON tbl TO role_x;
REVOKE SELECT ON tbl FROM user1;

結語

熟練掌握上述語法與物件操作,是 Oracle 開發與 DBA 的基本功。建議將常用片段存為 IDE Live Template 或 Snippet,編碼時一鍵插入,減少拼寫錯誤、提升效率。

copyright © 萬盛學電腦網 all rights reserved