Oracle數據庫批量變更字段類型的實現(xiàn)步驟

更新時間：2024年02月22日 09:14:14 作者：高彬

我有個項目使用Oracle數據庫,運行幾年后數據量較大,需要對數據庫做一次優(yōu)化,其中有些字段類型類型需要調整,這里分享一下實現(xiàn)步驟,感興趣的朋友可以參考下

場景：

我有個項目使用Oracle數據庫，運行幾年后數據量較大，需要對數據庫做一次優(yōu)化，其中有些字段類型類型需要調整，這里分享一下實現(xiàn)步驟。

思路：

首先大家要知道Oracle數據庫不允許修改有數據表的的字段類型，經過分析我選擇下面的方式實現(xiàn)修改字段類型：

1、如果原字段叫A ，則創(chuàng)建一個新字段(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 --主鍵和外鍵創(chuàng)建腳本拼接
'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 --索引創(chuàng)建腳本拼接
'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(+)--自建表規(guī)則（只查詢自己創(chuàng)建的表【我的建表規(guī)則以TB_開頭】，排除系統(tǒng)表）
AND I.INDEX_TYPE != 'FUNCTION-BASED NORMAL' --排除基于函數的索引
GROUP BY T.TABLE_NAME,
T.INDEX_NAME,
I.UNIQUENESS,
I.INDEX_TYPE,
C.CONSTRAINT_TYPE;

上面的sql會生成“刪除索引”和“添加索引”，執(zhí)行刪除索引中的語句會刪掉庫中的所有約束。

第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表里的，這個過程需要您自己實現(xiàn)，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執(zhí)行完數據類型就調整好了，最后把第1步生成的“添加約束"的sql執(zhí)行一遍就完成了。