快捷導(dǎo)航

Oracle數(shù)據(jù)庫(kù)批量變更字段類(lèi)型的實(shí)現(xiàn)步驟

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

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

場(chǎng)景：

我有個(gè)項(xiàng)目使用Oracle數(shù)據(jù)庫(kù)，運(yùn)行幾年后數(shù)據(jù)量較大，需要對(duì)數(shù)據(jù)庫(kù)做一次優(yōu)化，其中有些字段類(lèi)型類(lèi)型需要調(diào)整，這里分享一下實(shí)現(xiàn)步驟。

思路：

首先大家要知道Oracle數(shù)據(jù)庫(kù)不允許修改有數(shù)據(jù)表的的字段類(lèi)型，經(jīng)過(guò)分析我選擇下面的方式實(shí)現(xiàn)修改字段類(lèi)型：

1、如果原字段叫A ，則創(chuàng)建一個(gè)新字段(A2)；

2、把原字段(A)的數(shù)據(jù)更新到A2；

3、刪除原字段A;

4、把A2改名為A；

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

操作步驟

第1步、因?yàn)樽柚剐薷淖隽思s束的字段，因此先刪除所有約束，在刪除之前我們要備份好約束，調(diào)整完數(shù)據(jù)類(lèi)型還要還原約束。

-- 生成索引
SELECT T.TABLE_NAME, --表名
T.INDEX_NAME, --索引名
I.UNIQUENESS, --是否非空
I.INDEX_TYPE, --索引類(lèi)型
C.CONSTRAINT_TYPE, --鍵類(lèi)型
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ī)則（只查詢(xún)自己創(chuàng)建的表【我的建表規(guī)則以TB_開(kāi)頭】，排除系統(tǒng)表）
AND I.INDEX_TYPE != 'FUNCTION-BASED NORMAL' --排除基于函數(shù)的索引
GROUP BY T.TABLE_NAME,
T.INDEX_NAME,
I.UNIQUENESS,
I.INDEX_TYPE,
C.CONSTRAINT_TYPE;

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

第2步：生成修改字段類(lèi)型的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  '異常類(lèi)型'
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) 表類(lèi)型,
lower(case when to_char( x."fieldType") like 'E_%' then 'int' else x."fieldType" end ) 實(shí)體類(lèi)型,
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是目標(biāo)字段類(lèi)型，我是從實(shí)體類(lèi)中使用代碼生成到xt_class表里的，這個(gè)過(guò)程需要您自己實(shí)現(xiàn)，xt_class表結(jié)構(gòu)如下：

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í)行完數(shù)據(jù)類(lèi)型就調(diào)整好了，最后把第1步生成的“添加約束"的sql執(zhí)行一遍就完成了。

最后

到此這篇關(guān)于Oracle數(shù)據(jù)庫(kù)批量變更字段類(lèi)型的實(shí)現(xiàn)步驟的文章就介紹到這了,更多相關(guān)Oracle批量變更字段類(lèi)型內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: