Oracle数据库批量变更字段类型的实现步骤
(福利推荐:你还在原价购买阿里云服务器?现在阿里云0.8折限时抢购活动来啦!4核8G企业云服务器仅2998元/3年,立即抢购>>>:9i0i.cn/aliyun)
场景:
我有个项目使用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执行一遍 就完成了。
最后
到此这篇关于Oracle数据库批量变更字段类型的实现步骤的文章就介绍到这了,更多相关Oracle批量变更字段类型内容请搜索程序员之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持程序员之家!
相关文章
Oracle SQL中实现indexOf和lastIndexOf功能的思路及代码
INSTR的第三个参数为1时,实现的是indexOf功能;为-1时实现的是lastIndexOf功能,具体实现如下,感兴趣的朋友可以参考下哈下,希望对大家有所帮助2013-05-05Oracle中的translate函数和replace函数的用法详解
translate返回expr,其中from_string中的每个字符的所有出现都被to_string中的相应字符替换,而replace 函数将char中的字符串search_string全部转换为字符串replacement_string。下面给大家分享Oracle中的translate函数和replace函数的用法,一起看看吧2017-11-11
最新评论