萬盛學電腦網

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

Oracle DDL 與資料庫物件完整入門:建表、約束、視圖、索引、序列、同義詞

Oracle 資料庫物件包含表、視圖、索引、序列、同義詞、約束等。本文系統整理 DDL 建立、修改、刪除、重命名語法與最佳實務。

命名規則

  • 以字母開頭,可含字母、數字、_、$、#,長度 ≤ 128 位元組 (12cR2 起 128 字元)。
  • 同一 Schema 物件名唯一,避免保留字 (需雙引號強制使用)。

建立表 (CREATE TABLE)

CREATE TABLE [schema.]table (
  col1 datatype [DEFAULT expr] [constraint],
  col2 datatype,
  ...
  [table_constraint]
);

範例

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

子查詢建表 (CTAS)

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

修改表結構 (ALTER TABLE)

操作語法限制
新增欄位ADD (col datatype [DEFAULT expr])只能加到最後
修改欄位MODIFY (col datatype [DEFAULT expr])不可改名;有資料時型態/長度變更受限
刪除欄位DROP (col1, col2...)釋放空間;12c+ 可 DROP UNUSED COLUMNS
設定未使用SET UNUSED (col)標記未用,稍後批次釋放空間

清空與刪除表

  • TRUNCATE TABLE t;:DDL、不可回滾、釋放 High Water Mark、重設序號 (Identity)。
  • DROP TABLE t [CASCADE CONSTRAINTS] [PURGE];:移除表、索引、約束、觸發器;PURGE 略過回收筒。

重命名

RENAME old_name TO new_name;  -- 表、視圖、序列、同義詞皆適用

約束條件 (Constraints)

類型關鍵字層級說明
非空NOT NULL欄位級唯一只能欄位級
唯一UNIQUE欄位/表級允許多個 NULL
主鍵PRIMARY KEY欄位/表級隱含 NOT NULL+UNIQUE,每表僅一個
外鍵FOREIGN KEY ... REFERENCES表級參照父表 PK/UK,可指定 ON DELETE CASCADE/SET NULL
檢查CHECK (condition)欄位/表級條件不可包含子查詢、SYSDATE、NEXTVAL 等

建表時加約束

CREATE TABLE stu (
  sid NUMBER PRIMARY KEY,
  name VARCHAR2(20) NOT NULL,
  dept_id NUMBER,
  CONSTRAINT fk_stu_dept FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);

事後新增/刪除/啟用/停用

ALTER TABLE stu ADD CONSTRAINT uk_stu_name UNIQUE (name);
ALTER TABLE stu DROP CONSTRAINT fk_stu_dept CASCADE;  -- 連帶刪除子表 FK
ALTER TABLE stu DISABLE CONSTRAINT uk_stu_name;
ALTER TABLE stu ENABLE CONSTRAINT uk_stu_name;

視圖 (VIEW)

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias,...)] AS subquery
[WITH CHECK OPTION [CONSTRAINT name]]
[WITH READ ONLY [CONSTRAINT name]];
  • FORCE:基表不存在也能建立。
  • WITH CHECK OPTION:DML 需符合視圖 WHERE 條件。
  • WITH READ ONLY:唯讀視圖。

索引 (INDEX)

CREATE [UNIQUE|BITMAP] INDEX idx_name ON table(col1, col2 DESC)
[TABLESPACE ts] [COMPRESS n] [ONLINE];
  • B*Tree (預設) 適合高基數;Bitmap 適合低基數 (DW 環境)。
  • ONLINE:11g+ 線上建立不鎖表。

序列 (SEQUENCE)

CREATE SEQUENCE seq_name
  INCREMENT BY 1 START WITH 1
  MAXVALUE 999999 MINVALUE 1
  CACHE 20 NOCYCLE;
  • CACHE:預先產生號碼放記憶體,效能佳但重啟會跳號。
  • NEXTVAL/CURRVAL 取值。

同義詞 (SYNONYM)

CREATE [PUBLIC] SYNONYM syn_name FOR [schema.]object_name;
  • Public 同義詞需 CREATE PUBLIC SYNONYM 權限,所有使用者可見。
  • 簡化跨 Schema 存取:SELECT * FROM emp_syn; 代替 SELECT * FROM scott.emp;。

資料字典查詢

-- 當前使用者物件
SELECT object_name, object_type FROM user_objects;
-- 所有可存取物件
SELECT * FROM all_tables;
-- DBA 檢視所有 (需 DBA 權限)
SELECT * FROM dba_tables;
-- 約束明細
SELECT * FROM user_constraints WHERE table_name='STU';
SELECT * FROM user_cons_columns WHERE table_name='STU';
copyright © 萬盛學電腦網 all rights reserved