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

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

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

sqlplus -s username/password <

@/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;

结果呈现：