Oracle数据库基础学习15-修改表

添加表列

表创建好后经常需要向表添加新的列，添加表列使用 alter table 语句

alter table [schema_name.]table_name
{
	add column_name data_type [column_attributes]
}

语法参数的含义如下所示：

table_name ：要修改的表名称，可选的 [schema_name] 用来指定方案名

add ：用来指定操作类型

column_name：用来指定表列的名称

data_type ：指定增加或修改的表列的数据类型

column_attributes：指定列的属性

为了演示添加表列的语法，以下代码在 SCOTT 方案下，创建了一个简单的图书表 BOOKS

create table BOOKS_LIB
(
	BOOK_ID NUMBER PRIMARY KEY,  --图书编号
	BOOK_NAME VARCHAR2(50) NOT NULL  --图书名称
);

接下来向该表中添加一个新的列 PUBLISHER 用来指定图书的出版公司

alter table BOOKS_LIB add PUBLISHER VARCHAR2(50);

由于添加表列的 alter table 语句无法指定列在表中出现的位置，因此新添加的列总是位于表中最后一列。如果表中已经具有了行数据，那么新添加的列的初始值将都为 NULL

alter table 语句除了一次性添加一列外，还可以在列定义中同时指定多个列进行添加，当同时添加两个列时，列与列之间使用逗号进行分隔，并且在 add 后面将列的定义包含在括号中

alter table BOOKS_LIB add 
(
	QTY NUMBER CHECK (QTY>0 and QTY<100),  --当前藏书数量
	ISBN VARCHAR2(20),  
	PUBLISH_DATE DATE DEFAULT sysdate  --出版日期
);

 

使用虚拟列

虚拟列又称为计算字段，它并不是表中具体存储的列，而实通过表达式或函数计算而得到的一个计算列。在 create table 语句或 alter table 语句中，可以通过在列属性后面指定 GENERATED ALWAYS AS 语句指定虚拟列，定义语法如下

column [datatype] [GENERATED ALWAYS] AS (column_expression) [VIRTUAL] [inline_constraint [inline_constraint]...]

GENERATED ALWAYS 用来指定该列并不会实际存储在磁盘上，而是在需要时通过计算得出。语法中的 column+erpression 用于指定一个计算表达式，虚拟列的列表达式可以使用任何合法的Oracle表达式语句或各种计算函数，也可以使用自定义的 PL/SQL 函数。

下面的示例创建了一个表 EMP_VIRTUAL ，该表演示了如何在员工表中创建一个虚拟列以便计算员工的实际提成数，员工的提成金额是根据员工的工资和员工的提成率计算得出的。

create table EMP_VIRTUAL 
(
	EMPNO VARCHAR2(10) PRIMARY KEY,  --员工编码
	SAL NUMBER(7,2),  --工资
	COMM_RCT NUMBER(7,2),  --提成率
	--虚拟列，提成金额
	COMM_SAL GENERATED ALWAYS as (SAL * COMM_RCT) VIRTUAL
);

Oracle会根据表达式自动计算出虚拟列的值，因此不允许向虚拟列插入值，也不可以手动的修改和指定虚拟列的值。当然虚拟列的使用也有很多限制，如下：

• 只能在标准表（堆组织表）上定义虚拟列，不能在索引表、外部表、临时表上创建虚拟列
• 虚拟列的类型由Oracle根据计算的结果自动决定，不能为用户自定义类型、大对象（LOB 或 RAW）类型的列创建虚拟列
• 不能引用其他表中的列来创建虚拟列，虚拟列中的所有列必须属于相同的表
• 虚拟列的表达式必须返回一个标量值，且虚拟列表达式不能使用其他的虚拟列
• 不能对虚拟列做 insert 或 update 操作，但是可以在虚拟列上建立索引

对于已经创建的表，可以通过 alter table 语句来添加一个虚拟列：

create table SCOTT_EMP_CP as select * from EMP;

alter table SCOTT_EMP_CP add (COMM_SAL GENERATED ALWAYS as (SAL+COMM));

 

修改表列

alter table 的 MODIFY 子句用来对一个现有的表列进行修改，可以更改列的数据类型、长度和默认值，其使用详细语法如下：

alter table table_name
MODIFY
(
	column_name datatype [default expr]
	[, column_name datatype]...
);

其中 table_name 指定要修改的表名，在 MODIFY 的括号中，可以同时使用逗号分隔的方式修改多个表列，如果不使用括号，则表示修改单个列

注意：MODIFY 修改了列的默认值后，影响的是表中新插入的行，对于现有行中的值，不会造成任何影响。但是新增加约束时，如果现有的值不符合约束的条件，Oracle将弹出错误提示

在修改列时，必须要注意不是任何列都可以随意修改，下面是用于修改列的一些需要知道的原则：

• 可以增大字符类型列的长度或数值类型列的精度
• 如果表中所有列的字符型的长度或数值型的精度匹配要缩小的值，那么也可以缩小字符类型的长度或数值类型的精度，否则Oracle会弹出错误提示
• 如果更改列的数据类型，相关列的值必须为NULL
• 如果不减小长度，可以把数据类型从 CHAR 更改为 VARCHAR2 或者是将 VARCHAR2 更改为 CHAR 类型，即便相关的列值不为空也是可以的

 

删除表列

在Oracle中，提供了如下两种用来删除表列的方法：

• 直接删除表列：这会导致表列以及表列中的数据被永久删除，同时释放所占用的存储空间
• 将列标记为不可用状态：将列标记为 UNUSED 状态，列中的数据并未被真正删除，但是与删除表列的效果相同，在数据字典中无法查到该表列，该列上的所有依赖对象都被删除

直接删除表列

可以使用 alter table ..drop 语句直接将一个表列从表中删除

alter table [schems.]table_name drop column (column_name) [cascade constraints]

在 drop 后面的 COLUMN 可以省略，如果要同时删除多个列，可以在括号中使用逗号分隔的方式指定多个列名，如果删除的列是一个多列约束的组成部分，那么必须指定 cascade constraints 选项，这样才会删除相关的约束

将列标记为不可用状态

如果要删除的列中包含大量的数据，那么删除操作可能需要执行很长的时间，这可能会影响部分用户对表的使用，这时就可以用到，将列标记不可用状态

alter table [schems.]table_name set unused (column_name) [cascade constraints]

虽然无法查询到被设置未 UNUSED 的列，但是实际上它们依然存储在数据库中，可以在以后适当的时间对这些列进行删除，其语法如下：

alter table [schems.]table_name drop unused columns;

如果向知道在数据库表中有哪些表被标记了 UNUSED ，可以通过数据字典视图 USER_UNUSED_COL_TABS 来进行查询

 

重命名表列

可以使用 alter table ... rename column 命令方便的重命名表列，这是一个比较少见的操作，主要是因为对字段的重命名可能会影响到数据库视图或 PL/SQL 程序的异常等

alter table [schems.]table_name rename column old_name to new_name

重命名列之后，Oracle会处理列的依赖性关系，比如依赖原有列的检查约束或者是索引在重命名后的列上依然有效，但是依赖于该列的视图、触发器、函数、过程和包将会失效

 

重命名表

重命名表使用 RENAME 语句，其语法如下：

rename old_name to new_name;

当重命名一个表时，Oracle会自动完成完整性约束、索引及相关权限由旧对象转换到新对象，同时Oracle会验证所有依赖于该对象的权限到重命名之后的表。

注意：只能用 RENAME 语句更改自己方案中对象的名字，还有一种方法，可以指定方案名来修改其他方案中的表，其语法如下

alter table [schema.]old_name rename to new_name;

 

删除数据表

删除数据库表会删除该表中所有的数据，连同该表在数据字典中的元数据定义。删除表使用 drop table 语句，其语法如下：

drop table [schema.]table_name [cascade constraints] [PURGE]

在drop table 语句中，除非指定了 PURGE 子句，否则并不会立即删除表，Oracle只是简单的重命名此表并将其存储到回收站中。

对于外部表来说，drop table 语句仅是从数据库移除元数据，并不会移除它指向的具体数据文件。cascade constraints 用来移除表上的所有主键和外键约束。