linux shell实现从数据库中查询数据并把数据用csv格式发送邮件

$ORACLE_HOME/bin/sqlplus -S username/password@instanceName > /dev/null 2>&1 <<!! #对sqlplus作一些设置，使之只输出我们需要的文本 set echo off set pagesize 0 set verify off set feedback off set termout off set linesize 3000 set trimspool on #查询输出到临时文件 spool /tmp/some_tmp.csv #select语句 select column1||','||column2||','||column3 .... from some_table where condition spool off #这里可以添加多个查询,且每个查询可输出到不同的文件,如下面注释 #spool /tmp/some_tmp.csv #select column1||','||column2||','||column3 .... #from some_table where condition #spool off set markup HTML off quit !! #计算记录总行数，如果是0则不发邮件 NUM=`cat /tmp/some_tmp.csv | wc -l` if[ $NUM -gt 0 ] then #先写excel的每列的title echo 'Title_Of_Column1, Title_Of_Column2, ....Title_Of_ColumnN' > /tmp/attachement.csv #数据正文 cat /tmp/some_tmp.csv >> /tmp/attachement.csv #发邮件 ( echo "From: user@somehost.com" echo "To: user1@somehost.com" echo "MIME-Version: 1.0" echo "Content-Type: multipart/mixed;" echo ' boundary="A_Boundary_Name_You_Can_Change_It"' echo "Subject: Subject" echo "" echo "" #附件分隔符，上面的boundary前面加-- echo "--A_Boundary_Name_You_Can_Change_It" echo 'Content-Transfer-Encoding: x-uuencode' echo 'Content-Type: text/csv;' echo ' name="attachement.csv"' echo 'Content-Disposition: attachment;' echo ' filename="attachement.csv"' echo "" uuencode /tmp/attachement.csv attachement.csv echo "--A_Boundary_Name_You_Can_Change_It" #附件结束 ) | mailx -t fi #删除临时文件 rm -f /tmp/*.csv exit 0