oracle自动生成excel报表（时间为变量，条件含变量）

目的：unix+oracle下每日生成前一天某统计报表，以一张统计表为例。

 

----在unix下执行sh脚本----

sqlplus -s username/password <<EOF
@/home/zxin10/task/mazongxiao/sql.sql
/
column spf1 new_value spf1 noprint

select '/home/zxin10/mazongxiao/'||to_char(sysdate,'yyyymmdd')||'_sms_sp_count.xls'  spf1 from dual;

set echo off
set feedback off
set heading off
set termout off
set pagesize 0
spool &spf1
@/home/zxin10/task/mazongxiao/unload1.sql
spool off

EOF
------sh中的sql脚本-----------

declare
  dd varchar(8);
  num int;
begin
  select to_char(sysdate-1,'yyyymmdd')into dd from dual;
  select count(1) into num from user_tables where table_name='MAZONGXIAO_1';
   if num>0 then
    execute immediate 'drop table MAZONGXIAO_1';
   end if;
  execute immediate 'create table MAZONGXIAO_1 a