Oracle資料庫入門：DDL 與資料庫物件完整指南

資料庫物件概覽

Oracle 資料庫中的核心物件包括：

• 表：儲存資料的基本單位，由列（欄位）與列（記錄）組成。
• 約束條件：強制執行資料驗證規則，確保資料完整性。
• 視圖：基於一或多個表衍生的邏輯資料呈現，不佔用實體儲存空間。
• 索引：針對特定欄位建立的有序結構，用於加速查詢效能。
• 序列：系統自動產生的唯一整數序列，常用於主鍵值生成。
• 同義詞：物件的別名，簡化跨使用者存取或縮短物件名稱。

命名規範

• 必須以字母開頭，可包含字母、數字、底線、$、#。
• 同一綱要（使用者）下物件名稱不可重複。
• 不可使用 Oracle 保留字。

表的分類

• 使用者定義表：由使用者建立並維護，存放業務資料。
• 資料字典表：由 Oracle 自動建立維護，儲存資料庫中繼資訊。

建立表

前置條件

• 具備 CREATE TABLE 權限。
• 有可用的儲存空間（表空間配額）。

基本語法

CREATE TABLE [schema.]table_name (
    column_name datatype [DEFAULT expr] [, ...]
);

執行後不可回滾（屬 DDL）。

範例：預設值

CREATE TABLE scott.test1 (
    name       VARCHAR2(20),
    hire_date  DATE DEFAULT SYSDATE,
    salary     NUMBER(8,2) DEFAULT 0
);

若插入時未指定 hire_date，自動取系統時間；salary 預設為 0。數值型欄位若無預設值，預設為 NULL 而非 0。

子查詢建表 (CTAS)

CREATE TABLE [schema.]table_name [(column_alias, ...)] AS subquery;

• 新表欄位清單可省略，自動採用子查詢結果欄位名。
• 子查詢中的運算式必須指定別名，否則報錯。

-- 非法：運算式無別名
CREATE TABLE myemp2 AS SELECT empno, ename, sal*12 FROM emp;

-- 合法
CREATE TABLE myemp2 AS SELECT empno, ename, sal*12 AS annsal FROM emp;

-- 指定新表欄位名
CREATE TABLE myemp (emp_id, emp_name, ann_sal) AS SELECT empno, ename, sal*12 FROM emp;

修改表結構

使用 ALTER TABLE，屬 DDL，執行後不可回滾。

新增欄位

ALTER TABLE table_name ADD (
    column_name datatype [DEFAULT expr] [, ...]
);

新欄位只能加在表尾。

ALTER TABLE test1 ADD (
    grade NUMBER(3),
    phone VARCHAR2(20) DEFAULT '無'
);

修改欄位

ALTER TABLE table_name MODIFY (
    column_name datatype [DEFAULT expr] [, ...]
);

• 可變更資料型別、長度、預設值，不可修改欄位名稱。
• 修改預設值僅對後續新增記錄生效。
• 若欄位已有資料，型別/長度變更可能因資料不相容而失敗；僅當所有值為 NULL 時可自由變更。

ALTER TABLE test1 MODIFY (
    grade NUMBER(2),
    phone VARCHAR2(15) DEFAULT '010-12345678'
);

刪除欄位

ALTER TABLE table_name DROP (column_name [, ...]);

釋放資料區塊中佔用的儲存空間。

ALTER TABLE test1 DROP (grade, phone);

清空與刪除表

操作	指令	特性
清空資料	TRUNCATE TABLE table_name;	DDL，不可回滾，釋放儲存空間，保留表結構
刪除表	DROP TABLE table_name;	DDL，不可回滾，刪除資料、索引、約束，提交未完成交易

DELETE 為 DML，可條件刪除、支援交易回滾，不釋放空間。

重命名表

RENAME old_name TO new_name;

僅物件擁有者可執行，亦適用於視圖、序列、同義詞等物件。

RENAME test1 TO test88;

資料字典

Oracle 核心元件，由一組唯讀表與視圖組成，擁有者為 SYS，記錄資料庫結構、物件定義、空間分配、完整性約束、使用者、角色、權限、稽核記錄等。

常用視圖分類

• USER_*：當前使用者擁有的物件。
• ALL_*：當前使用者可存取的物件。
• DBA_*：所有使用者的物件（需 DBA 權限）。

實用查詢範例

-- 當前使用者所有表名
SELECT table_name FROM user_tables;

-- 可存取的所有表名
SELECT table_name FROM all_tables;

-- 所有使用者所有表名
SELECT table_name FROM dba_tables;

-- 當前使用者所有物件類型
SELECT DISTINCT object_type FROM user_objects;

-- 當前使用者所有約束資訊
SELECT * FROM user_constraints;

-- 特定表的約束資訊
SELECT * FROM user_constraints WHERE table_name = 'STUDENT';

約束條件

五大類型

類型	完整性分類	說明
NOT NULL	域完整性	欄位不可為空
UNIQUE	實體完整性	欄位值唯一，允許 NULL
PRIMARY KEY	實體完整性	唯一且非空，每表僅一個
FOREIGN KEY	參照完整性	維護父子表參照關係
CHECK	域完整性	自訂條件驗證

約束命名

• 未指定名稱時，系統自動以 SYS_Cn 格式命名。
• 建議命名慣例：表名_欄位名_類型縮寫，如 student_birth_nn。

建立與管理

-- 建表時定義（欄位層級）
CREATE TABLE student (
    sid   NUMBER(3) NOT NULL,
    name  VARCHAR2(20),
    birth DATE CONSTRAINT nn_birth NOT NULL
);

-- 建表時定義（表層級）
CREATE TABLE student (
    sid  NUMBER(3),
    name VARCHAR2(20),
    CONSTRAINT student_sid_pk PRIMARY KEY (sid)
);

-- 建表後新增
ALTER TABLE stu ADD CONSTRAINT stu_sid_pk PRIMARY KEY (sid);
ALTER TABLE stu ADD PRIMARY KEY (sid);  -- 系統自動命名

-- 非空約束必須用 MODIFY
ALTER TABLE stu MODIFY (name NOT NULL);
ALTER TABLE stu MODIFY (name CHAR(8) DEFAULT 'N/A' NOT NULL);

-- 刪除約束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
ALTER TABLE table_name DROP PRIMARY KEY;  -- 僅主鍵可用

-- 級聯刪除相關約束
ALTER TABLE table_name DROP CONSTRAINT constraint_name CASCADE;

-- 刪除欄位時級聯刪除相關約束
ALTER TABLE table_name DROP (column_name) CASCADE CONSTRAINTS;

-- 停用/啟用約束
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name [CASCADE];
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

各約束詳細說明

NOT NULL

• 僅能在欄位層級定義。
• 一個表可定義多個。

UNIQUE

• 可在欄位或表層級定義。
• 允許 NULL 值（多筆 NULL 不視為重複）。
• Oracle 自動建立唯一索引。
• 複合唯一鍵只能用表層級定義。

-- 欄位層級
CREATE TABLE student (sid NUMBER(3) UNIQUE, name VARCHAR2(20));

-- 表層級
CREATE TABLE student (
    sid  NUMBER(3),
    name VARCHAR2(20),
    CONSTRAINT student_sid_un UNIQUE (sid)
);

-- 複合唯一鍵
CREATE TABLE scores (
    a NUMBER(3), b VARCHAR2(20), c NUMBER(4),
    CONSTRAINT scores_a_b_un UNIQUE (a, b)
);

PRIMARY KEY

• 功能等同於 NOT NULL + UNIQUE。
• 每表僅一個，可為單欄或複合欄位。
• 自動建立唯一索引。
• 複合主鍵只能用表層級定義，所有參與欄位皆不可為 NULL。

CREATE TABLE scores (
    a NUMBER(3), b VARCHAR2(20), c NUMBER(3),
    CONSTRAINT scores_a_b_pk PRIMARY KEY (a, b)
);

FOREIGN KEY

• 確保子表欄位值存在於父表主鍵或唯一鍵中，或為 NULL。
• 父表被參照記錄不可刪除（除非定義 ON DELETE CASCADE）。

-- 父表
CREATE TABLE info (
    id   NUMBER(3) PRIMARY KEY,
    name VARCHAR2(20),
    job  VARCHAR2(20),
    birth DATE
);

-- 子表：欄位層級
CREATE TABLE sal (
    a NUMBER(3) REFERENCES info(id),
    b NUMBER(8,2)
);

-- 子表：表層級
CREATE TABLE sal (
    a NUMBER(3),
    b NUMBER(8,2),
    CONSTRAINT sal_a_fk FOREIGN KEY (a) REFERENCES info(id)
);

-- 同時設為主鍵與外鍵（防止重複與 NULL）
CREATE TABLE sal (
    a NUMBER(3) PRIMARY KEY REFERENCES info(id),
    b NUMBER(8,2)
);

CHECK

• 僅能在欄位層級定義。
• 條件運算式不可包含 CURRVAL, NEXTVAL, LEVEL, ROWNUM 等偽欄，或 SYSDATE, UID, USER, USERENV 等函數，也不可參考其他欄位值。

CREATE TABLE test1 (
    name VARCHAR2(20) CHECK (LENGTH(name) >= 6),
    age  NUMBER(3) CHECK (age BETWEEN 0 AND 120)
);

-- 等同於 NOT NULL
CREATE TABLE test1 (
    name VARCHAR2(20) CHECK (name IS NOT NULL),
    age  NUMBER(3)
);

視圖

特性

• 虛擬表，不儲存實體資料，定義儲存於資料字典。
• 簡化複雜查詢、隱藏表結構、實現邏輯獨立性、限制資料存取、提供不同視角共享資料。
• 可對視圖執行 DML（需符合可更新條件）。

建立語法

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.]view_name
    [(alias [, alias] ...)] AS subquery
    [WITH CHECK OPTION [CONSTRAINT constraint_name]]
    [WITH READ ONLY];

關鍵選項

• OR REPLACE：同名視圖存在時覆蓋，保留權限。
• FORCE：基表或欄位不存在時仍強制建立（不建議常用）。
• WITH READ ONLY：建立唯讀視圖。
• WITH CHECK OPTION：確保透過視圖修改的資料符合視圖定義條件。

範例

-- 簡單視圖
CREATE OR REPLACE VIEW v1 (emp_id, emp_name, salary) AS
SELECT empno, ename, sal FROM emp WHERE deptno = 20;

-- 強制建立（基表 emp2 不存在）
CREATE OR REPLACE FORCE VIEW v2 AS
SELECT empno, ename, job, sal FROM emp2 WHERE deptno = 20;

-- 複雜視圖（含連接與運算）
CREATE OR REPLACE VIEW v_emp (emp_id, emp_name, job, ann_sal, tenure_mon, dept_no, dept_name) AS
SELECT e.empno, e.ename, e.job, e.sal*12,
       MONTHS_BETWEEN(SYSDATE, e.hiredate),
       e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

查詢與管理

-- 查詢視圖（每次執行子查詢，即時反映基表異動）
SELECT * FROM v1;

-- 檢視結構
DESC v1;

-- 刪除視圖
DROP VIEW v1;

可更新視圖限制

• 定義中不可含分組函數、GROUP BY、DISTINCT、ROWNUM、運算式欄位。
• 多基表連接視圖不可更新。
• 基表中未包含於視圖的 NOT NULL 欄位，導致 INSERT 失敗。
• 實務上不建議透過視圖更新基表資料。

臨時視圖

內嵌於 SQL 陳述式中的子查詢（如 CTAS、Top-N 分析），非資料庫物件，執行結束即銷毀，類似 Java 匿名類別。

索引

分類與建立

類型	建立時機	語法
唯一性索引	定義 PK 或 UK 時自動建立	系統自動
非唯一索引	使用者手動建立以加速查詢	CREATE INDEX idx_name ON table(col [, col]...);

CREATE INDEX myidx ON emp(ename);

刪除

DROP INDEX idx_name;

刪除表時，相關索引與約束自動刪除，視圖與序列保留。

建立原則

適合建立	不適合建立
欄位值分布廣、含大量 NULL	表極小
常出現在 WHERE 或 JOIN 條件	欄位很少用於查詢條件
大表且單次存取 < 2%-4% 筆數	單次存取 > 2%-4% 筆數（全表掃描較快）
	表頻繁大量更新
	被索引欄位常作為運算式一部分引用

函數索引

CREATE INDEX idx_emp_lower ON emp(LOWER(ename));
SELECT * FROM emp WHERE LOWER(ename) = 'king';  -- 條件必須與索引定義完全一致

查詢索引資訊

SELECT * FROM user_indexes;
SELECT * FROM user_ind_columns;

序列

特性

• 系統自動產生不重複整數，可跨使用者共享。
• 典型用途：主鍵值生成，避免應用層編號效能瓶頸。
• 支援快取（CACHE）提升效能，預設 CACHE 20。

建立語法

CREATE SEQUENCE [schema.]seq_name
    [INCREMENT BY n] [START WITH n]
    [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}]
    [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}] [{ORDER | NOORDER}];

參數說明

• INCREMENT BY：遞增值，預設 1（負值為遞減）。
• START WITH：起始值，預設 1，建立後不可修改。
• MAXVALUE/MINVALUE：極限值，實際上限約 10^27。
• CYCLE：達極限後是否循環，預設 NOCYCLE（建議不循環避免重複）。
• CACHE：預先產生號碼備用，預設 20；快取號碼可能因異常中斷而不連續。
• ORDER：保證多併發請求下號碼順序，預設 NOORDER。

使用方式

-- 取得下一值
SELECT myseq.NEXTVAL FROM dual;

-- 取得當前值（需先在該 Session 用過 NEXTVAL）
SELECT myseq.CURRVAL FROM dual;

-- 實務應用：插入主鍵
INSERT INTO test1 VALUES (myseq.NEXTVAL, 'Tom');

修改與刪除

ALTER SEQUENCE seq_name
    [INCREMENT BY n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}]
    [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}] [{ORDER | NOORDER}];

DROP SEQUENCE seq_name;

• 修改僅影響未來產生的值；MAXVALUE 不得小於當前值。
• 刪除需為擁有者或具 DROP ANY SEQUENCE 權限。

同義詞

用途

• 提供物件別名，簡化跨綱要存取。
• 縮冗長物件名稱。

建立與管理

-- 私有同義詞（僅當前綱要可用）
CREATE SYNONYM gt1 FOR emp;

-- 公用同義詞（所有使用者可用，需 DBA 權限）
CREATE PUBLIC SYNONYM pub_emp FOR scott.emp;

-- 使用
SELECT * FROM gt1;

-- 刪除
DROP SYNONYM gt1;
DROP PUBLIC SYNONYM pub_emp;