烦人的sqlserver到oracle的表结构转换
这几天搞了个sqlserver到oracle的ogg同步，总体来说 ，就是 配置参数，建表，初始化数据，配置进程。额，搞了2 ，3天。总的来说还是技术不过关。工欲善其事必先利其器，花的时间长就要看花哪里了。回忆并总结了一下，原来 花了一天的时间 纠结在表结构的配置上。OGG的initload不会创建表。所以必须自己想办法弄表结构。
心路历程
配置透明网关， CTAS， 以为成功了，But ...
表名和字段名全部小写了，需要加""； 源库表太长，没办法通过透明网关查询； 字段超过30 创建表会报错；
使用oracle的sql developer ,同步数据，基本上成功了，But：
必须同时连接oracle 和 sqlserver 两个库； sqlserver 的varchar 大于4000的时候，没有处理，导致建表的时候报错； varchar转成varchar2的时候变成了VARCHAR2(50 CHAR) ，着实不好看； 主键名称怪怪的，不好看，字段太长了没有处理，表太长了，没有处理；
还有没有什么工具可以用呀。
网上大神 还是很多的，《SQLServer表结构转换成Oracle表结构》 https://blog.csdn.net/mh942408056/article/details/71330540
试了一下效果挺好的，不过依然存在几个问题：
1个1个表来搞也是麻烦； 没有考虑表太长的情况，没有考虑字段太长的情况； 没有考虑的varchar 大于4000的时候的情况；
因此在基础上修改一下：
varchar 超过4000的转换成clob; 表名，字段名限制为30个字符； 改成用中间表ConvOracleList 来实现多表操作; 修复源存储过程多表无法循环问题; 更多的数据类型的转换，下图是sql developer工具的类型转换，可参考：
实操过程
1 创建中间表
字段说明：源表名，目标表名，目标主键名，ddl文本
create table ConvOracleList( table_name varchar(100) not null ,tar_tab_name varchar(30) not null ,prikeyname varchar(100),ddl_text text);
alter table ConvOracleList add constraint pk_ConvOracleList primary key(table_name);
2、插入需要处理的表
insert into ConvOracleList
select CONVERT(varchar(100),x.name),substring(x.name,1,30),'PK_'+CONVERT(varchar(100),x.name),NULL
from sys.tables x where x.name<>'ConvOracleList'
order by x.name;
3、处理源库表过长的条目
select *,LEN(tar_tab_name),LEN(prikeyname) from ConvOracleList where LEN(tar_tab_name)>30 or LEN(prikeyname)>30;
确认表名和主键名没有大于30的，如果有，则先修改：
update ConvOracleList set prikeyname ='PK_PersonalizationAllUsers' where table_name='AAAAA_PersonalizationAllUsers';
4、创建存储过程（核心内容）
CREATE PROCEDURE [dbo].[Conv2Oracle]
as
begin
/********************
* created: sqlserver 表结构转换成Oralce 表结构
* modify by chuchu , 2021-05-08: 新增多种字段的转换
* modify by chuchu , 2021-05-08: 表名，字段名限制为个字符
* modify by chuchu , 2021-05-08: 改成用中间表ConvOracleList 来实现多表操作
* modify by chuchu , 2021-05-08: 修复源存储过程多表无法循环问题
**********************/
--取消影响行数
set nocount on;
--创建表名游标
declare table_cursor cursor for
(select CONVERT(varchar(100),x.name) name,CONVERT(varchar(500),y.value) value ,z.tar_tab_name,z.prikeyname
from sys.tables x
left join (select major_id,value from sys.extended_properties where minor_id=0) y
on x.object_id=y.major_id
inner join ConvOracleList z
on x.name=z.table_name
);
--声明变量
declare
@sql varchar(max)='',
@primary varchar(100),
@tablename varchar(100), --表名称
@tabledes varchar(500), --表名称描述
@tartabName varchar(30), --目标表名，截取个字符
@prikeyname varchar(100); --主键名
--创建表结构临时表
create table #table(colname varchar(300),
isprimary int,
typename varchar(50),
intlength int,
decimallength int,
nullflag int,
defaultval varchar(50),
commonts varchar(500)
)
--打开游标
open table_cursor;
fetch next from table_cursor into @tableName,@tabledes,@tartabname,@prikeyname;
while @@FETCH_STATUS = 0
begin
--初始化变量
select @sql='' ;
select @tabledes = case when ISNULL(@tabledes,'')='' then '' else @tabledes end
truncate table #table;
--获取表字段的类型信息
insert into #table (colname,isprimary,typename,intlength,decimallength,nullflag,defaultval,commonts)
SELECT CONVERT(varchar(300),replace(a.name,' ','')) [字段名],
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in (SELECT name
FROM sysindexes
WHERE (id = a.id)
AND (indid in (SELECT indid
FROM sysindexkeys
WHERE (id = a.id)
AND (colid in (SELECT colid
FROM syscolumns
WHERE (id = a.id)
AND (name = a.name)))))))
AND (xtype = 'PK')) > 0 then 1
end) [主键],
b.name [类型],
COLUMNPROPERTY(a.id, a.name, 'PRECISION') as [长度],
isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) as [小数位数],
(case when a.isnullable = 1 then 1 else 0 end) [允许空],
e.text [默认值],
CONVERT(varchar(500),g.[value]) AS [说明]
FROM syscolumns a
left join sys* b
on a.xtype = b.xusertype
inner join sysobjects d
on a.id = d.id
and d.xtype = 'U'
and d.name <> 'dtproperties'
left join syscomments e
on a.cdefault = e.id
left join sys.extended_properties g
on a.id = g.major_id
AND a.colid = g.minor_id
left join sys.extended_properties f
on d.id = f.class
and f.minor_id = 0
where b.name is not null
and d.name=@tableName
order by a.id,a.colorder;
--构建表结构
select @sql+='
'+case colname when 'Comment' then 'Comments' else SUBSTRING(colname,1,30) end +' '
+case typename
when 'image' then 'BLOB '
when 'binary' then 'BLOB '
when 'varbinary' then 'BLOB '
when 'text' then 'CLOB '
when 'xml' then 'CLOB '
when 'ntext' then 'NCLOB '
when 'uniqueidentifier' then 'varchar2(36) '
when 'time' then 'varchar2(200) '
when 'datetimeoffset' then 'varchar2(200) '
when 'datatime2' then 'varchar2(200) '
when 'date' then 'varchar2(200) '
when 'sysname' then 'varchar2(128) '
when 'timestamp' then 'number(8,0) '
when 'varchar' then
case when intlength>4000 then 'CLOB ' --当varchar>4000的时候，oracle不支持，需要转成clob
when intlength<0 then 'CLOB ' --max ,-1 CLOB
else 'varchar2('+CONVERT(varchar(10),intlength)+') '
end
when 'nvarchar' then
case when intlength>4000 then 'NCLOB '
when intlength<0 then 'NCLOB ' --max ,-1 CLOB
else 'nvarchar2('+CONVERT(varchar(10),intlength)+') '
end
when 'bit' then 'number(1,0) '
when 'int' then 'number(10,0) '
when 'integer' then 'number(10,0) '
when 'bigint' then 'number(19,0) '
when 'smallmoney' then 'number(10,4) '
when 'money' then 'number(19,4) '
when 'numeric' then 'number('+CONVERT(varchar(10),intlength)+ ( case when decimallength>0 then ','+ CONVERT(varchar(10),decimallength) else '' end)+') '
when 'decimal' then 'number('+CONVERT(varchar(10),intlength)+ ( case when decimallength>0 then ','+ CONVERT(varchar(10),decimallength) else '' end)+') '
when 'datetime' then 'date '
when 'smalldatatime' then 'date '
else typename
end
+ case when defaultval is not null and len(defaultval)>0 then 'default '+
(case when charindex('getdate',defaultval)>0 then 'sysdate '
when charindex('newid',defaultval)>0 then 'sys_guid() '
else (case when typename='int' or typename='decimal' then REPLACE(REPLACE(defaultval,'(',''),')','') else defaultval end )
end)
else '' end
+ case when nullflag=0 then ' not null,' else ',' end
from #table;
if @sql is not null and len(@sql)>0
begin
set @sql=left(@sql,len(@sql)-1);
--创建表结构
set @sql='create table '+ @tartabname +'('+@sql+
'
);
comment on table '+@tartabname+' is '''+@tabledes+''';
'
--添加字段备注
select @sql+= case when commonts is not null and len(commonts)>0 then 'comment on column '+@tartabname+'.'+colname+' is '''+commonts+''';
' else '' end
from #table;
--添加主键索引
if exists(select 1 from #table where isprimary=1 )
begin
set @primary=''
select @primary+= colname+','
from #table
where isprimary=1
set @primary=left(@primary,len(@primary)-1);
set @sql+='alter table '+@tartabname+' add constraint '+@prikeyname+' primary key ('+@primary+') using INDEX;
'
end
--行尾加两个空行
select @sql+='
'
end
--更新到中间表
update ConvOracleList set ddl_text = @sql where table_name = @tableName;
print CAST(@sql AS TEXT);
fetch next from table_cursor into @tableName,@tabledes,@tartabname,@prikeyname ;
end
close table_cursor;
deallocate table_cursor;
--删除临时表
drop table #table;
end
5、生成ddl 更新到中间表
update ConvOracleList set ddl_text=null;
exec conv2oracle
select ddl_text from ConvOracleList;
6、导出查询结果
create table ACSI(
ACSI_ID varchar2(20) not null,
);
comment on table ACSI is '';
alter table ACSI add constraint PK_ACSI primary key (ACSI_ID) using INDEX;
7、编辑脚本，可以加个表空间，然后执行即可。
至此sqlserver 转换oracle 的表结构完成，除了适用于ogg迁移，普通的数据迁移也是适用的。
本文来源https://www.modb.pro/db/66735
相关文章