Oracle修改列定義


在本教學中,您將學習如何使用Oracle ALTER TABLE MODIFY列語句來更改現有列的定義。

要更改表中列的定義,請按如下所示使用ALTER TABLE MODIFY列語法:

ALTER TABLE table_name 
MODIFY column_name action;

語句很直接。要修改表的列,需要指定要執行的列名,表名和操作。

Oracle允許執行多種操作,但以下是主要常用的操作:

  • 修改列的可見性
  • 允許或不允許NULL
  • 縮短或擴大列的大小
  • 更改列的預設值
  • 修改虛擬列的表示式

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

ALTER TABLE table_name
MODIFY (
    column_name_1 action,
    column_name_2 action,
    ...
);

Oracle ALTER TABLE MODIFY列範例

首先,為演示建立一個名為accounts的新表:

-- 12c語法
CREATE TABLE accounts (
    account_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(25) NOT NULL,
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(100),
    phone VARCHAR2(12) ,
    full_name VARCHAR2(51) GENERATED ALWAYS AS( 
            first_name || ' ' || last_name
    ),
    PRIMARY KEY(account_id)
);

其次,向accounts表中插入一些行:

INSERT INTO accounts(first_name,last_name,phone)
VALUES('Trinity',
       'Knox',
       '410-555-0197');


INSERT INTO accounts(first_name,last_name,phone)
VALUES('Mellissa',
       'Porter',
       '410-555-0198');


INSERT INTO accounts(first_name,last_name,phone)
VALUES('Leeanna',
       'Bowman',
       '410-555-0199');

第三,通過使用下面的SELECT語句驗證插入操作:

SELECT
    *
FROM
    accounts;

執行上面查詢語句,得到以下結果 -

1. 修改列的可見性

在Oracle 12c中,可以將表列定義為不可見或可見。不可見列不可用於查詢,如:

SELECT
    *
FROM
    table_name;

或者,

DESCRIBE table_name;

都是查到不到不可見列的。

但是,可以通過在查詢中顯式指定不可見列來查詢:

SELECT
    invisible_column_1,
    invisible_column_2
FROM
    table_name;

預設情況下,表列是可見的。可以在建立表或使用ALTER TABLE MODIFY列語句時定義不可見列。

例如,以下語句使full_name列不可見:

ALTER TABLE accounts 
MODIFY full_name INVISIBLE;

執行再次查詢表中資料,得到以下結果 -

以下語句返回accounts表除了full_name列以外的所有列中返回資料:

SELECT
    *
FROM
    accounts;

這是因為full_name列是不可見的。要將列從不可見變為可見,請使用以下語句:

ALTER TABLE accounts 
MODIFY full_name VISIBLE;

2. 允許或不允許null範例

以下語句將email列更改為接受非空(not null)值:

ALTER TABLE accounts 
MODIFY email VARCHAR2( 100 ) NOT NULL;

但是,Oracle發出以下錯誤:

SQL Error: ORA-02296: cannot enable (OT.) - null values found

因為當將列從可為null改為not null時,必須確保現有資料符合新約束(也就是說,如果原來資料中NULL是不行的)。

為了解決這個問題,首先更新email列的值:

UPDATE 
    accounts
SET 
    email = LOWER(first_name || '.' || last_name || '@oraok.com') ;

請注意,LOWER()函式將字串轉換為小寫字母。

然後改變email列的約束:

ALTER TABLE accounts 
MODIFY email VARCHAR2( 100 ) NOT NULL;

現在,它應該就會按預期那樣工作了。

3. 擴大或縮短列範例的大小

假設要新增國際程式碼到phone列上,比如:字首加上+86。 在修改列的值之前,必須使用以下語句擴大phone列的大小:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 24 );

現在,我們可以更新電話號碼的資料了:

UPDATE
    accounts
SET
    phone = '+86 ' || phone;

以下語句驗證更新:

SELECT
    *
FROM
    accounts;

執行上面查詢語句結果中,應該可以看到原電話號碼字首有加上+86的國際區號了。

要縮短列的大小,請確保列中的所有資料都符合新的大小。

例如,嘗試將phone列的大小縮減到12個字元:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 12 );

Oracle資料庫發出以下錯誤:

SQL Error: ORA-01441: cannot decrease column length because some  value is too big

要解決這個問題,首先,應該從電話號碼中刪除國際程式碼(即:+86):

UPDATE
    accounts
SET
    phone = REPLACE(
        phone,
        '+86 ',
        ''
    );

REPLACE()函式用一個新的子字串替換一個子字串。在這種情況下,它將用空字串替換+86

然後縮短phone列的大小:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 12 );

4. 修改虛擬列

假設按以下兩列的格式填寫全名:

last_name, first_name

為此,可以更改虛擬列full_name的表示式,如下所示:

ALTER TABLE accounts 
MODIFY full_name VARCHAR2(52) 
GENERATED ALWAYS AS (last_name || ', ' || first_name);

以下語句驗證修改:

SELECT
    *
FROM
    accounts;

執行上面查詢語句,可以看到以下結果 -

5. 修改列的預設值

新增一個名為status的新列,預設值為1accounts表中。參考以下語句 -

ALTER TABLE accounts
ADD status NUMBER( 1, 0 ) DEFAULT 1 NOT NULL ;

當執行了該語句,就會將accounts表中的所有現有行的status列中的值設定為1

要將status列的預設值更改為0,請使用以下語句:

ALTER TABLE accounts 
MODIFY status DEFAULT 0;

可以在accounts表中新增一個新行來檢查status列的預設值是0還是1

INSERT INTO accounts ( first_name, last_name, email, phone )
VALUES ( 'Julia',
         'Madden',
         '[email protected]',
         '410-555-0200' );

現在,查詢accounts表中的資料:

SELECT
  *
FROM
  accounts;

執行上面查詢語句,應該看類似下面的結果 -

正如所看到的那樣,ID4的賬戶的status列的值是0

在本教學中,您已學習如何使用OracleALTER TABLE MODIFY列語句來更改表中現有列的定義。