您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle 11g 表結構操作全攻略：建立、修改、約束、重命名、刪除

編輯：Oracle 教學

Oracle 11g 表結構操作全攻略：建立、修改、約束、重命名、刪除

連線與模式切換

# 設定 SID
export ORACLE_SID=ORCL

# 進入 SQL*Plus
sqlplus /nolog

-- 1. 普通用戶模式 (SCOTT)
CONN scott/123
SHOW USER

-- 2. SYSDBA 模式 (最高權限)
CONN / AS SYSDBA
SHOW USER

-- 3. SYSOPER 模式 (操作權限)
CONN sys/123456 AS SYSOPER
SHOW USER

常見連線錯誤：ORA-12560

原因	解決方法
監聽器未啟動	Windows: services.msc 啟動 OracleTNSListener Linux: lsnrctl start
資料庫實例未啟動	Windows: services.msc 啟動 OracleService Linux: sqlplus 啟動實例
環境變數 ORACLE_SID 未設定/錯誤	設定系統變數 ORACLE_SID=正確SID值

一、建立表 (CREATE TABLE)

1. 基本建表含約束

CREATE TABLE productinfo (
  ProductId    VARCHAR2(10) CONSTRAINT P_Id PRIMARY KEY,
  ProductName  VARCHAR2(10) NOT NULL,
  ProductPrice NUMBER(8,2)  CONSTRAINT P_Price CHECK (ProductPrice BETWEEN 0 AND 1000),
  Quantity     NUMBER(10)
);

2. 全域暫存表 (Global Temporary Table)

-- 交易級：COMMIT/ROLLBACK 時清空資料
CREATE GLOBAL TEMPORARY TABLE temp_transaction (
  ID   NUMBER(2) PRIMARY KEY,
  Name VARCHAR2(20)
) ON COMMIT DELETE ROWS;

-- 會話級：斷開連線時清空資料
CREATE GLOBAL TEMPORARY TABLE temp_session (
  ID   NUMBER(2) PRIMARY KEY,
  Name VARCHAR2(20)
) ON COMMIT PRESERVE ROWS;

3. 子查詢建表 (CTAS)

CREATE TABLE P_select (P_Id, P_Name)
AS
SELECT ProductId, ProductName
  FROM Productinfo
 WHERE ProductPrice > 10;

二、表約束操作

操作	語法範例
新增唯一約束	ALTER TABLE productinfo ADD CONSTRAINT P_Name UNIQUE (ProductName);
刪除唯一約束	ALTER TABLE productinfo DROP UNIQUE (ProductName); 或: ALTER TABLE productinfo DROP CONSTRAINT P_Name;
啟用約束	ALTER TABLE productinfo ENABLE CONSTRAINT P_Price;
停用約束	ALTER TABLE productinfo DISABLE CONSTRAINT P_Price;
查詢約束資訊	SELECT constraint_name, constraint_type, status FROM user_constraints WHERE table_name = 'PRODUCTINFO';

三、修改表結構 (ALTER TABLE)

操作	語法範例	備註
新增欄位	ALTER TABLE productinfo ADD (Category NUMBER(6));
修改欄位型別	ALTER TABLE productinfo MODIFY Category VARCHAR2(10);	需確保現有資料相容
修改欄位名稱	ALTER TABLE productinfo RENAME COLUMN Category TO Desperation;	11g+ 支援
刪除欄位	ALTER TABLE productinfo DROP (Desperation);	11g+ 支援 DROP COLUMN
修改儲存參數	ALTER TABLE productinfo PCTFREE 10 PCTUSED 70;	影響新資料填充
移動表 (重組)	ALTER TABLE productinfo MOVE;	釋放碎片、變更表空間
重命名表	RENAME productinfo TO product; ALTER TABLE product RENAME TO productinfo;	兩種語法皆可

四、表與欄位註釋

COMMENT ON TABLE productinfo IS '商品資訊主檔';
COMMENT ON COLUMN productinfo.ProductName IS '商品名稱';
COMMENT ON COLUMN productinfo.ProductPrice IS '單價(元)';

五、刪除表 (DROP TABLE)

-- 基本刪除 (進回收桶 11g+)
DROP TABLE productinfo;

-- 徹底刪除 (不進回收桶)
DROP TABLE productinfo PURGE;

-- 清空回收桶
PURGE RECYCLEBIN;

完整測試腳本範例

-- 1. 建立測試表
CREATE TABLE demo_emp (
  empno    NUMBER(4) PRIMARY KEY,
  ename    VARCHAR2(20) NOT NULL,
  job      VARCHAR2(15),
  hiredate DATE DEFAULT SYSDATE,
  sal      NUMBER(7,2) CHECK (sal > 0),
  deptno   NUMBER(2)
);

-- 2. 新增欄位與約束
ALTER TABLE demo_emp ADD (email VARCHAR2(50));
ALTER TABLE demo_emp ADD CONSTRAINT uk_email UNIQUE (email);

-- 3. 修改欄位
ALTER TABLE demo_emp MODIFY ename VARCHAR2(30);
ALTER TABLE demo_emp RENAME COLUMN job TO position;

-- 4. 註釋
COMMENT ON TABLE demo_emp IS '員工測試表';
COMMENT ON COLUMN demo_emp.position IS '職位';

-- 5. 驗證
DESC demo_emp
SELECT * FROM user_constraints WHERE table_name = 'DEMO_EMP';
SELECT * FROM user_col_comments WHERE table_name = 'DEMO_EMP';

-- 6. 清理
DROP TABLE demo_emp PURGE;