linux shell實現從資料庫中查詢資料並把資料用csv格式發送郵件

$ORACLE_HOME/bin/sqlplus -S username/password@instanceName > /dev/null 2>&1 <<!!<br />#對sqlplus作一些設定，使之只輸出我們需要的文本<br />set echo off<br />set pagesize 0<br />set verify off<br />set feedback off<br />set termout off<br />set linesize 3000<br />set trimspool on<br />#查詢輸出到臨時檔案<br />spool /tmp/some_tmp.csv<br />#select語句<br />select column1||','||column2||','||column3 ....<br />from some_table where condition<br />spool off<br />#這裡可以添加多個查詢,且每個查詢可輸出到不同的檔案,如下面注釋<br />#spool /tmp/some_tmp.csv<br />#select column1||','||column2||','||column3 ....<br />#from some_table where condition<br />#spool off<br />set markup HTML off<br />quit<br />!!<br />#計算記錄總行數，如果是0則不發郵件<br />NUM=`cat /tmp/some_tmp.csv | wc -l`<br />if[ $NUM -gt 0 ]<br />then<br />#先寫excel的每列的title<br />echo 'Title_Of_Column1, Title_Of_Column2, ....Title_Of_ColumnN' > /tmp/attachement.csv<br />#資料本文<br />cat /tmp/some_tmp.csv >> /tmp/attachement.csv<br />#發郵件<br />(<br />echo "From: user@somehost.com"<br />echo "To: user1@somehost.com"<br />echo "MIME-Version: 1.0"<br />echo "Content-Type: multipart/mixed;"<br />echo ' boundary="A_Boundary_Name_You_Can_Change_It"'<br />echo "Subject: Subject"<br />echo ""<br />echo ""<br />#附件分隔字元，上面的boundary前面加--<br />echo "--A_Boundary_Name_You_Can_Change_It"<br />echo 'Content-Transfer-Encoding: x-uuencode'<br />echo 'Content-Type: text/csv;'<br />echo ' name="attachement.csv"'<br />echo 'Content-Disposition: attachment;'<br />echo ' filename="attachement.csv"'<br />echo ""<br />uuencode /tmp/attachement.csv attachement.csv<br />echo "--A_Boundary_Name_You_Can_Change_It"<br />#附件結束<br />) | mailx -t<br />fi<br />#刪除臨時檔案<br />rm -f /tmp/*.csv<br />exit 0<br />