目的：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 offset feedback offset heading offset termout offset pagesize 0spool &spf1@/home/zxin10/task/mazongxiao/unload1.sqlspool 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 anda. 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;
结果呈现：
