从mysql导一个表到Oracle
背景：业务需要从阿里云数据库导出一张费率表到Oracle数据库，每天凌晨5点更新一次，需要做2个定时任务，1个下载数据，1个插入数据的定时任务，下载是在Linux平台，插入数据的Oracle数据库是安装在windows服务器上。因此，下载数据的定时任务是.sh 的脚本，插入数据的定时任务是.bat脚本。
一、下载阿里云数据的定时任务脚本
vi data_transport.sh
#!/bin/bash
db_user="***"
db_pass="***"
db_host="****"
/usr/bin/mysql -h${db_host} -u${db_user} -p${db_pass} -ss -e 'select * from testdb.test ' | sed 's/\t/,/g' > /tmp/aa.csv
chmod +x /tmp/data_transport.sh
00 05 * * * /bin/bash /tmp/data_transport.sh
二、ftp到目标主机相应位置
三、Oracle目标端创建好表结构
注意：1，将mysql为数字类型的全部替换为number，将varchar替换为varchar2
2,表，字段注释加在建表语句之后
3，Oracle字段长度不超过30个字符
CREATE TABLE TEST (
id number primary key not null,
security_code varchar2(255) NOT NULL,
net_asset_value number DEFAULT NULL,
latest_net_present_value number DEFAULT NULL,
the_date date DEFAULT NULL,
latest_npvc number DEFAULT NULL,
latest_cnpv number DEFAULT NULL,
latest_cnpvc number DEFAULT NULL,
latest_twcnpv number DEFAULT NULL ,
latest_twcnpvc number DEFAULT NULL,
latest_yield number DEFAULT NULL,
latest1week_yield number DEFAULT NULL,
latest1week_type_yield_rank number DEFAULT NULL,
latest1week_type_yield_total number DEFAULT NULL,
latest_lwtay number DEFAULT NULL,
latest1week_yield_rank number DEFAULT NULL,
latest1month_yield number DEFAULT NULL,
latest1month_type_yield_rank number DEFAULT NULL,
latest1month_type_yield_total number DEFAULT NULL,
latest_1mtay number DEFAULT NULL,
latest1month_yield_rank number DEFAULT NULL,
latest3months_yield number DEFAULT NULL,
latest3months_type_yield_rank number DEFAULT NULL,
latest_3mtyt number DEFAULT NULL,
latest_3mtay number DEFAULT NULL,
latest3months_yield_rank number DEFAULT NULL,
latest_half_year_yield number DEFAULT NULL,
latest_hytyr number DEFAULT NULL,
latest_hytyt number DEFAULT NULL,
latest_htay number DEFAULT NULL,
latest_half_year_yield_rank number DEFAULT NULL,
latest_year_yield number DEFAULT NULL,
latest_year_type_yield_rank number DEFAULT NULL,
latest_year_type_yield_total number DEFAULT NULL,
latest_ytay number DEFAULT NULL,
latest_year_yield_rank number DEFAULT NULL,
latest_2year_yield number DEFAULT NULL,
latest_2year_type_yield_rank number DEFAULT NULL,
latest_2year_type_yield_total number DEFAULT NULL,
latest_2ytyay number DEFAULT NULL,
latest_2year_yield_rank number DEFAULT NULL,
latest_3year_yield number DEFAULT NULL,
latest_3year_type_yield_rank number DEFAULT NULL,
latest_3year_type_yield_total number DEFAULT NULL,
lateslatest_3ytay number DEFAULT NULL,
latest_3year_yield_rank number DEFAULT NULL,
yield_since_founding number DEFAULT NULL,
type_yrsf number DEFAULT NULL,
type_ytsf number DEFAULT NULL,
yield_stay number DEFAULT NULL,
yield_rank_since_founding number DEFAULT NULL,
since_this_year_yield number DEFAULT NULL,
since_tytyt number DEFAULT NULL,
since_tytay number DEFAULT NULL,
since_tytyr number DEFAULT NULL,
since_this_year_yield_rank number DEFAULT NULL,
fund_total number DEFAULT NULL,
latest10k_accrual number DEFAULT NULL,
latest7days_years_yield number DEFAULT NULL,
latest14days_years_yield number DEFAULT NULL,
latest28days_years_yield number DEFAULT NULL,
latest35days_years_yield number DEFAULT NULL,
rpt_netasset number DEFAULT NULL,
rpt_netasset_date date DEFAULT NULL,
minimum_application number DEFAULT NULL,
hbec_risk_level varchar2(255) DEFAULT NULL,
subscription_fee number DEFAULT NULL,
subscription_fee_dis number DEFAULT NULL,
subscription_dis number DEFAULT NULL,
application_fee number DEFAULT NULL,
application_fee_dis number DEFAULT NULL,
application_dis number DEFAULT NULL,
redemption_fee number DEFAULT NULL,
redemption_fee_dis number DEFAULT NULL,
redemption_dis number DEFAULT NULL,
application_status_int number DEFAULT NULL,
application_status varchar2(255) DEFAULT NULL,
is_available number DEFAULT NULL,
last_modified_dt timestamp NOT NULL ,
application_status_var varchar2(255) DEFAULT NULL,
redemption_status_var varchar2(255) DEFAULT NULL,
CONSTRAINT tb_security_code UNIQUE (security_code)
);
COMMENT ON TABLE TEST IS '基金信息计算指标表';
COMMENT ON COLUMN TEST.ID IS '自增主键';
COMMENT ON COLUMN TEST.SECURITY_CODE IS '基金代码';
COMMENT ON COLUMN TEST.NET_ASSET_VALUE IS '最新资产净值';
COMMENT ON COLUMN TEST.LATEST_NET_PRESENT_VALUE IS '最新净值';
COMMENT ON COLUMN TEST.THE_DATE IS '最新净值时间';
COMMENT ON COLUMN TEST.LATEST_NPVC IS '最新净值变动';
COMMENT ON COLUMN TEST.LATEST_CNPV IS '最新累计净值';
COMMENT ON COLUMN TEST.LATEST_CNPVC IS '最新累计净值变动';
COMMENT ON COLUMN TEST.LATEST_TWCNPV IS '最新时间加权累计净值';
COMMENT ON COLUMN TEST.LATEST_TWCNPVC IS '最新时间加权累计净值变动';
COMMENT ON COLUMN TEST.LATEST_YIELD IS '最新收益率';
COMMENT ON COLUMN TEST.LATEST1WEEK_YIELD IS '最近1周收益率';
COMMENT ON COLUMN TEST.LATEST1WEEK_TYPE_YIELD_RANK IS '最近1周同类收益率排名';
COMMENT ON COLUMN TEST.LATEST1WEEK_TYPE_YIELD_TOTAL IS '最近1周同类收益率基金总数';
COMMENT ON COLUMN TEST.LATEST_LWTAY IS '最近1周同类平均收益率';
COMMENT ON COLUMN TEST.LATEST1WEEK_YIELD_RANK IS '最近1周收益率排名';
COMMENT ON COLUMN TEST.LATEST1MONTH_YIELD IS '最近1月收益率';
COMMENT ON COLUMN TEST.LATEST1MONTH_TYPE_YIELD_RANK IS '最近1月同类收益率排名';
COMMENT ON COLUMN TEST.LATEST1MONTH_TYPE_YIELD_TOTAL IS '最近1月同类收益率基金总数';
COMMENT ON COLUMN TEST.LATEST_1MTAY IS '最近1月同类平均收益率';
COMMENT ON COLUMN TEST.LATEST1MONTH_YIELD_RANK IS '最近1月收益率排名';
COMMENT ON COLUMN TEST.LATEST3MONTHS_YIELD IS '最近3月收益率';
COMMENT ON COLUMN TEST.LATEST3MONTHS_TYPE_YIELD_RANK IS '最近3月同类收益率排名';
COMMENT ON COLUMN TEST.LATEST_3MTYT IS '最近3月同类收益率基金总数';
COMMENT ON COLUMN TEST.LATEST_3MTAY IS '最近3月同类平均收益率';
COMMENT ON COLUMN TEST.LATEST3MONTHS_YIELD_RANK IS '最近3月收益率排名';
COMMENT ON COLUMN TEST.LATEST_HALF_YEAR_YIELD IS '最近6月(半年)收益率';
COMMENT ON COLUMN TEST.LATEST_HYTYR IS '最近6月(半年)同类收益率排名';
COMMENT ON COLUMN TEST.LATEST_HYTYT IS '最近6月(半年)同类收益率基金总数';
COMMENT ON COLUMN TEST.LATEST_HTAY IS '最近6月(半年)同类平均收益率';
COMMENT ON COLUMN TEST.LATEST_HALF_YEAR_YIELD_RANK IS '最近6月(半年)收益率排名';
COMMENT ON COLUMN TEST.LATEST_YEAR_YIELD IS '最近1年收益率';
COMMENT ON COLUMN TEST.LATEST_YEAR_TYPE_YIELD_RANK IS '最近1年同类收益率排名';
COMMENT ON COLUMN TEST.LATEST_YEAR_TYPE_YIELD_TOTAL IS '最近1年同类收益率基金总数';
COMMENT ON COLUMN TEST.LATEST_YTAY IS '最近1年同类平均收益率';
COMMENT ON COLUMN TEST.LATEST_YEAR_YIELD_RANK IS '最近1年收益率排名';
COMMENT ON COLUMN TEST.LATEST_2YEAR_YIELD IS '最近2年收益率';
COMMENT ON COLUMN TEST.LATEST_2YEAR_TYPE_YIELD_RANK IS '最近2年同类收益率排名';
COMMENT ON COLUMN TEST.LATEST_2YEAR_TYPE_YIELD_TOTAL IS '最近2年同类收益率基金总数';
COMMENT ON COLUMN TEST.LATEST_2YTYAY IS '最近2年同类平均收益率';
COMMENT ON COLUMN TEST.LATEST_2YEAR_YIELD_RANK IS '最近2年收益率排名';
COMMENT ON COLUMN TEST.LATEST_3YEAR_YIELD IS '最近3年收益率';
COMMENT ON COLUMN TEST.LATEST_3YEAR_TYPE_YIELD_RANK IS '最近3年同类收益率排名';
COMMENT ON COLUMN TEST.LATEST_3YEAR_TYPE_YIELD_TOTAL IS '最近3年同类收益率基金总数';
COMMENT ON COLUMN TEST.lateslatest_3ytay IS '最近3年同类平均收益率';
COMMENT ON COLUMN TEST.LATEST_3YEAR_YIELD_RANK IS '最近3年收益率排名';
COMMENT ON COLUMN TEST.YIELD_SINCE_FOUNDING IS '成立以来收益率';
COMMENT ON COLUMN TEST.TYPE_YRSF IS '成立以来同类收益率排名';
COMMENT ON COLUMN TEST.TYPE_YTSF IS '成立以来同类收益率基金总数';
COMMENT ON COLUMN TEST.YIELD_STAY IS '成立以来同类平均收益率';
COMMENT ON COLUMN TEST.YIELD_RANK_SINCE_FOUNDING IS '成立以来收益率排名';
COMMENT ON COLUMN TEST.SINCE_THIS_YEAR_YIELD IS '今年以来收益率';
COMMENT ON COLUMN TEST.SINCE_TYTYT IS '今年以来同类收益率排名总数';
COMMENT ON COLUMN TEST.SINCE_TYTAY IS '今年以来同类平均收益率';
COMMENT ON COLUMN TEST.SINCE_TYTYR IS '今年以来同类收益率排名';
COMMENT ON COLUMN TEST.SINCE_THIS_YEAR_YIELD_RANK IS '今年以来收益率排名';
COMMENT ON COLUMN TEST.FUND_TOTAL IS '基金总数';
COMMENT ON COLUMN TEST.LATEST10K_ACCRUAL IS '万分收益率';
COMMENT ON COLUMN TEST.LATEST7DAYS_YEARS_YIELD IS '7日年化收益率';
COMMENT ON COLUMN TEST.LATEST14DAYS_YEARS_YIELD IS '14日年化收益率';
COMMENT ON COLUMN TEST.LATEST28DAYS_YEARS_YIELD IS '28日年化收益率';
COMMENT ON COLUMN TEST.LATEST35DAYS_YEARS_YIELD IS '35日年化收益率';
COMMENT ON COLUMN TEST.RPT_NETASSET IS '资产规模(资产净值)';
COMMENT ON COLUMN TEST.RPT_NETASSET_DATE IS '基金资产规模最新时间';
COMMENT ON COLUMN TEST.MINIMUM_APPLICATION IS '最低申购金额';
COMMENT ON COLUMN TEST.HBEC_RISK_LEVEL IS '风险级别(中文)';
COMMENT ON COLUMN TEST.SUBSCRIPTION_FEE IS '赎回基础费率';
COMMENT ON COLUMN TEST.SUBSCRIPTION_FEE_DIS IS '赎回折扣费率';
COMMENT ON COLUMN TEST.SUBSCRIPTION_DIS IS '默认赎回折扣';
COMMENT ON COLUMN TEST.APPLICATION_FEE IS '申购基础费率';
COMMENT ON COLUMN TEST.APPLICATION_FEE_DIS IS '申购折扣费率';
COMMENT ON COLUMN TEST.APPLICATION_DIS IS '默认申购折扣';
COMMENT ON COLUMN TEST.REDEMPTION_FEE IS '认购基础费率';
COMMENT ON COLUMN TEST.REDEMPTION_FEE_DIS IS '认购折扣费率';
COMMENT ON COLUMN TEST.REDEMPTION_DIS IS '默认认购折扣';
COMMENT ON COLUMN TEST.APPLICATION_STATUS_INT IS '申购状态-数字';
COMMENT ON COLUMN TEST.APPLICATION_STATUS IS '申购状态';
COMMENT ON COLUMN TEST.IS_AVAILABLE IS '是否可用；1:可用；2:不可用';
COMMENT ON COLUMN TEST.LAST_MODIFIED_DT IS '上次修改时间';
COMMENT ON COLUMN TEST.APPLICATION_STATUS_VAR IS '基金公司申购状态';
COMMENT ON COLUMN TEST.REDEMPTION_STATUS_VAR IS '基金公司赎回状态';
四、编辑控制文件control.ctl
load data
CHARACTERSET ZHS16GBK ---指定字符集与下载的aa.csv 字符集相同
infile 'd:/aa.csv' ---导入的数据文件是aa.csv
replace into table dao.TEST ---replace delete Oracle表中的原数据，再插入 --schema=dao ，插入test表中
fields terminated by ',' --字段以逗号分隔
optionally enclosed by '"' --以双引号包裹的字段，左右侧都有必须2N+1个双引号，且DB数据有N双个引号。
只有右侧有左侧没有的情况下，右侧有多少都可以导入，且DB数据和原数据一致。
只有左侧有所有数据都不符合规范。
trailing nullcols -- 表的字段没有对应的值时允许为空 ，这句很有用，默认加的好
(id,
security_code "case when :security_code = 'NULL' then '' else :security_code end",
net_asset_value "case when :net_asset_value = 'NULL' then '' else :net_asset_value end",
latest_net_present_value "case when :latest_net_present_value = 'NULL' then '' else :latest_net_present_value end",
the_date "case when :the_date = 'NULL' then null else TO_DATE(:the_date,'yyyy-mm-dd') end",
latest_npvc "case when :latest_npvc = 'NULL' then '' else :latest_npvc end",
latest_cnpv "case when :latest_cnpv = 'NULL' then '' else :latest_cnpv end",
latest_cnpvc "case when :latest_cnpvc = 'NULL' then '' else :latest_cnpvc end",
latest_twcnpv "case when :latest_twcnpv = 'NULL' then '' else :latest_twcnpv end",
latest_twcnpvc "case when :latest_twcnpvc = 'NULL' then '' else :latest_twcnpvc end",
latest_yield "case when :latest_yield = 'NULL' then '' else :latest_yield end",
latest1week_yield "case when :latest1week_yield = 'NULL' then '' else :latest1week_yield end",
latest1week_type_yield_rank "case when :latest1week_type_yield_rank = 'NULL' then '' else :latest1week_type_yield_rank end",
latest1week_type_yield_total "case when :latest1week_type_yield_total = 'NULL' then '' else :latest1week_type_yield_total end",
latest_lwtay "case when :latest_lwtay = 'NULL' then '' else :latest_lwtay end",
latest1week_yield_rank "case when :latest1week_yield_rank = 'NULL' then '' else :latest1week_yield_rank end",
latest1month_yield "case when :latest1month_yield = 'NULL' then '' else :latest1month_yield end",
latest1month_type_yield_rank "case when :latest1month_type_yield_rank = 'NULL' then '' else :latest1month_type_yield_rank end",
latest1month_type_yield_total "case when :latest1month_type_yield_total = 'NULL' then '' else :latest1month_type_yield_total end",
latest_1mtay "case when :latest_1mtay = 'NULL' then '' else :latest_1mtay end",
latest1month_yield_rank "case when :latest1month_yield_rank = 'NULL' then '' else :latest1month_yield_rank end",
latest3months_yield "case when :latest3months_yield = 'NULL' then '' else :latest3months_yield end",
latest3months_type_yield_rank "case when :latest3months_type_yield_rank = 'NULL' then '' else :latest3months_type_yield_rank end",
latest_3mtyt "case when :latest_3mtyt = 'NULL' then '' else :latest_3mtyt end",
latest_3mtay "case when :latest_3mtay = 'NULL' then '' else :latest_3mtay end",
latest3months_yield_rank "case when :latest3months_yield_rank = 'NULL' then '' else :latest3months_yield_rank end",
latest_half_year_yield "case when :latest_half_year_yield = 'NULL' then '' else :latest_half_year_yield end",
latest_hytyr "case when :latest_hytyr = 'NULL' then '' else :latest_hytyr end",
latest_hytyt "case when :latest_hytyt = 'NULL' then '' else :latest_hytyt end",
latest_htay "case when :latest_htay = 'NULL' then '' else :latest_htay end",
latest_half_year_yield_rank "case when :latest_half_year_yield_rank = 'NULL' then '' else :latest_half_year_yield_rank end",
latest_year_yield "case when :latest_year_yield = 'NULL' then '' else :latest_year_yield end",
latest_year_type_yield_rank "case when :latest_year_type_yield_rank = 'NULL' then '' else :latest_year_type_yield_rank end",
latest_year_type_yield_total "case when :latest_year_type_yield_total = 'NULL' then '' else :latest_year_type_yield_total end",
latest_ytay "case when :latest_ytay = 'NULL' then '' else :latest_ytay end",
latest_year_yield_rank "case when :latest_year_yield_rank = 'NULL' then '' else :latest_year_yield_rank end",
latest_2year_yield "case when :latest_2year_yield = 'NULL' then '' else :latest_2year_yield end",
latest_2year_type_yield_rank "case when :latest_2year_type_yield_rank = 'NULL' then '' else :latest_2year_type_yield_rank end",
latest_2year_type_yield_total "case when :latest_2year_type_yield_total = 'NULL' then '' else :latest_2year_type_yield_total end",
latest_2ytyay "case when :latest_2ytyay = 'NULL' then '' else :latest_2ytyay end",
latest_2year_yield_rank "case when :latest_2year_yield_rank = 'NULL' then '' else :latest_2year_yield_rank end",
latest_3year_yield "case when :latest_3year_yield = 'NULL' then '' else :latest_3year_yield end",
latest_3year_type_yield_rank "case when :latest_3year_type_yield_rank = 'NULL' then '' else :latest_3year_type_yield_rank end",
latest_3year_type_yield_total "case when :latest_3year_type_yield_total = 'NULL' then '' else :latest_3year_type_yield_total end",
lateslatest_3ytay "case when :lateslatest_3ytay = 'NULL' then '' else :lateslatest_3ytay end",
latest_3year_yield_rank "case when :latest_3year_yield_rank = 'NULL' then '' else :latest_3year_yield_rank end",
yield_since_founding "case when :yield_since_founding = 'NULL' then '' else :yield_since_founding end",
type_yrsf "case when :type_yrsf = 'NULL' then '' else :type_yrsf end",
type_ytsf "case when :type_ytsf = 'NULL' then '' else :type_ytsf end",
yield_stay "case when :yield_stay = 'NULL' then '' else :yield_stay end",
yield_rank_since_founding "case when :yield_rank_since_founding = 'NULL' then '' else :yield_rank_since_founding end",
since_this_year_yield "case when :since_this_year_yield = 'NULL' then '' else :since_this_year_yield end",
since_tytyt "case when :since_tytyt = 'NULL' then '' else :since_tytyt end",
since_tytay "case when :since_tytay = 'NULL' then '' else :since_tytay end",
since_tytyr "case when :since_tytyr = 'NULL' then '' else :since_tytyr end",
since_this_year_yield_rank "case when :since_this_year_yield_rank = 'NULL' then '' else :since_this_year_yield_rank end" ,
fund_total "case when :fund_total = 'NULL' then '' else :fund_total end",
latest10k_accrual "case when :latest10k_accrual = 'NULL' then '' else :latest10k_accrual end",
latest7days_years_yield "case when :latest7days_years_yield = 'NULL' then '' else :latest7days_years_yield end",
latest14days_years_yield "case when :latest14days_years_yield = 'NULL' then '' else :latest14days_years_yield end",
latest28days_years_yield "case when :latest28days_years_yield = 'NULL' then '' else :latest28days_years_yield end",
latest35days_years_yield "case when :latest35days_years_yield = 'NULL' then '' else :latest35days_years_yield end",
rpt_netasset "case when :rpt_netasset = 'NULL' then '' else :rpt_netasset end",
rpt_netasset_date "case when :rpt_netasset_date = 'NULL' then null else TO_DATE(:rpt_netasset_date,'yyyy-mm-dd') end",
minimum_application "case when :minimum_application = 'NULL' then '' else :minimum_application end",
hbec_risk_level "case when :hbec_risk_level = 'NULL' then '' else :hbec_risk_level end",
subscription_fee "case when :subscription_fee = 'NULL' then '' else :subscription_fee end",
subscription_fee_dis "case when :subscription_fee_dis = 'NULL' then '' else :subscription_fee_dis end",
subscription_dis "case when :subscription_dis = 'NULL' then '' else :subscription_dis end",
application_fee "case when :application_fee = 'NULL' then '' else :application_fee end",
application_fee_dis "case when :application_fee_dis = 'NULL' then '' else :application_fee_dis end",
application_dis "case when :application_dis = 'NULL' then '' else :application_dis end",
redemption_fee "case when :redemption_fee = 'NULL' then '' else :redemption_fee end",
redemption_fee_dis "case when :redemption_fee_dis = 'NULL' then '' else :redemption_fee_dis end",
redemption_dis "case when :redemption_dis = 'NULL' then '' else :redemption_dis end",
application_status_int "case when :application_status_int = 'NULL' then '' else :application_status_int end",
application_status "case when :application_status = 'NULL' then '' else :application_status end",
is_available "case when :is_available = 'NULL' then '' else :is_available end",
last_modified_dt "case when :last_modified_dt = 'NULL' then null else TO_DATE(:last_modified_dt,'yyyy-mm-dd hh24:mi:ss') end",
application_status_var "case when :application_status_var = 'NULL' then '' else :application_status_var end" ,
redemption_status_var "case when :redemption_status_var = 'NULL' then '' else :redemption_status_var end")
五、在目标端执行定时任务
@echo off
:: the script used to load data into oracle automatically
:: define several variables
set db_user=THINKIVE_STOCK
set db_pass=THINKIVE_STOCK
set db_name=10.0.30.173:1521/ORCL
set db_crtlfile=d:/control.ctl
set db_datafile=d:/aa1.csv
set log_file=d:/control.log
sqlldr userid=%db_user%/%db_pass%@%db_name% control=%db_crtlfile% log=%log_file%
exit
网友评论