在SQL*Plus下导出数据为Excel

set linesize 200 #根据实际应用设置行的大小 
set term off verify off feedback off pagesize 0
set markup html on entmap ON spool on preformat off
spool tables.xls
@get_tables.sql   #此处为SQL脚本或SQL语句
spool off

 

Shell脚本中使用方法：

sqlplus后面必须跟 -S

#!/bin/bash
export CURRDATE=`date -d '1 days ago' +%Y-%m-%d`
export FILEPATH=/u01/oracle/backup
rm ${FILEPATH}/*.xls
sqlplus -S /nolog <<EOF
conn / as sysdba
set term off verify off feedback off pagesize 0
set markup html on entmap ON spool on preformat OFF
spool ${CURRDATE}.xls
SELECT '统计日期', '商户号', '商户名', '终端号', '业务类型'
  FROM dual;
spool off
set markup html off entmap off
exit;
EOF