Oracle_SQL审核_ALTER TABLE - MODIFY COLUMN

语法

ALTER TABLE 'owner'.'tabname' MODIFY 'colname' [ 'coltype(length)' [DEFAULT] [CONSTRAINT] | VISIBLE ];

语法示例

SQL> create table scott.emp(empno number(8),empname varchar2(32));
SQL> desc emp
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO                                                          NUMBER(8)
 EMPNAME                                                        VARCHAR2(32)

ALTER TABLE scott.emp MODIFY empno number(12);
ALTER TABLE scott.emp MODIFY (empno number(12),empname varchar2(64)...);
ALTER TABLE scott.emp MODIFY empno number(12) DEFAULT 0;
ALTER TABLE scott.emp MODIFY empno number(12) NOT NULL;
ALTER TABLE scott.emp MODIFY empno number(12) DEFAULT 0 NULL;
ALTER TABLE scott.emp MODIFY empno INVISIBLE;

概述

字段变更的需求看起来比较简单，但实际上，存在不少的坑;
除了变更字段的类型长度是否合理外，还需要考虑以下几方面：

• 通常只允许扩大字段长度，不建议缩小缩小长度;
• 需要确定是否只需要变更列出来的表中的字段;
• 如果涉及到CHAR，VARCHAR2的字符类型的转变，需要确认业务中是否需要对字段长度进行判定;
• 给字段新增默认值时，原字段中字段值不会受影响，不能通过扩字段时新增默认值来处理字段中的NULL值;

1、缩小字段长度

SQL> insert into scott.emp values('12345678','xiaoming'); 
SQL> ALTER TABLE scott.emp modify(empno number(6));

ORA-01440: column to be modified must be empty to decrease precision or scale

SQL> select max(length(empno)) from scott.emp;

MAX(LENGTH(EMPNO))
------------------
                 8

缩小字段长度不能小于表中字段的最大长度，否则会导致报错，一般不建议;

2、还有其他表中的字段也需要变更

业务上常用到的字段，都不会只存在于单一的表中，在很多表中都会存在，
因此有扩字段的需求时，需要考虑提供过来审核的变更脚本中，需要变更的表是否全面。

通过SQL，查出用户下的同名字段，看看是否都包含在变更脚本中，没包含在其中的需
要反馈给业务，判断是否需要一起变更。

SQL> create table scott.dept(deptno char(4),empname varchar2(32));
SQL> @same_cols
Enter value for owner: SCOTT
Enter value for column_name: EMPNAME

Table                          Column
Name                           Name                           DATA_TYPE                      DATA_LENGTH
------------------------------ ------------------------------ ------------------------------ -----------
EMP                            EMPNAME                        VARCHAR2                                32
DEPT                           EMPNAME                        VARCHAR2                                32

#SQL脚本 same_cols.sql
select table_name,column_name,data_type,data_length 
  from dba_tab_columns
 where owner = upper('&owner') and column_name=upper('&column_name')
 group by table_name,column_name,data_type,data_length
 order by table_name desc;

3、定长，变长字符类型的转变

char -> varchar2

char类型是定长，会给长度不够的字符串后面填充空格;
字段类型从char转变为varchar2时，已经被填充空格的字段值不会去掉空格;

SQL> insert into scott.dept values('010','xiaoming');
SQL> desc scott.dept 
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DEPTNO                                                         CHAR(4)
 EMPNAME                                                        VARCHAR2(32)

SQL> select length(deptno) from scott.dept where empname='xiaoming';

LENGTH(DEPTNO)
--------------
             4

SQL> alter table scott.dept modify deptno varchar2(4);
SQL> desc scott.dept 
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DEPTNO                                                         VARCHAR2(4)
 EMPNAME                                                        VARCHAR2(32)
SQL> select length(deptno) from scott.dept where empname='xiaoming';

LENGTH(DEPTNO)
--------------
             4

varchar2 -> char

字段类型从varchar2转变为char时，长度不足的字段值会被填充上空格;
使用length()来获取字段值长度时，会发现长度已经改变;

SQL> desc scott.dept
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DEPTNO                                                         VARCHAR2(4)
 EMPNAME                                                        VARCHAR2(32)

select length(deptno) from scott.dept where empname='xiaoming';

LENGTH(DEPTNO)
--------------
             4

SQL> alter table scott.dept modify deptno char(8);
SQL> desc scott.dept 
  Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DEPTNO                                                         CHAR(8)
 EMPNAME                                                        VARCHAR2(32)
SQL> select length(deptno) from scott.dept where empname='xiaoming';

LENGTH(DEPTNO)
--------------
             8

被填充了空格，字段值是否与原来不一样了呢?
试一下同一字符串末尾带空格与不带空格进行比较

SQL> select 1 from dual where '010'='010  ';
         1
----------
         1

可以发现，Oracle认为同一字符串的末尾带空格与不带空格是相等的;

4、给字段新增默认值

给字段新增默认值时，需要明确的是，表中该字段已经存在的字段值不会发生改变，原有的NULL值也不会被默认值所填充。

SQL> insert into scott.emp values ('87654321',null);
SQL> select * from scott.emp;

     EMPNO EMPNAME
---------- --------------------------------
  12345678 xiaoming
  87654321

SQL> alter table scott.emp modify empname default 'xiaoming';
SQL> @col_detail
Enter value for owner: SCOTT
Enter value for tabname: EMP
Enter value for colname: EMPNAME

Table                          Column                                                                               Null
Name                           Name                           DATA_TYPE                      DATA_LENGTH DATA_DEFAU able
------------------------------ ------------------------------ ------------------------------ ----------- ---------- ----
EMP                            EMPNAME                        VARCHAR2                                32 'xiaoming' Y

SQL> select * from scott.emp;

     EMPNO EMPNAME
---------- --------------------------------
  12345678 xiaoming
  87654321

SQL> insert into scott.emp values('',default);
SQL> select * from scott.emp;
     EMPNO EMPNAME
---------- --------------------------------
  12345678 xiaoming
  87654321
           xiaoming

#SQL脚本 col_detail.sql
select table_name,column_name,data_type,data_length,data_default,nullable 
  from dba_tab_columns
 where owner = upper('&owner') 
   and table_name = upper('&tabname') 
   and column_name=upper('&colname');