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 as ( select b.cpcnshortname,a.cpid,count(*) cc from v_ssrv_subscription a,scp_basic b
where a.cpid=b.cpid and  substr(a.starttime,1,8)= '''||dd||''' and a.subscribechannel !=107 and
a. servicetype=1
and a.cpid not in (18100543,18100548,18100552,18100538,18100540,18100547,18100542,35100005,35100080,35100030,35100001)
group by b.cpcnshortname,a.cpid)';
end;

----unload.sql脚本-----

select 'CPNAME'||' '||'CPID'||' '||'COUNT' FROM DUAL                                
UNION ALL                                                               
select to_char(cpcnshortname)||' '||to_char(cpid)||' '||to_char(cc) from MAZONGXIAO_1;

 

结果呈现：