将执行结果输出到CSV文件里

最新推荐文章于 2021-12-19 12:00:58 发布

luck2001

最新推荐文章于 2021-12-19 12:00:58 发布

阅读量2.1k

点赞数

分类专栏： ORACLE技术笔记

本文链接：https://blog.csdn.net/luck2001/article/details/49737143

版权

ORACLE技术笔记专栏收录该内容

30 篇文章 0 订阅

订阅专栏

set linesize 1000
set pagesize 1000
set time on timing on
set null '#'
set colsep ','
set trimspool on
set heading on
set echo off

col batchname format a40
col batchstart format a26
col batchend format a26

SPOOL output.csv;

-- 算出执行开始结束时间的间隔

select batchname,to_char(batchstart,'YYYY-MM-DD HH24:MI:SS.FF') batchstart,to_char(batchend,'YYYY-MM-DD HH24:MI:SS.FF')batchend
, EXTRACT(DAY    FROM batchend-batchstart) * 86400
+ EXTRACT(HOUR    FROM batchend-batchstart) * 3600
+ EXTRACT(MINUTE    FROM batchend-batchstart) *    60
+ EXTRACT(SECOND    FROM batchend-batchstart) as time
from hd_tbl hd
where hd.logid > 0
and   hd.batchstart is not null
and   hd.batchend is not null
and   batchname like '%&1%'
and   batchstart > to_date('&2','YYYY-MM-DD HH24:MI:SS')
and   batchend < to_date('&3','YYYY-MM-DD HH24:MI:SS')
union all
select '実行回数',to_char(cnt),'平均時間(秒)',avgts
from (
select count(*) cnt,
avg(EXTRACT(DAY    FROM batchend-batchstart) * 86400
+ EXTRACT(HOUR    FROM batchend-batchstart) * 3600
+ EXTRACT(MINUTE    FROM batchend-batchstart) *    60
+ EXTRACT(SECOND    FROM batchend-batchstart)) avgts
from hd_tbl hd
where hd.logid > 0
and   hd.batchstart is not null
and   hd.batchend is not null
and   batchname like '%&1%'
and   batchstart > to_date('&2','YYYY-MM-DD HH24:MI:SS')
and   batchend < to_date('&3','YYYY-MM-DD HH24:MI:SS')
);