2020-04-22Oracle中将VARCHAR2的字段乘1.

将Oracle进行数据迁移，因为字符集发生变化GBK变为UTF-8，因为GBK中汉字是占两个字符，UTF-8中是占3个字符，所以要对VARCHAR2长度进行1.5倍的扩大。参考SQL如下

SELECT
    'alter table ' || a.table_name || ' modify (' || a.column_name || ' VARCHAR2(' ||
CASE
    WHEN ceil( data_length * 1.5 ) >= 4000 
    THEN 4000 
    ELSE ceil( data_length * 1.5 ) 
    END || '));' 
FROM
    all_tab_columns a 
WHERE
    data_type = 'VARCHAR2' 
    AND DATA_LENGTH != 4000 
    AND a.TABLE_NAME = '传入一个表名' 
    AND TABLE_NAME IN ( SELECT object_name FROM user_objects WHERE object_type = 'TABLE' ) 
ORDER BY
    TABLE_NAME,
    COLUMN_NAME;

以表ABCABC举例，此表中有个字段MACHINE是VARCHAR2(64)类型的，运行以下SQL

SELECT
    'alter table ' || a.table_name || ' modify (' || a.column_name || ' VARCHAR2(' ||
CASE
    WHEN ceil( data_length * 1.5 ) >= 4000 
    THEN 4000 
    ELSE ceil( data_length * 1.5 ) 
    END || '));' 
FROM
    all_tab_columns a 
WHERE
    data_type = 'VARCHAR2' 
    AND DATA_LENGTH != 4000 
    AND a.TABLE_NAME = 'ABCABC' 

得到以下SQL，就可以直接修改了

Alter table ABCABC modify(MACHINE VARCHAR2(96));