網頁檔案自動下載入庫指令碼_shell_sqlldr

#一個從網上自動下載指定檔案併入庫的shell指令碼。

#!/bin/bash
# ############################################################################
#
#                    created by yallonking
#
#                    2012-4-2  zxbp.sh
#
#                    Email:oraking_job@163.com
#
# ############################################################################
ORACLE_SID=m1hf; export ORACLE_SID
ORACLE_BASE=/m1hf; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/oracle; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
NLS_LANG="Simplified Chinese_china".ZHS16GBK; export NLS_LANG
PATH=$PATH:$HOME/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH

#獲取檔案
wget -t 5 "http://202.108.253.184/csvsave.asp?spcode=SH816000" -O /opt/wyl/downloads/SH816000_`date +%Y%m%d`.txt -o /opt/wyl/sqlldr/logs/SH816000_`date +%Y%m%d`.log
wget -t 5 "http://202.108.253.184/csvsave.asp?spcode=SH818100" -O /opt/wyl/downloads/SH818100_`date +%Y%m%d`.txt -o /opt/wyl/sqlldr/logs/SH818100_`date +%Y%m%d`.log
wget -t 5 "http://202.108.253.184/csvsave.asp?spcode=SH818200" -O /opt/wyl/downloads/SH818200_`date +%Y%m%d`.txt -o /opt/wyl/sqlldr/logs/SH818200_`date +%Y%m%d`.log
wget -t 5 "http://202.108.253.184/csvsave.asp?spcode=SH818300" -O /opt/wyl/downloads/SH818300_`date +%Y%m%d`.txt -o /opt/wyl/sqlldr/logs/SH818300_`date +%Y%m%d`.log

#刪除以前的表記錄
sqlplus /nolog<conn username/password
truncate table idx.idxzxbpquoteday_bak;
exit;
eof

#檔案入庫
file_in=/opt/wyl/downloads/
dos2unix /opt/wyl/downloads/*
file_temp=/opt/wyl/sqlldr/file_temp
for file_a in ${file_in}/*; do
    cp $file_a $file_temp/test.csv
    filename=`basename $file_a`
    sqlldr username/password control=/opt/wyl/sqlldr/zxbp.ctl bad=/opt/wyl/sqlldr/zxbp.bad  log=/opt/wyl/sqlldr/test.log  direct=true
    rm -rf $file_temp/*
    mv /opt/wyl/sqlldr/test.log /opt/wyl/sqlldr/logs/$filename
done
rm -rf /opt/wyl/downloads/*

#取消記錄中多餘引號,並去掉重複記錄行
sqlplus /nolog<conn username/password
truncate table idx.idxzxbpquoteday;
drop table idx.idxzxbpquoteday;
create table idx.idxzxbpquoteday as select trim('"' from secucode) as secucode,
trim('"' from secuname) as secuname,
tradingday,
openprice,
highprice,
lowprice,
closeprice,
turnovervolume,
turnovervalue
from idx.idxzxbpquoteday_bak;

delete from idx.idxzxbpquoteday where rowid in
(select a.rowid from idx.idxzxbpquoteday a,idx.idxzxbpquoteday b
where a.rowid > b.rowid
and a.secucode=b.secucode
and a.secuname=b.secuname
and a.tradingday=b.tradingday
and a.openprice=b.openprice
and a.highprice=b.highprice
and a.lowprice=b.lowprice
and a.closeprice=b.closeprice
and a.turnovervolume=b.turnovervolume
and a.turnovervalue=b.turnovervalue);
commit;

SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
select max(tradingday) as table_last_updateday from idx.idxzxbpquoteday;
exit;
eof

 

#在crontab中制定此指令碼執行計劃即可！