Oracle数据库批量变更字段类型

场景：

我有个项目使用Oracle数据库，运行几年后数据量较大，需要对数据库做一次优化，其中有些字段类型类型需要调整，这里分享一下实现步骤。

首先大家要知道Oracle数据库不允许修改有数据表的的字段类型，经过分析我选择下面的方式实现修改字段类型：

1、如果原字段叫A ，则创建一个新字段(A2)；

2、把原字段(A)的数据更新到A2；

3、删除原字段A;

4、把A2改名为A；

 以上是思路，以下是操作步骤 ：

第1步、因为阻止修改做了约束的字段，因此先删除所有约束，在删除之前我们要备份好约束，调整完数据类型还要还原约束。

-- 生成索引
SELECT T.TABLE_NAME, --表名
T.INDEX_NAME, --索引名
I.UNIQUENESS, --是否非空
I.INDEX_TYPE, --索引类型
C.CONSTRAINT_TYPE, --键类型
WM_CONCAT(T.COLUMN_NAME) COLS,
 
'ALTER TABLE ' || T.TABLE_NAME || ' DROP CONSTRAINT ' || T.INDEX_NAME ||';' 删除索引 ,

(CASE
WHEN C.CONSTRAINT_TYPE = 'P' OR C.CONSTRAINT_TYPE = 'R' THEN --主键和外键创建脚本拼接
'ALTER TABLE ' || T.TABLE_NAME || ' ADD CONSTRAINT ' ||
T.INDEX_NAME || (CASE
WHEN C.CONSTRAINT_TYPE = 'P' THEN
' PRIMARY KEY ('
ELSE
' FOREIGN KEY ('
END) || WM_CONCAT(T.COLUMN_NAME) || ');'
ELSE --索引创建脚本拼接
'CREATE ' || (CASE
WHEN I.UNIQUENESS = 'UNIQUE' THEN
I.UNIQUENESS || ' '
ELSE
CASE
WHEN I.INDEX_TYPE = 'NORMAL' THEN
''
ELSE
I.INDEX_TYPE || ' '
END
END) || 'INDEX ' || T.INDEX_NAME || ' ON ' || T.TABLE_NAME || '(' ||
WM_CONCAT(COLUMN_NAME) || ');'
END) 添加索引 
FROM USER_IND_COLUMNS T, USER_INDEXES I, USER_CONSTRAINTS C
WHERE T.INDEX_NAME = I.INDEX_NAME
AND T.INDEX_NAME = C.CONSTRAINT_NAME(+)--自建表规则（只查询自己创建的表【我的建表规则以TB_开头】，排除系统表）
AND I.INDEX_TYPE != 'FUNCTION-BASED NORMAL' --排除基于函数的索引
GROUP BY T.TABLE_NAME,
T.INDEX_NAME,
I.UNIQUENESS,
I.INDEX_TYPE,
C.CONSTRAINT_TYPE;

 

上面的sql会生成“删除索引”和“添加索引”，   执行 删除索引  中的语句会删掉库中的所有约束。

第2步：生成修改字段类型的sql

select 
 x."fieldType",
'alter table '||x."tableName"||' add '||c.column_name||'_U2 '||
case LOWER(x."fieldType")
when 'dateTime' then 'date'  
when 'decimal'  then 'number(18,2)'
when 'tring'  then 'VARCHAR2(100)'
when 'int16'  then 'number(5)'
when 'int'  then 'number(9)'
when 'int32'  then 'number(9)'
when 'int64'  then 'number(19)'
else  '异常类型'
end ||';
update '||x."tableName"||' set '||c.column_name||'_U2='|| case when  c.column_name ='ZFID' then f_tonumber(SUBSTR(ZFID, 1,8)) else  c.column_name end  ||';
alter table '||x."tableName"||'  drop column '||c.column_name||';
alter table '||x."tableName"||' rename column '||c.column_name||'_U2 to '||c.column_name||';' s,

-- 'alter table '||x."tableName"||' modify '||x."fieldName"||' varchar2(50);' sql,
x."id",x."fieldName" , x."tableName",lower( c.data_type) 表类型,
lower(case when to_char( x."fieldType") like 'E_%' then 'int' else x."fieldType" end ) 实体类型,
x."fieldName",x."className",x."nameSpace",x."createDate" 
from user_tab_columns  c  
inner join XT_CLASS x 
on c.table_name = x."tableName" and c.column_name = x."fieldName"
where  1=1 and X."nameSpace"='RCSCloud.Models'  and  lower(x."tableName") not like 'v_%'

上面sql中用到的  xt_class是目标字段类型，我是从实体类中使用代码生成到xt_class表里的，这个过程需要您自己实现，xt_class表结构如下：

CREATE TABLE "XT_CLASS" 
   (	"tableName" VARCHAR2(50) NOT NULL ENABLE, 
	"fieldName" VARCHAR2(50) NOT NULL ENABLE, 
	"fieldTitle" VARCHAR2(255), 
	"fieldType" VARCHAR2(50), 
	"className" VARCHAR2(50), 
	"nameSpace" VARCHAR2(255), 
	"createDate" DATE, 
	"id" NUMBER(11,0) NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "IOTDATA"

第3步：把第2步生成的sql执行完 数据类型就调整好了，最后把第1步生成的“添加约束"的sql执行一遍 就完成了。

-end-