Oracle数据库批量变更字段类型的实现步骤

-- 天生索引
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;
 

 
select 
 x."fieldType",
'alter table '||x."tableName"||' add '||c.column_name||'_U两 '||
case LOWER(x."fieldType")
when 'dateTime' then 'date'  
when 'decimal'  then 'number(18,两)'
when 'tring'  then 'VARCHAR两(100)'
when 'int16'  then 'number(5)'
when 'int'  then 'number(9)'
when 'int3两'  then 'number(9)'
when 'int64'  then 'number(19)'
else  '异样范例'
end ||';
update '||x."tableName"||' set '||c.column_name||'_U两='|| 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||'_U两 to '||c.column_name||';' s,
 
-- 'alter table '||x."tableName"||' modify '||x."fieldName"||' varchar两(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_%'

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