BayaiM__SQLLDR_linux_shell高级版
备注：1.因公司在职，商业机密，顾IP地方加了"*"号，你可以任意写一个数字做IP做就好。2.不要瞎BB，哥自己写的，挺实用的，里面告诉你许多来龙去脉。3.也算是知识分享，不算泄露什么鸟。=========================================================
1.直接上脚本：-----------------------------------------------------------------------------------------------------------------[root@oradata script]# cat wx_zfb.sh #!/bin/bashTDATE=`date -d '1 days ago' +%Y%m%d`Path_wx="/home/ftp/2gdata/wx/$TDATE"Path_wxwal="/home/ftp/2gdata/wxwal/$TDATE"Path_jyt_wk="/home/ftp/2gdata/jyt_wk/$TDATE"Path_zfb_nosett="/home/ftp/2gdata/zfb_nosett/$TDATE"Path_zfb_sett="/home/ftp/2gdata/zfb_sett/$TDATE"Path_xnzh="/home/ftp/2gdata/xnzh/$TDATE"if [ -d "${Path_wx}" ]; thenrm -rf "${Path_wx}"fiif [ -d "${Path_wxwal}" ]; thenrm -rf "${Path_wxwal}"fiif [ -d "${Path_jyt_wk}" ]; thenrm -rf "${Path_jyt_wk}"fiif [ -d "${Path_zfb_nosett}" ]; thenrm -rf "${Path_zfb_nosett}"fiif [ -d "${Path_zfb_sett}" ]; thenrm -rf "${Path_zfb_sett}"fiif [ -d "${Path_xnzh}" ]; thenrm -rf "${Path_xnzh}"fimkdir -p "${Path_wx}"mkdir -p "${Path_wxwal}"mkdir -p "${Path_jyt_wk}"mkdir -p "${Path_zfb_nosett}"mkdir -p "${Path_zfb_sett}"mkdir -p "${Path_xnzh}"ftp -n<<!open 172.16.3.*user js_ls ls3ppqvmbinarycd /xqfdzwj/wxlcd ${Path_wx}promptmget 48023010_${TDATE}.txt.gzclosebye!#cd /home/ftp/2gdata/wx/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz#gunzip -n -q /home/ftp/2gdata/wx/${TDATE}/48023010_${TDATE}.txt.gzftp -n<<!open 172.16.3.*user js_ls ls3ppqvmbinarycd /xqfdzwj/wxwallcd ${Path_wxwal}promptget 48023010_${TDATE}.txt.gzclosebye!#cd /home/ftp/2gdata/wxwal/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gzgunzip -n -q /home/ftp/2gdata/wxwal/${TDATE}/48023010_${TDATE}.txt.gzftp -n<<!open 172.16.3.*user js_ls ls3ppqvmbinarycd /xqfdzwj/jyt_wklcd ${Path_jyt_wk}promptget 48023010_${TDATE}.txt.gzclosebye!#cd /home/ftp/2gdata/jyt_wk/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gzgunzip -n -q /home/ftp/2gdata/jyt_wk/${TDATE}/48023010_${TDATE}.txt.gzftp -n<<!open 172.16.3.*user js_ls ls3ppqvmbinarycd /xqfdzwj/zfb_nosettlcd ${Path_zfb_nosett}promptget 48023010_${TDATE}.txt.gzclosebye!#cd /home/ftp/2gdata/zfb_nosett/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gzgunzip -n -q /home/ftp/2gdata/zfb_nosett/${TDATE}/48023010_${TDATE}.txt.gzftp -n<<!open 172.16.3.*user js_ls ls3ppqvmbinarycd /xqfdzwj/zfb_settlcd ${Path_zfb_sett}promptget 48023010_${TDATE}.txt.gzclosebye!#cd /home/ftp/2gdata/zfb_sett/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gzgunzip -n -q /home/ftp/2gdata/zfb_sett/${TDATE}/48023010_${TDATE}.txt.gzftp -n<<!open 172.16.3.*user js_ls ls3ppqvmbinarycd /xqfdzwj/xnzhlcd ${Path_xnzh}promptget 48023010_${TDATE}.txt.gzclosebye!#cd /home/ftp/2gdata/xnzh/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gzgunzip -n -q /home/ftp/2gdata/xnzh/${TDATE}/48023010_${TDATE}.txt.gzgunzip -n -q /home/ftp/2gdata/wx/${TDATE}/48023010_${TDATE}.txt.gz---------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------[root@oradata intelligent_pos]# cat intelligent_pos_one.sh #!/bin/bash#TDATE=`date -d '1 days ago' +%Y%m%d`#TTXT=`date -d '1 days ago' +'%Y''-'%m'-'%d`#INDATE=`date -d '1 days ago' +%Y%m%d`#INSERT_TIME=`date +"%Y%m%d"`for TDATE in {20170619..20170620}doT1=(1)TDATE0=`expr $TDATE - $T1`TTXT=${TDATE0:0:4}'-'${TDATE0:4:2}'-'${TDATE0:6:2}echo "DELETE FROM UNION_INTELLIGENT_POS T WHERE T.INSERT_TIME='$TDATE';TRUNCATE TABLE UNION_INTELLIGENT_POS_TEMP;exit">/home/oracle/u01/sqlldr/intelligent_pos/intelligent_pos.sql && sqlplus oggadmin/umsogg@umsdb @/home/oracle/u01/sqlldr/intelligent_pos/intelligent_pos.sqlfile_tt="/home/ftp/2gdata/intelligent_pos/$TDATE0/100200${TTXT}.txt"echo "loadinfile '${file_tt}' append INTO TABLE UNION_INTELLIGENT_POS_TEMPFIELDS TERMINATED BY '|'trailing nullcols (CHN_ID \"trim(:CHN_ID)\",PAY_NO \"trim(:PAY_NO)\",TRANS_TYPE \"trim(:TRANS_TYPE)\",MRCH_NAME \"trim(:MRCH_NAME)\",CARD_NO \"trim(:CARD_NO)\",PROCESS_CODE \"trim(:PROCESS_CODE)\",TRANS_AMT \"trim(:TRANS_AMT)\",TRMNL_FLOWNO \"trim(:TRMNL_FLOWNO)\",TRANS_TIME \"trim(:TRANS_TIME)\",TRANS_DATE \"trim(:TRANS_DATE)\",CARD_EXPIRE_DATE \"trim(:CARD_EXPIRE_DATE)\",SETTLEMENT_DATE \"trim(:SETTLEMENT_DATE)\",POS_ENTRYMODE \"trim(:POS_ENTRYMODE)\",ACQUIRING_CODE \"trim(:ACQUIRING_CODE)\",TRMNL_REFERNO \"trim(:TRMNL_REFERNO)\",AUTH_CODE \"trim(:AUTH_CODE)\",RESP_CODE \"trim(:RESP_CODE)\",TRMNL_NO \"trim(:TRMNL_NO)\",MRCH_NO \"trim(:MRCH_NO)\",TRMNL_BATCHNO \"trim(:TRMNL_BATCHNO)\",CARD_TYPE \"trim(:CARD_TYPE)\",CRE_TIME \"trim(:CRE_TIME)\",UPD_TIME \"trim(:UPD_TIME)\",EXT_ORD_NO \"trim(:EXT_ORD_NO)\",EXT_BILL_NO \"trim(:EXT_BILL_NO)\",APPID \"trim(:APPID)\",ORD_STATUS \"trim(:ORD_STATUS)\",INSERT_TIME CONSTANT '${TDATE}')">/home/oracle/u01/sqlldr/intelligent_pos/intelligent_pos.ctl && sqlldr oggadmin/umsogg@umsdb CONTROL=/home/oracle/u01/sqlldr/intelligent_pos/intelligent_pos.ctl log=/home/oracle/u01/sqlldr/intelligent_pos/intelligent_pos.log rows=200000 echo "INSERT INTO UNION_INTELLIGENT_POS SELECT DISTINCT * FROM UNION_INTELLIGENT_POS_TEMP t WHERE T.INSERT_TIME='$TDATE';COMMIT;TRUNCATE TABLE UNION_INTELLIGENT_POS_TEMP;EXIT;">/home/oracle/u01/sqlldr/intelligent_pos/intelligent_pos_temp.sql && sqlplus oggadmin/umsogg@umsdb @/home/oracle/u01/sqlldr/intelligent_pos/intelligent_pos_temp.sqldone
2.直接上脚本：2.1 >>
[root@oradata wx]# cat wx_all.sh #!/bin/bashsh /home/oracle/u01/sqlldr/wx/01_wx.shsh /home/oracle/u01/sqlldr/wx/02_wxwal.shsh /home/oracle/u01/sqlldr/wx/03_jyt_wk.shsh /home/oracle/u01/sqlldr/wx/04_zfb_nosett.shsh /home/oracle/u01/sqlldr/wx/05_zfb_sett.shsh /home/oracle/u01/sqlldr/wx/06_xnzh.sh2.2 >>[root@oradata wx]# cat 01_wx.sh #!/bin/bashTDATE=`date -d '1 days ago' +%Y%m%d`file_tt="/home/ftp/2gdata/wx/$TDATE/48023010_${TDATE}.txt"echo "loadinfile '${file_tt}' append INTO TABLE union_wxFIELDS TERMINATED BY '","'trailing nullcols ( dateofstlm , timeofloctxn , mchtid_sl , sl_appid , mchtid_js , js_appid , termid , amountoftxn , totalfee , invtfee , brandfee , txnclass , panval , card_issuer , card_type , singleorgcode , term_serial_no , swsystraceaudnum , referenceno , authorization_no , orig_term_serial_no , originalsysno , ori_referenceno , mcc , isdz , isfr , mchtid_sld , appid , pointfee , issuerfee , isqs , termid_sld , transaction , sendinsid , oriclearingdate , reserve , is_T0 , T0_invtfee , new3 , jdbj , jhf , zjqsf , new7 , new8 , new9 , ddh , tsjf , tsjflx , fbjgbs , kzhdj , fsylbzk , srcReserve , systemId , UNION_TYPE CONSTANT '01')">/home/oracle/u01/sqlldr/wx/wx.ctl && sqlldr oggadmin/umsogg@umsdb CONTROL=/home/oracle/u01/sqlldr/wx/wx.ctl log=/home/oracle/u01/sqlldr/wx/wx.log skip=1 rows=20000001_ * 至 06_* 都是类推 InvtFee position(242:253), PosRemark position(254:255), InvtFee3 position(261:267), AccountType position(269:270), TotalFee position(827:835)EXT_BILL_NO \"trim(:EXT_BILL_NO)\",APPID \"trim(:APPID)\",ORD_STATUS \"trim(:ORD_STATUS)\",INSERT_TIME CONSTANT '${TDATE}')3. 直接上脚本：