首发微信公众号:SQL数据库运维

原文链接:https://mp.weixin.qq.com/s?__biz=MzI1NTQyNzg3MQ==&mid=2247485212&idx=1&sn=450e9e94fa709b5eeff0de371c62072b&chksm=ea37536cdd40da7a94e165ce4b4c6e70fb1360d51bed4b3566eee438b587fa231315d0a5a5b3&token=1491694448&lang=zh_CN#rd


之前总结了ALTER TABLE的五种用法:Oracle中ALTER TABLE的五种用法(点击文字可跳转),现在以实例的方式详细讲解下ALTER TABLE ADD语句向表中添加一列或多列的用法。

语法

--在 ADD 之后指定要添加新列的名称,数据类型及其约束--添加一列ALTER TABLE table_name ADD column_name data_type constraint;--添加多列,在这个语法中,用逗号分隔两列ALTER TABLE table_name ADD (    column_name_1 data_type constraint,    column_name_2 data_type constraint,    ...);

首先创建一个测试表,脚本如下:

--我们本次使用的11g建立数据表语法(无自增)CREATE TABLE TEST(    ID INT,     NAME VARCHAR2(50),    SEX VARCHAR2(50),    PRIMARY KEY(ID));-- 12c语法,设置ID自增,在12c以下版本会报错CREATE TABLE TEST(    ID NUMBER GENERATED BY DEFAULT AS IDENTITY,    NAME VARCHAR2(50),    SEX VARCHAR2(50),    PRIMARY KEY(ID ));

以下语句将一个名为birth的新列添加到TEST表中,且不允许为空值:

ALTER TABLE TEST ADD birth DATE NOT NULL;

假如我们想记录一行的创建和更新的时间。那么可以再添加两列created_at和updated_at,如下所示:

ALTER TABLE    TEST ADD(        created_at TIMESTAMP WITH TIME ZONE NOT NULL,        updated_at TIMESTAMP WITH TIME ZONE NOT NULL    );

要检查表中是否存在列,可以从user_tab_cols视图查询数据。例如,以下语句将检查TEST表是否具有NAME 列。

SELECT    COUNT(*)FROM    user_tab_colsWHERE    column_name = 'NAME'    AND table_name = 'TEST';

当想在添加表之前检查列中是否存在列时,此查询就派上用场了。
例如,下面的PL/SQL块在使用PLSQL Developer工具添加之前检查TEST表是否有ADD_TMS列,如果有则输出“表中已存在要添加的列”,如果没有则直接添加。

--需要注意的是默认pl/sql不输出,需要开启下,--开启步骤:首选项——>Oracle——>输出——>勾选已开启--声明变量存储要查询的表中的列是否存在DECLARE  v_column_exists number;  BEGIN  --从系统表中查询表中的列是否存在  Select count(*) into v_column_exists    from user_tab_cols    where upper(column_name) = 'ADD_TMS'      and upper(table_name) = 'TEST';--如果不存在,使用快速执行语句添加ADD_TMS列  if (v_column_exists = 0) then      execute immediate 'alter table TEST add (ADD_TMS date)';   ELSE      dbms_output.put_line('表中已存在要添加的列');      end if;end;--oracle中斜杠(/)的含义斜杠就是让服务器执行前面所写的sql脚本/

打开微信搜索“SQL数据库运维”,点击关注“SQL数据库运维”,后台或浏览至公众号文章底部点击“发消息”回复关键字:进群,带你进入高手如云的技术交流群。后台回复关键字:SQL,获取学习资料。