mysql与oracle数据手动互转

使用说明

1. 修改数据库和表名
2. 执行代码，获取查询结果
3. 将所有查询结果粘贴一起，再次执行获得加入语句。

mysql 转 oracle

select 
concat(
case ordinal_position when 1 then concat('select concat(' , char(39) , 'insert into ' , a.table_name , '(',b.ms,') values (' , char(39) , ',') else '' end ,
case a.is_nullable when 'YES' then concat('case when ' , column_name , ' IS NULL then ' , char(39) , 'NULL' , char(39) , ' else concat(') else '' end ,
 case when a.data_type in ('VARCHAR', 'CHAR') then concat('char(39),', column_name ,  ',char(39)')
-- 日期特殊处理
when a.data_type in ('DATETIME', 'DATE') then concat(char(39) , 'to_date(' , char(39) , ',' , 'char(39),' , 'DATE_FORMAT(' , column_name , ',' , char(39) , '%Y-%m-%d %H:%i:%s' , char(39) , ')' , ',char(39),' , char(39) , ',' , char(39) , ',char(39),' , char(39) ,'yyyy-mm-dd hh24:mi:ss' , char(39) , ',char(39),' , char(39) , ')' , char(39))
else column_name  end 
  , case a.is_nullable when 'YES' then ') end ' else '' end
  , case ordinal_position when b.mid then concat(',' , char(39) , ');' , char(39) , ') from ' , a.table_name , ';') else concat(',' , char(39) , ',' , char(39) , ',') end 
)
 from 
 INFORMATION_SCHEMA.COLUMNS a,
 (
 select table_schema, table_name, max(ordinal_position) mid, GROUP_CONCAT(column_name order by ordinal_position) ms from INFORMATION_SCHEMA.COLUMNS 
 where table_schema like concat('数据库名', '_100_001')
 GROUP BY table_schema, table_name
 ) b
where a.table_name = b.table_name and a.table_schema like concat('数据库名', '_100_001') and a.table_name = '数据库表名'
order by ordinal_position;

oracle 转 mysql

select 
case column_id when 1 then 'select ' || chr(39) || 'insert into ' || a.table_name || '('||ms||') values (' || chr(39) || '||' else '' end ||
case a.nullable when 'Y' then 'decode(' || column_name || ', NULL, ' || chr(39) || 'NULL' || chr(39) || ', ' else '' end ||
 case a.data_type when 'VARCHAR2' then 'chr(39)||'|| column_name ||  '||chr(39)'
-- 日期特殊处理
when 'DATE' then chr(39) || 'str_to_date(' || chr(39) || '||' || 'chr(39)||' || 'to_char(' || column_name || ',' || chr(39) || 'yyyy-mm-dd hh24:mi:ss' || chr(39) || ')' || '||chr(39)||' || chr(39) || ',' || chr(39) || '||chr(39)||' || chr(39) ||'%Y-%m-%d %H:%i:%s' || chr(39) || '||chr(39)||' || chr(39) || ')' || chr(39)
else column_name  end 
  || case a.nullable when 'Y' then ')' else '' end
  || case column_id when b.mid then '||' || chr(39) || ');' || chr(39) || ' from ' || a.table_name || ';' else '||' || chr(39) || ',' || chr(39) || '||' end 
 from (select  * from dba_tab_columns where owner = upper('oracle用户名') and table_name = upper('oracle用户下表名')) a，
 (
 select table_name, max(column_id) mid, max(to_char(s)) ms from
(
select table_name, column_id, wm_concat(column_name) over (partition by table_name order by column_id) s from dba_tab_columns 
where owner = upper('oracle用户名')
) t
group by table_name
 ) b
where a.table_name = b.table_name
order by column_id;