Oracle修改表結構


在本教學中,我們將學習如何使用Oracle ALTER TABLE語句來修改表結構。

要修改現有表的結構,請使用ALTER TABLE語句。 以下說明語法:

ALTER TABLE table_name action;

在上面的語句中,

  • 首先,指定要修改的表名稱。
  • 其次,指出想在表名稱後執行的操作。

ALTER TABLE語句可用來:

  • 新增一個或多個列
  • 修改列定義
  • 刪除一列或多列
  • 重新命名列
  • 重新命名表

下面來看看一些例子來了解每個操作的工作原理。

Oracle ALTER TABLE範例

我們將使用在上一個教學中建立的persons表進行演示。

-- 12c語法
CREATE TABLE persons(
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(person_id)
);

1. Oracle ALTER TABLE ADD列的例子

要將新列新增到表中,請使用以下語法:

ALTER TABLE table_name
ADD column_name type constraint;

例如,以下語句將一個名為birthdate的新列新增到persons表中:

ALTER TABLE persons 
ADD birthdate DATE NOT NULL;

如果檢視persons表格,會看到birthdate日期列被新增到列列表的末尾:

DESC persons; 

Name       Null     Type         
---------- -------- ------------ 
PERSON_ID  NOT NULL NUMBER       
FIRST_NAME NOT NULL VARCHAR2(50) 
LAST_NAME  NOT NULL VARCHAR2(50) 
BIRTHDATE  NOT NULL DATE

要同時向表中新增多列,請按如下所示將新列置於括號內:

ALTER TABLE table_name
ADD (
    column_name type constraint,
    column_name type constraint,
    ...
);

看下面的例子:

ALTER TABLE persons 
ADD (
    phone VARCHAR(20),
    email VARCHAR(100)
);

在這個例子中,語句在users表中新增了兩個名為phoneemail的新列。

DESC persons

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
BIRTHDATE  NOT NULL DATE          
PHONE               VARCHAR2(20)  
EMAIL               VARCHAR2(100)

2. Oracle ALTER TABLE MODIFY列範例

要修改列的屬性,請使用以下語法:

ALTER TABLE table_name
  MODIFY column_name type constraint;

例如,下面的語句將birthdate列更改為一個空列:

ALTER TABLE persons MODIFY birthdate DATE NULL;

我們再次驗證persons表結構:

DESC persons

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
BIRTHDATE           DATE          
PHONE               VARCHAR2(20)  
EMAIL               VARCHAR2(100)

正如所看到的,birthdate日期變得無能為力。

要修改多個列,請使用以下語法:

 ALTER TABLE table_name
  MODIFY ( column_1 type constraint,
          column_1 type constraint,
         ...);

例如,以下語句將phoneemail列更改為NOT NULL列,並將email列的長度擴充套件為255個字元:

ALTER TABLE persons MODIFY(
    phone VARCHAR2(20) NOT NULL,
    email VARCHAR2(255) NOT NULL
);

再次驗證persons表結構:

DESC persons;

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
BIRTHDATE           DATE          
PHONE      NOT NULL VARCHAR2(20)  
EMAIL      NOT NULL VARCHAR2(255)

3. Oracle ALTER TABLE DROP COLUMN範例

要從表中刪除現有的列,請使用以下語法:

ALTER TABLE table_name
DROP COLUMN column_name;

該語句從表結構中刪除列,並刪除儲存在該列中的資料。以下範例從persons表中刪除birthdate日期型別的列:

ALTER TABLE persons
DROP
  COLUMN birthdate;

再次檢視persons表結構,就會發現birthdate列已被刪除:

DESC persons;

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
PHONE      NOT NULL VARCHAR2(20)  
EMAIL      NOT NULL VARCHAR2(255)

要同時刪除多個列,請使用以下語法:

ALTER TABLE table_name 
DROP (column_1,column_2,...);

例如,以下語句從個人表中刪除phoneemail列:

ALTER TABLE persons
DROP
  ( email, phone );

我們再來看一下persons表:

DESC persons;

Name       Null     Type         
---------- -------- ------------ 
PERSON_ID  NOT NULL NUMBER       
FIRST_NAME NOT NULL VARCHAR2(50) 
LAST_NAME  NOT NULL VARCHAR2(50)

emailphone列已按預期刪除。

4. Oracle ALTER TABLE RENAME列範例

從版本9i開始,Oracle新增了一個用於重新命名列的子句,如下所示:

ALTER TABLE table_name
RENAME COLUMN column_name TO new_name;

例如,以下語句將first_name列重新命名為forename列:

ALTER TABLE persons 
RENAME COLUMN first_name TO forename;

以下語句檢查結果:

DESC persons;

Name      Null     Type         
--------- -------- ------------ 
PERSON_ID NOT NULL NUMBER       
FORENAME  NOT NULL VARCHAR2(50) 
LAST_NAME NOT NULL VARCHAR2(50)

5. Oracle ALTER TABLE RENAME表的例子

要將一個表重新命名為一個新的name的表名,使用下面的語法:

ALTER TABLE table_name
RENAME TO new_table_name;

例如,下面的語句將users表重新命名為people表:

ALTER TABLE persons RENAME TO people;

在本教學中,您已學習如何使用Oracle ALTER TABLE語句來更改現有表的結構。