【獨孤九劍】Oracle 實戰 (五) Table 與 View 物件完整操作指南

【獨孤九劍】Oracle 實戰 (五) Table 與 View 物件完整操作指南

Table 與 View 是資料庫核心物件。本章涵蓋常用資料型別、約束定義、表建立修改刪除、View 建立與 UNION/UNION ALL 效能差異。

1. 常用資料型別

2. 欄位約束 (建表時或 ALTER ADD)

3. 表操作實戰

建表與註解

CREATE TABLE person (
  id       NUMBER PRIMARY KEY,
  name     VARCHAR2(32),
  gender   CHAR DEFAULT '1'
) TABLESPACE users;

COMMENT ON TABLE person IS '人員表';
COMMENT ON COLUMN person.id IS '編號';
COMMENT ON COLUMN person.name IS '姓名';

修改表結構 (ALTER TABLE)

RENAME person TO people;                           -- 改表名
ALTER TABLE person RENAME COLUMN name TO xingming; -- 改欄名
ALTER TABLE person ADD phone VARCHAR2(11);         -- 新增欄
ALTER TABLE person ADD birthday DATE DEFAULT SYSDATE NOT NULL; -- 新增帶預設非空
ALTER TABLE person DROP COLUMN phone;              -- 刪欄
ALTER TABLE person MODIFY name VARCHAR2(64);       -- 擴充長度
ALTER TABLE person MODIFY gender CHAR(2);          -- 改精度
ALTER TABLE person MODIFY gender VARCHAR2(2);      -- 改型別
ALTER TABLE person MODIFY gender DEFAULT '2';      -- 改預設值

約束管理

-- 新增主鍵
ALTER TABLE person ADD CONSTRAINT pk_person_id PRIMARY KEY (id);
-- 新增外鍵
ALTER TABLE person ADD CONSTRAINT fk_person_dept FOREIGN KEY (dept_id) REFERENCES dept(id);
-- 新增檢查
ALTER TABLE person ADD CONSTRAINT ck_person_gender CHECK (gender IN ('M','F'));
-- 設非空 (需 MODIFY)
ALTER TABLE person MODIFY name CONSTRAINT nn_person_name NOT NULL;
-- 新增唯一
ALTER TABLE person ADD CONSTRAINT uq_person_name UNIQUE (name);
-- 設定預設值
ALTER TABLE person MODIFY birthday DATE DEFAULT SYSDATE;

-- 約束啟用/停用/延遲
ALTER TABLE person DISABLE CONSTRAINT uq_person_name;
ALTER TABLE person ENABLE CONSTRAINT uq_person_name;
ALTER TABLE person DROP CONSTRAINT fk_person_dept;
ALTER TABLE person ADD CONSTRAINT fk_person_dept FOREIGN KEY (dept_id)
  REFERENCES dept(id) DEFERRABLE INITIALLY DEFERRED;

清除/刪除表

TRUNCATE TABLE person;      -- 清空不可回滾，釋放 High Water Mark
DELETE FROM person WHERE id > 10; -- 可回滾，產生 Undo
DROP TABLE person;          -- 連結構一起刪除

4. View 視圖操作

建立視圖

CREATE OR REPLACE VIEW v_person AS
SELECT a.id, a.name, b.dept_name
  FROM person a, dept b
 WHERE a.dept_id = b.id;

關鍵字區別： PROCEDURE/FUNCTION 可用 IS/AS；VIEW 只能用 AS；CURSOR 只能用 IS。

UNION vs UNION ALL

• UNION：合併並 去重，需排序比較，效能較低。
• UNION ALL：直接合併 保留重複，無排序開銷，效能顯著更高。
• 確定無重複或允許重複時，優先使用 UNION ALL。

刪除視圖

DROP VIEW v_person;

實戰建議

• 欄位長度預留 20%~50% 緩衝，避免頻繁 MODIFY 鎖表。
ㅤ
• 大表新增 NOT NULL 欄位帶 DEFAULT 值 (11g+) 為瞬時完成，無需全表更新。
• 視圖簡化複雜 JOIN，但避免在視圖上再建視圖造成解析遞迴過深。