#oracle-mysql#
#从oracle数据库导出数据，并远程导入到mysql数据库#
@echo off
#获取日期参数#
set para1=%1
#生成导出时使用的sql#
#配置sqlplus输出格式#
echo set echo off;>ora_my_%para1%.sql
echo set feedback off;>>ora_my_%para1%.sql
echo set trimspool on;>>ora_my_%para1%.sql
echo set trimout on;>>ora_my_%para1%.sql
echo set linesize 45;>>ora_my_%para1%.sql
echo set pagesize 2000;>>ora_my_%para1%.sql
echo set newpage 1;>>ora_my_%para1%.sql
echo set heading off;>>ora_my_%para1%.sql
echo set term off;>>ora_my_%para1%.sql
echo set termout off;>>ora_my_%para1%.sql
echo set timing off;>>ora_my_%para1%.sql
#准备导出数据#
echo spool in_%para1%.txt >>ora_my_%para1%.sql
#导出使用语句，因为win默认导出是GBK字符集，mysql导入时不能正常识别非utf-8#
#所以干脆用decode转换成纯英文字符#
#因为默认是用tab来作为列分隔符，导出时直接将分隔符用下述方式修改为英文逗号#
echo select riqi^|^|','^|^|accountid^|^|','^|^|ID^|^|','^|^|decode(telecomtype,'移动',0,'联通',1,'电信',2,3)^|^|','^|^|hz >>ora_my_%para1%.sql
echo from (SELECT * FROM C_PROVINCE_LIST WHERE riqi='%para1%') t LEFT JOIN t_area r ON
r.AREANAME=t.PROVINCE;>>ora_my_%para1%.sql
#导出结束#
echo spool off
echo exit>>ora_my_%para1%.sql
#sql文件生成结束#
#运行sqlplus执行刚才生成的sql语句以实现导出#
sqlplus test2017/"""test@0101"""@test_192.168.0.54 @F:\hd\ora_my_%para1%.sql
#生成导入mysql数据库的sql语句#
#补充一下，修改mysql数据库字符集为GBK并set names gbk后导入中文混排正常#
echo set names gbk;>>my_%para1%.sql
echo load data local infile "F:/hd/in_%para1%.txt" into table C_PROVINCE_LIST fields terminated by ','; >my_%para1%.sql
#删除导入时生成的空行#
echo delete from c_province_list where accountid is null; >>my_%para1%.sql
#生成sql结束#
#通过mysql客户端运行刚才生成的load data语句导入数据#
F:\hd\mysql.exe -utest -ptest -h102.124.174.11 test_db <my_%para1%.sql
#删除中间生成的sql语句和导出的文本文件#
del /q *.sql
del /q *.txt