Oracle數(shù)據(jù)庫批量變更字段類型的實現(xiàn)步驟
場景:
我有個項目使用Oracle數(shù)據(jù)庫,運(yùn)行幾年后數(shù)據(jù)量較大,需要對數(shù)據(jù)庫做一次優(yōu)化,其中有些字段類型類型需要調(diào)整,這里分享一下實現(xiàn)步驟。
思路:
首先大家要知道Oracle數(shù)據(jù)庫不允許修改有數(shù)據(jù)表的的字段類型,經(jīng)過分析我選擇下面的方式實現(xiàn)修改字段類型:
1、如果原字段叫A ,則創(chuàng)建一個新字段(A2);
2、把原字段(A)的數(shù)據(jù)更新到A2;
3、刪除原字段A;
4、把A2改名為A;
以上是思路,以下是操作步驟 :
操作步驟
第1步、因為阻止修改做了約束的字段,因此先刪除所有約束,在刪除之前我們要備份好約束,調(diào)整完數(shù)據(jù)類型還要還原約束。
-- 生成索引 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' --排除基于函數(shù)的索引 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是目標(biāo)字段類型,我是從實體類中使用代碼生成到xt_class表里的,這個過程需要您自己實現(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ù)類型就調(diào)整好了,最后把第1步生成的“添加約束"的sql執(zhí)行一遍 就完成了。
最后
到此這篇關(guān)于Oracle數(shù)據(jù)庫批量變更字段類型的實現(xiàn)步驟的文章就介紹到這了,更多相關(guān)Oracle批量變更字段類型內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Windows server 2019安裝Oracle 19c的圖文教程
Oracle是是一款高效、可靠、吞吐量高的數(shù)據(jù)庫,本文主要介紹了Windows server 2019安裝Oracle 19c的圖文教程,具有一定的參考價值,感興趣的可以了解一下2024-04-04oracle 存儲過程、函數(shù)和觸發(fā)器用法實例詳解
這篇文章主要介紹了oracle 存儲過程、函數(shù)和觸發(fā)器用法,結(jié)合實例形式詳細(xì)分析了oralce 存儲過程、函數(shù)和觸發(fā)器具體功能、原理、定義、使用方法及相關(guān)操作注意事項,需要的朋友可以參考下2020-02-02詳解Oracle數(shù)據(jù)庫中自帶的所有表結(jié)構(gòu)(sql代碼)
這篇文章主要介紹了Oracle數(shù)據(jù)庫中自帶的所有表結(jié)構(gòu),本文給大家介紹的非常詳細(xì),代碼簡單易懂,對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-11-11Oracle數(shù)據(jù)IMP和EXP命令用法詳解
這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)IMP和EXP命令用法詳解的相關(guān)資料,我們可以使用Oracle自帶的exp、imp命令來進(jìn)行數(shù)據(jù)庫備份,imp和exp稱之為導(dǎo)入導(dǎo)出,這種方式也能快速的導(dǎo)入導(dǎo)出table或數(shù)據(jù)庫,需要的朋友可以參考下2023-11-11