BayaiM__SQLLDR_linux_shell高级版
备注：
1.因公司在职，商业机密，顾IP地方加了"*"号，你可以任意写一个数字做IP做就好。
2.不要瞎BB，哥自己写的，挺实用的，里面告诉你许多来龙去脉。
3.也算是知识分享，不算泄露什么鸟。
=========================================================
1.因公司在职，商业机密，顾IP地方加了"*"号，你可以任意写一个数字做IP做就好。
2.不要瞎BB，哥自己写的，挺实用的，里面告诉你许多来龙去脉。
3.也算是知识分享，不算泄露什么鸟。
=========================================================
1.直接上脚本：
-----------------------------------------------------------------------------------------------------------------
[root@oradata script]# cat wx_zfb.sh
#!/bin/bash
TDATE=`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}" ]; then
rm -rf "${Path_wx}"
fi
if [ -d "${Path_wxwal}" ]; then
rm -rf "${Path_wxwal}"
fi
if [ -d "${Path_jyt_wk}" ]; then
rm -rf "${Path_jyt_wk}"
fi
if [ -d "${Path_zfb_nosett}" ]; then
rm -rf "${Path_zfb_nosett}"
fi
if [ -d "${Path_zfb_sett}" ]; then
rm -rf "${Path_zfb_sett}"
fi
if [ -d "${Path_xnzh}" ]; then
rm -rf "${Path_xnzh}"
fi
mkdir -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 ls3ppqvm
binary
cd /xqfdzwj/wx
lcd ${Path_wx}
prompt
mget 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/wx/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
#gunzip -n -q /home/ftp/2gdata/wx/${TDATE}/48023010_${TDATE}.txt.gz
ftp -n<<!
open 172.16.3.*
user js_ls ls3ppqvm
binary
cd /xqfdzwj/wxwal
lcd ${Path_wxwal}
prompt
get 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/wxwal/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/wxwal/${TDATE}/48023010_${TDATE}.txt.gz
ftp -n<<!
open 172.16.3.*
user js_ls ls3ppqvm
binary
cd /xqfdzwj/jyt_wk
lcd ${Path_jyt_wk}
prompt
get 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/jyt_wk/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/jyt_wk/${TDATE}/48023010_${TDATE}.txt.gz
ftp -n<<!
open 172.16.3.*
user js_ls ls3ppqvm
binary
cd /xqfdzwj/zfb_nosett
lcd ${Path_zfb_nosett}
prompt
get 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/zfb_nosett/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/zfb_nosett/${TDATE}/48023010_${TDATE}.txt.gz
ftp -n<<!
open 172.16.3.*
user js_ls ls3ppqvm
binary
cd /xqfdzwj/zfb_sett
lcd ${Path_zfb_sett}
prompt
get 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/zfb_sett/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/zfb_sett/${TDATE}/48023010_${TDATE}.txt.gz
ftp -n<<!
open 172.16.3.*
user js_ls ls3ppqvm
binary
cd /xqfdzwj/xnzh
lcd ${Path_xnzh}
prompt
get 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/xnzh/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/xnzh/${TDATE}/48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/wx/${TDATE}/48023010_${TDATE}.txt.gz
----------------------------------------------------------------------------------------------------------------------
#!/bin/bash
sh /home/oracle/u01/sqlldr/wx/01_wx.sh
sh /home/oracle/u01/sqlldr/wx/02_wxwal.sh
sh /home/oracle/u01/sqlldr/wx/03_jyt_wk.sh
sh /home/oracle/u01/sqlldr/wx/04_zfb_nosett.sh
sh /home/oracle/u01/sqlldr/wx/05_zfb_sett.sh
sh /home/oracle/u01/sqlldr/wx/06_xnzh.sh
2.2 >>
[root@oradata wx]# cat 01_wx.sh
#!/bin/bash
TDATE=`date -d '1 days ago' +%Y%m%d`
file_tt="/home/ftp/2gdata/wx/$TDATE/48023010_${TDATE}.txt"
echo "
load
infile '${file_tt}'
append INTO TABLE union_wx
FIELDS 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=200000
01_ * 至 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.
-------------------------------------------------------------------------------------
[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}
do
T1=(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.sql
file_tt="/home/ftp/2gdata/intelligent_pos/$TDATE0/100200${TTXT}.txt"
echo "
load
infile '${file_tt}'
append INTO TABLE UNION_INTELLIGENT_POS_TEMP
FIELDS 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.sql
done
-----------------------------------------------------------------------------------------------------------------
[root@oradata script]# cat wx_zfb.sh
#!/bin/bash
TDATE=`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}" ]; then
rm -rf "${Path_wx}"
fi
if [ -d "${Path_wxwal}" ]; then
rm -rf "${Path_wxwal}"
fi
if [ -d "${Path_jyt_wk}" ]; then
rm -rf "${Path_jyt_wk}"
fi
if [ -d "${Path_zfb_nosett}" ]; then
rm -rf "${Path_zfb_nosett}"
fi
if [ -d "${Path_zfb_sett}" ]; then
rm -rf "${Path_zfb_sett}"
fi
if [ -d "${Path_xnzh}" ]; then
rm -rf "${Path_xnzh}"
fi
mkdir -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 ls3ppqvm
binary
cd /xqfdzwj/wx
lcd ${Path_wx}
prompt
mget 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/wx/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
#gunzip -n -q /home/ftp/2gdata/wx/${TDATE}/48023010_${TDATE}.txt.gz
ftp -n<<!
open 172.16.3.*
user js_ls ls3ppqvm
binary
cd /xqfdzwj/wxwal
lcd ${Path_wxwal}
prompt
get 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/wxwal/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/wxwal/${TDATE}/48023010_${TDATE}.txt.gz
ftp -n<<!
open 172.16.3.*
user js_ls ls3ppqvm
binary
cd /xqfdzwj/jyt_wk
lcd ${Path_jyt_wk}
prompt
get 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/jyt_wk/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/jyt_wk/${TDATE}/48023010_${TDATE}.txt.gz
ftp -n<<!
open 172.16.3.*
user js_ls ls3ppqvm
binary
cd /xqfdzwj/zfb_nosett
lcd ${Path_zfb_nosett}
prompt
get 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/zfb_nosett/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/zfb_nosett/${TDATE}/48023010_${TDATE}.txt.gz
ftp -n<<!
open 172.16.3.*
user js_ls ls3ppqvm
binary
cd /xqfdzwj/zfb_sett
lcd ${Path_zfb_sett}
prompt
get 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/zfb_sett/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/zfb_sett/${TDATE}/48023010_${TDATE}.txt.gz
ftp -n<<!
open 172.16.3.*
user js_ls ls3ppqvm
binary
cd /xqfdzwj/xnzh
lcd ${Path_xnzh}
prompt
get 48023010_${TDATE}.txt.gz
close
bye
!
#cd /home/ftp/2gdata/xnzh/${TDATE} && gunzip -n -q 48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/xnzh/${TDATE}/48023010_${TDATE}.txt.gz
gunzip -n -q /home/ftp/2gdata/wx/${TDATE}/48023010_${TDATE}.txt.gz
----------------------------------------------------------------------------------------------------------------------
2.直接上脚本：
2.1 >>
[root@oradata wx]# cat wx_all.sh 2.1 >>
#!/bin/bash
sh /home/oracle/u01/sqlldr/wx/01_wx.sh
sh /home/oracle/u01/sqlldr/wx/02_wxwal.sh
sh /home/oracle/u01/sqlldr/wx/03_jyt_wk.sh
sh /home/oracle/u01/sqlldr/wx/04_zfb_nosett.sh
sh /home/oracle/u01/sqlldr/wx/05_zfb_sett.sh
sh /home/oracle/u01/sqlldr/wx/06_xnzh.sh
2.2 >>
[root@oradata wx]# cat 01_wx.sh
#!/bin/bash
TDATE=`date -d '1 days ago' +%Y%m%d`
file_tt="/home/ftp/2gdata/wx/$TDATE/48023010_${TDATE}.txt"
echo "
load
infile '${file_tt}'
append INTO TABLE union_wx
FIELDS 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=200000
01_ * 至 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.
直接上脚本：
-------------------------------------------------------------------------------------
[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}
do
T1=(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.sql
file_tt="/home/ftp/2gdata/intelligent_pos/$TDATE0/100200${TTXT}.txt"
echo "
load
infile '${file_tt}'
append INTO TABLE UNION_INTELLIGENT_POS_TEMP
FIELDS 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.sql
done