在oracle中,可以利用“ALTER TABLE MODIFY”语句来修改字段,语法为“ALTER TABLE 表名 MODIFY 字段名 需要执行的操作;”;常用操作有:修改列的可见性、更改列的默认值、修改虚拟列的表达式等。
本教程操作环境:Windows7系统、Oracle 11g版、Dell G3电脑。
oracle数据库怎么修改字段
在oracle中,可以利用“ALTER TABLE MODIFY
”语句来修改字段,更改现有字段的定义。
要更改表中列的定义,请按如下所示使用ALTER TABLE MODIFY
列语法:
ALTER TABLE 表名 MODIFY 字段名 需要执行的操作;
语句很直接。要修改表的列,需要指定要执行的列名,表名和操作。
Oracle允许执行多种操作,但以下是主要常用的操作:
修改列的可见性
允许或不允许NULL值
缩短或扩大列的大小
更改列的默认值
修改虚拟列的表达式
要修改多个列,请使用以下语法:
ALTER TABLE 表名 MODIFY ( 字段名1 action, 字段名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
的新列,默认值为1
到accounts
表中。参考以下语句 -
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;
执行上面查询语句,应该看类似下面的结果
正如所看到的那样,ID
为4
的账户的status
列的值是0
。
推荐教程:《Oracle教程》
以上就是oracle数据库怎么修改字段的详细内容,更多请关注自由互联其它相关文章!