案例:利用shell指令碼spool資料到一指定文

!/usr/bin/sh
###############################
# 1.define #
###############################

#----------1.1_set param-------#
#資料庫使用者名稱
ORA_USER=xxx
#資料庫使用者密碼
ORA_PASS=xxx
#登陸本地命名(也即@後的連線串)
ORA_LOCAL_NAME=db80
#存放匯出資料的本地路徑
DataPath='/home/oracle/'
echo "======================================================================="
echo "database username: ${ORA_USER}"
echo "the path of the file: ${DataPath}"

###############################
# 2.main program #
###############################
#----------2.1 export data------#
cd ${DataPath}
Cur_Date=`date +%Y%m%d%H%M%S`
TMPFILE="service_${Cur_Date}.txt"
echo "export max service data to file： ${TMPFILE}"

sqlplus -s </dev/null
set echo off;
set feedback off;
set head off
set pagesize 0;
set linesize 1000;
set termout off;
set trimout on;
set trimspool on;

spool ${TMPFILE}
select distinct a.icpcode||chr(31)||a.icpservid||chr(31)||a.servname||chr(31)||decode(a.servtype,2,'10',3,'10',6,'10',9,'10',1,'19',4,'19',5,'19',8,'19',21,'19',22,'19',23,'19')||chr(31)||decode(c.type,'nt:gcontent:redSegment','18','nt:gcontent:audio','12','nt:gcontent:cartoon','14','nt:gcontent:image','13','nt:gcontent:game','16','nt:gcontent:video','15','nt:gcontent:news','17','nt:gcontent:coloring','11') from v_service a,t_r_gcontent b,T_R_base c where a.icpcode=b.icpcode and a.icpservid=b.icpservid and b.id=c.id;
spool off;
exit;
EOF

[@more@]