数据库教程-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; 

执行上面查询语句,得到以下结果 –

Oracle修改列定义

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; 

执行再次查询表中数据,得到以下结果 –

Oracle修改列定义

以下语句返回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的国际区号了。

Oracle修改列定义

要缩短列的大小,请确保列中的所有数据都符合新的大小。

例如,尝试将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; 

执行上面查询语句,可以看到以下结果 –
Oracle修改列定义

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',          'julia.madden@oraok.com',          '410-555-0200' ); 

现在,查询accounts表中的数据:

SELECT   * FROM   accounts; 

执行上面查询语句,应该看类似下面的结果 –
Oracle修改列定义

正如所看到的那样,ID4的账户的status列的值是0

在本教程中,您已学习如何使用OracleALTER TABLE MODIFY列语句来更改表中现有列的定义。

  

从零到一,创造未来!跨零综合IT问题解决服务站,欢迎你的到来。Oracle数据库教程 只为你绽放。

本文固定链接: http://kua0.com/2019/01/18/数据库教程-oracle修改列定义/

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注