-- 基本語法
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')DBMS_LOBCREATE SEQUENCE seq_tbl INCREMENT BY 1 START WITH 1 MAXVALUE 999999,再 INSERT ... VALUES (seq_tbl.NEXTVAL, ...)DELETE FROM table_name WHERE condition;
注意:DELETE 不釋放空間(只標記 unused)。大量刪除全表請用 TRUNCATE TABLE table_name;(不可回滾、瞬間釋放空間)。
UPDATE table_name SET col1=val1, col2=val2, ... WHERE condition;
陷阱:未賦值欄位會變 NULL;長度超限報錯。建議先驗證再執行。
COMMIT:確認寫入ROLLBACK:復原COMMIT,避免 UNDO 爆滿導致效能驟降。| 型別 | 說明 |
|---|---|
| CHAR(n) | 固定長度字串,不足補空白 |
| VARCHAR2(n) | 變長字串,建議優先使用 |
| NUMBER(p,s) | p=總位數,s=小數位 |
| DATE | 日期時間,精確到秒 |
| TIMESTAMP | 含毫秒/時區 |
| CLOB/BLOB | 大物件 |
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 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 TABLE tbl CASCADE CONSTRAINTS; -- 連同約束一併刪除
TRUNCATE TABLE tbl; -- 保留結構、釋放空間、不可回滾
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 回傳 expr2 | NVL(comm, 0) |
| DECODE(val, v1, r1, v2, r2, ..., default) | 多值對照 | DECODE(deptno, 10, 'ACCT', 20, 'R&D', 'OTHER') |
| LPAD(str, n, pad) | 左補字元至長度 n | LPAD('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:交集-- 內連接(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%'
每列隱含唯一物理位址,可用於極速單列存取:SELECT * FROM tbl WHERE ROWID = '...'。
CREATE INDEX idx_tbl_col ON tbl (col1, col2);
ALTER INDEX idx_tbl_col REBUILD [ONLINE];
原則:單表索引 ≤ 3 個;優先單欄;依執行計畫建立複合/函數索引。字串索引長度上限約 DB_BLOCK_SIZE * 40%(8K 塊約 3.2K)。
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。
CREATE SYNONYM emp_syn FOR emp; -- 私有
CREATE PUBLIC SYNONYM emp_syn FOR emp; -- 公用
CREATE SYNONYM emp_syn FOR emp@dblink; -- 遠端表
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 查詢)。
CONNECT:建立 SessionRESOURCE:建立表/序列/程序等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,編碼時一鍵插入,減少拼寫錯誤、提升效率。