一、批量扩容varchar2类型字段
场景:旧库迁移到新库,旧库编码是GBK,新库是UTF-8,一个汉字GBK咱2个字节UTF-8占三个字节,故对varchar2类型字段扩容二分之一。
--字段长度小于2600的增加三分长度
select t_tables.table_name,
t_column.column_name,
t_column.data_length,
'alter table ' || t_column.table_name || ' modify ' ||
t_column.column_name || ' varchar2(' || t_column.data_length || ');' as alter_sqlstr_old,
'alter table ' || t_column.table_name || ' modify ' ||
t_column.column_name || ' varchar2(' ||
(t_column.data_length + ceil(t_column.data_length * 0.5)) || ');' as alter_sqlstr
from user_tab_columns t_column, user_tables t_tables
where t_column.table_name = t_tables.table_name
--and t_tables.table_name like '%BM_B%' -- 模糊匹配的表名
and t_column.data_length <= 2600 -- 可根据字段长度
and t_column.data_type = 'VARCHAR2' -- 可根据字段类型
-- and t_tables.table_name = 'RP_NW_HWXSYBG' -- 具体那张表
and t_column.column_name not LIKE '%ID%' --过滤字段
and t_column.column_name not LIKE '%STATUS%' --过滤字段
UNION
--字段长度大于2600的并且小4000直接设置为4000
select t_tables.table_name,
t_column.column_name,
t_column.data_length,
'alter table ' || t_column.table_name || ' modify ' ||
t_column.column_name || ' varchar2(' || t_column.data_length || ');' as alter_sqlstr_old,
'alter table ' || t_column.table_name || ' modify ' ||
t_column.column_name || ' varchar2(4000);' as alter_sqlstr
from user_tab_columns t_column, user_tables t_tables
where t_column.table_name = t_tables.table_name
--and t_tables.table_name like '%BM_B%' -- 模糊匹配的表名
and t_column.data_length > 2600 -- 可根据字段长度
and t_column.data_length < 4000 -- 可根据字段长度
and t_column.data_type = 'VARCHAR2' -- 可根据字段类型
-- and t_tables.table_name = 'RP_NW_HWXSYBG' -- 具体那张表
and t_column.column_name not LIKE '%ID%' --过滤字段
and t_column.column_name not LIKE '%STATUS%' --过滤字段
二、批量修改字段类型char类型改为varchar2类型
新版本的数据库char类型字段不足位数补空格,如字段定义为char(3),存数据只存了一个字符,数据库真实数据是一个字符加两个空格,所以把类型改了,并去掉空格。
--生成批量修改字段类型和批量修改数据去除空格sql
select t_tables.table_name,
t_column.column_name,
t_column.data_length,
'alter table ' || t_column.table_name || ' modify ' ||
t_column.column_name || ' varchar2(' ||t_column.data_length || ');' as alter_sqlstr,
'update ' || t_column.table_name ||' set '||t_column.column_name||' = trim('||t_column.column_name||');'
from user_tab_columns t_column, user_tables t_tables
where t_column.table_name = t_tables.table_name
and t_column.data_type = 'CHAR' -- 可根据字段类型
;