利用SQL*PLUS导出成EXCEL和html的功能实现报表统计

转自 http://blog.chinaunix.net/uid-7589639-id-3247473.html

利用SQL*PLUS导出成EXCEL和html的功能实现报表统计:

也就是生成HTML格式，但是同样的格式输出到EXCEL中也能正常显示。

关键就是这些参数的设定

set markup html on entmap ON spool on preformat off

参数注解如下:

========================================================================

TABLE text 设置<TABLE>标签的属性，如BORDER, CELLPADDING, CELLSPACING和WIDTH。默认情况下，<TABLE> 的WIDTH属性设置为90%，BORDER属性设置为1。

ENTMAP {ON|OFF} 指定在SQL * Plus中是否用HTML字符实体如<, >, " and &等替换特殊字符<, >, " and & 。默认设置是ON。

SPOOL {ON|OFF} 指定是否在SQL*Plus生成HTML标签<HTML> 和<BODY>, </BODY> 和</HTML>。默认是OFF。
注：这是一个后台打印操作，只有在生成SPOOL文件生效，在屏幕上并不生效。

PRE[FORMAT] {ON|OFF} 指定SQL*Plus生成HTML时输出<PRE>标签还是HTML表格，默认是OFF，因此默认输出是写HTML表格。

=========================================================================

通过SQL*PLUS我们可以构建友好的输出，满足多样化用户需求。

本例通过简单示例，介绍通过sql*plus输出xls，html两种格式文件. 其实html格式就是可以用xls来打开的.

首先创建两个脚本:

1.get_d_stat.sh 用以设置环境，主要调用具体脚本
2.get_d_stat.sql 为获取具体数据之脚本

创建实验表t_grade如下：

    create table t_grade(id int,name varchar2(10),subject varchar2(20),grade number);
    insert into t_grade values(1,'ZORRO','语文',70);
    insert into t_grade values(2,'ZORRO','数学',80);
    insert into t_grade values(3,'ZORRO','英语',75);
    insert into t_grade values(4,'SEKER','语文',65);
    insert into t_grade values(5,'SEKER','数学',75);
    insert into t_grade values(6,'SEKER','英语',60);
    insert into t_grade values(7,'BLUES','语文',60);
    insert into t_grade values(8,'BLUES','数学',90);
    insert into t_grade values(9,'PG','数学',80);
    insert into t_grade values(10,'PG','英语',90);
    insert into t_grade values(11,'TOM','化学',90);
    commit;

脚本get_d_stat.sh内容如下：

    sqlplus -s dba_user/dbapasswd<<EOF
    set linesize 200
    set term off verify off feedback off pagesize 999
    set markup html on entmap ON spool on preformat off
    spool /apps/dba_tool/get_data/get_d_stat_`date --date "1 days ago" +%F`.xls
    --spool get_d_stat_`date +%F`.xls
    --spool tables.html
    @/apps/dba_tool/get_data/get_d_stat.sql;
    spool off
    exit;
    EOF

脚本get_d_stat.sql 内容如下：

select 
name,
sum(case when SUBJECT='语文' then GRADE else 0 end) "语文",
sum(case when SUBJECT='数学' then GRADE else 0 end) "数学",
sum(case when SUBJECT='英语' then GRADE else 0 end) "英语",
sum(case when SUBJECT='化学' then GRADE else 0 end) "化学" 
from t_grade group by name;

运行脚本get_d_stat.sh后，会在/apps/dba_tool/get_data/目录下生成get_d_stat_2012-06-18.xls的报表文件。效果图如下：

NAME	语文	数学	英语	化学
SEKER	65	75	60	0
BLUES	60	90	0	0
TOM	0	0	0	90
PG	0	80	90	0
ZORRO	70	80	75	0

到此为止，利用SQL*PLUS导出成EXCEL和html的功能实现报表统计已经成功。

以下内容转自 http://blog.csdn.net/robinson1988/article/details/5099254

我们可以在SQLPLUS中手工运行AWR,ASH的脚本生成HTML报表，下面来简单讲讲怎么利用SQLPLUS来生成HTML报表

在SQLPLUS中有个命令(具体可以参考官方文档SQLPLUS部分)

SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}]

一：首先在SQLPLUS中设置

set mark html on spool on entmap off pre off

这样设置过后，利用spool 导出为html，SQLPLUS将会自动的为我们创建HTML格式,

注意：如果设置pre 为on,那么输出的不是HTML格式，默认为off,

entmap 默认为on ，它会将>换成HTML中的&gt来显示，所以我将其设置为off

二：为了格式化输出，我们需要对输出内容格式化

set echo off 这样设置之后不会在HTML报表中显示执行过的SQL语句

set feedback off 这样设置过后不会在HTML报表中显示已经处理多少行

set heading on 设置标题显示

set termout off 关闭在屏幕上的输出，这样可以加快spool执行速度

set linesize 200 设置行宽度为120

set pagesize 1000 设置一页显示1000行

set trimout off 去掉每行后面多余的空格

三:利用spool 输出为 *.html

spool c:/test.html

四：写下要执行的SQL语句

五：spool off

例如要查询表空间利用率，并将结果输出为HTML报表格式:

将下面的语句保存为一个SQL脚本，然后在SQLPLUS中调用

SET MARKUP HTML ON SPOOL ON pre off entmap off
SET ECHO OFF
SET TERMOUT OFF
SET TRIMOUT OFF
set feedback off
set heading on
set linesize 200
set pagesize 10000
col tablespace_name format a15
col total_space format a10
col free_space format a10
col used_space format a10
col used_rate format 99.99
spool c:/test.html
select a.tablespace_name,a.total_space_Mb||'m' total_space,b.free_space_Mb||'m'

free_space,a.total_space_Mb-b.free_space_Mb||'m' used_space,
(1-(b.free_space_Mb/a.total_space_Mb))*100 used_rate,a.total_blocks,b.free_blocks from                   
(select tablespace_name,sum(bytes)/1024/1024 total_space_Mb,sum(blocks) total_blocks from dba_data_files

group by tablespace_name) a,
(select tablespace_name, sum((bytes)/1024/1024) free_space_Mb,sum(blocks) free_blocks from dba_free_space

group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by used_rate desc;
spool off
SQL> @test;

报表的截图

posted @ 2014-11-11 18:27 princessd8251 阅读(471) 评论(0) 收藏举报

刷新页面返回顶部