oracle输出表头,06_SPOOL导出_表头模式

–#############################################################################

–#############################################################################

–设置oracle参数信息

–设置字段间隔符，默认为空格

SET COLSEP ‘|’

–设置不列出命令清单

SET ECHO OFF

–设置不显示由查询返回的记录总数

SET FEEDBACK OFF

–设置不打印列标题

SET HEADING OFF

–设置查询结果不分页显示

SET PAGESIZE 0

–设置查询结果每一行显示的字符数

SET LINESIZE 600

–设置禁止在屏幕上显示查询输出结果

SET TERMOUT OFF

–设置去除每行显示结尾的空格

SET TRIMOUT ON

–设置对每一行的前后空格进行滤除

SET TRIMSPOOL ON

–设置输出数据的格式

COLUMN amount1 FORMAT 99999999999999.99

COLUMN amount2 FORMAT 99999999999999.99

COL today NEW_VALUE today NOPRINT

SELECT a.crndat today

FROM ds.sysctl a

JOIN mis.dtimes b

ON a.crndat = b.daykey

WHERE a.appnam=‘SSS’;

–每月1、15日凌晨生成要还款数据，传入日期为批量日期T-1

–COL daystr NEW_VALUE daystr noprint

–COL daycut NEW_VALUE daycut noprint

–select to_char(to_date(’&1’,‘YYYYMMDD’)+1,‘yyyymmdd’) daystr,

— to_char(to_date(’&1’,‘YYYYMMDD’)+1,‘dd’) daycut

– from dual;

–设置输出文件

–spool $HOME/ZRTP_CA/data/LOAN_NO_ACCT.&daystr.DAT

–处理分区

–declare

– TODAY varchar2(8); --明日

– TOMORROW varchar2(8); --明日

– MONTH_END_DATE varchar2(8); --月末

– DELETE_DAY varchar2(8); --删除日期 上两个月一号

– DATE_FLAG varchar2(8); --日期标志

– DORP_SQL1 varchar(200);

– DORP_SQL2 varchar(200);

– CREATE_SQL varchar(200);

–begin

– --先删除当日的分区以重跑

– begin

– select to_char(MONTH_END_DATE, ‘YYYYMMDD’),

– to_char(a.daykey, ‘YYYYMMDD’),

– to_char(a.daykey + 1, ‘YYYYMMDD’),

– to_char(add_months(a.daykey, -1), ‘YYYYMMDD’)

– into MONTH_END_DATE, TODAY, TOMORROW, DELETE_DAY

– from dw.dtimes a

– where daykey = to_date(’&1’,‘YYYYMMDD’);

– DORP_SQL1 := ‘alter table mis.af_actbal_pj drop partition af_actbal_pj_’||TODAY;

– execute immediate DORP_SQL1;

– exception

– when others then

– NULL;

– dbms_output.put_line(SQLERRM || DORP_SQL1);

– end;

– --建立当日分区

– begin

– CREATE_SQL := ‘alter table mis.af_actbal_pj add partition af_actbal_pj_’||TODAY

– || ’ values less than (to_date(’’’ || TOMORROW ||

– ‘’’,’‘YYYYMMDD’’))’;

– execute immediate CREATE_SQL;

– exception

– when others then

– NULL;

– dbms_output.put_line(SQLERRM || CREATE_SQL);

– end;

– --删除一个月之前的分区

– begin

– DATE_FLAG:=DELETE_DAY;

– if (DATE_FLAG != to_char(last_day(to_date(DATE_FLAG,‘YYYYMMDD’)),‘YYYYMMDD’)) then

– begin

– DORP_SQL2 := ‘alter table mis.af_actbal_pj drop partition af_actbal_pj_’||DATE_FLAG;

– execute immediate DORP_SQL2;

– exception

– when others then

– NULL;

– dbms_output.put_line(SQLERRM || DORP_SQL2);

– end;

– end if;

– end;

–end;

–/

prompt 数据处理开始…

prompt =======================================================================

delete from mis.af_actbal_pj

where crndat = to_date(’&daykey’, ‘YYYYMMDD’);

prompt =======================================================================

prompt 开始出具报表

–设置输出文件

spool gj_retfix.txt

–查询语句

select t.supbak,r.orgidt,r.dpttyp,r.curbal,r.orgidt||r.depnum||r.curcde||r.dpttyp||r.resbit||r.seqnu

m||r.chkbit,r.intrat,r.opndat,r.valdat,r.expdat

from retfix r join tmporg t on t.orgidt=r.orgidt

where r.dpttyp=‘96’ and r.dptprd=‘36’ and r.actsts=‘1’

and r.valdat between ‘2007-01-01’ and ‘&daystr’

;

–输出完成

spool off;

prompt 出具报表结束

prompt =======================================================================

–退出

exit