Html5 sql报表,sql嵌入html格式显示报表

在使用监控系统报警的时候，如果显示的报警信息为纯粹的文本，会枯燥很多，而且看起来很不清晰。

比如我们要监控表空间的使用情况，输出列有表空间名，状态，区管理方式，总共的空间，使用的空间，剩余的空间等。

如果显示成下面的形式，尽管在输出中尝试使结果看起来清晰一些，但是还是事与愿违。

showtsps：Tablespace: TEST_INDEX-->Status: OLN-->Ext_MGR: LOCAL-->Total: 301843.875MB-->Free: 30945.25MB-->Used: 270898.625MB-->PFree:  10%-->
Tablespace: TEST_DATA-->Status: OLN-->Ext_MGR: LOCAL-->Total: 7960MB-->Free: 422.5MB-->Used: 7537.5MB-->PFree:   5%-->
Tablespace: ADVER_INDEX-->Status: OLN-->Ext_MGR: LOCAL-->Total: 550MB-->Free: 55.25MB-->Used: 494.75MB-->PFree:  10%-->
Tablespace:  showtsps：Tablespace: ACCSTAT_INDEX-->Status: OLN-->Ext_MGR: LOCAL-->Total: 301843.875MB-->Free: 30945.25MB-->Used: 270898.625MB-->PFree:  10%-->

我们来看看改进后的效果，这样可能就更清晰一些了，当然这个只是个改进的方向而已。

Tablespace: TEST_INDEX

Status: OLN

Ext_MGR: LOCAL

Total: 301843MB

Free: 30937MB

Used: 270906MB

PFree: 10%

Tablespace: TEST_DATA

Status: OLN

Ext_MGR: LOCAL

Total: 7960MB

Free: 422MB

Used: 7537MB

PFree: 5%

Tablespace: TEST_INDEX

Status: OLN

Ext_MGR: LOCAL

Total: 550MB

Free: 55MB

Used: 494MB

PFree: 10%

Tablespace: TEST_DATA2

Status: OLN

Ext_MGR: LOCAL

Total: 45167MB

Free: 2302MB

Used: 42865MB

PFree: 5%

Tablespace: TEST_INDEX2

Status: OLN

Ext_MGR: LOCAL

Total: 13990MB

Free: 688MB

Used: 13301MB

PFree: 5%

对于这种情况，大多数情况下就是监控指标能够实现，但是展现出来的效果不明显，这样很可能就会使得这个功能没有什么实用价值。

尽管sqlplus本身提供了 markup html on的选项，但是在orabbix中还是使用受限，所以只能另辟蹊径。

检查表空间的脚本如下，只是一个参考例子。

select

d.tablespace_name,

decode(d.status,

'ONLINE', 'OLN',

'READ ONLY', 'R/O',

d.status) status,

d.extent_management,

decode(d.allocation_type,

'USER','',

d.allocation_type) allocation_type,

(case

when initial_extent < 1048576

then lpad(round(initial_extent/1024,0),3)||'K'

else lpad(round(initial_extent/1024/1024,0),3)||'M'

end) Ext_Size,

NVL (a.bytes / 1024 / 1024, 0) MB,

NVL (f.bytes / 1024 / 1024, 0) free,

(NVL (a.bytes / 1024 / 1024, 0) - NVL (f.bytes / 1024 / 1024, 0)) used,

NVL (l.large / 1024 / 1024, 0) largest,

d.MAX_EXTENTS ,

lpad(round((f.bytes/a.bytes)*100,0),3) pfree,

(case when round(f.bytes/a.bytes*100,0) >= 20 then ' ' else '*' end) alrt

FROM sys.dba_tablespaces d,

(SELECT   tablespace_name, SUM(bytes) bytes

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT   tablespace_name, SUM(bytes) bytes

FROM dba_free_space

GROUP BY tablespace_name) f,

(SELECT   tablespace_name, MAX(bytes) large

FROM dba_free_space

GROUP BY tablespace_name) l

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = f.tablespace_name(+)

AND d.tablespace_name = l.tablespace_name(+)

AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')

UNION ALL

select

d.tablespace_name,

decode(d.status,

'ONLINE', 'OLN',

'READ ONLY', 'R/O',

d.status) status,

d.extent_management,

decode(d.allocation_type,

'UNIFORM','U',

'SYSTEM','A',

'USER','',

d.allocation_type) allocation_type,

(case

when initial_extent < 1048576

then lpad(round(initial_extent/1024,0),3)||'K'

else lpad(round(initial_extent/1024/1024,0),3)||'M'

end) Ext_Size,

NVL (a.bytes / 1024 / 1024, 0) MB,

(NVL (a.bytes / 1024 / 1024, 0) - NVL (t.bytes / 1024 / 1024, 0)) free,

NVL (t.bytes / 1024 / 1024, 0) used,

NVL (l.large / 1024 / 1024, 0) largest,

d.MAX_EXTENTS ,

lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3) pfree,

(case when nvl(round(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,0),100) >= 20 then ' ' else '*' end) alrt

FROM sys.dba_tablespaces d,

(SELECT   tablespace_name, SUM(bytes) bytes

FROM dba_temp_files

GROUP BY tablespace_name order by tablespace_name) a,

(SELECT   tablespace_name, SUM(bytes_used  ) bytes

FROM v\$temp_extent_pool

GROUP BY tablespace_name) t,

(SELECT   tablespace_name, MAX(bytes_cached) large

FROM v\$temp_extent_pool

GROUP BY tablespace_name order by tablespace_name) l

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = t.tablespace_name(+)

AND d.tablespace_name = l.tablespace_name(+)

AND d.extent_management LIKE 'LOCAL'

AND d.contents LIKE 'TEMPORARY'

ORDER by 1

可以看到脚本还是相对比较复杂的，这么复杂的功能都能实现，但是展现的效果却打了折扣，导致了功能上的丰富和显示效果不太相符。

这个时候还是得考虑嵌入点html代码，自己也着实温习了一下以前html的东西。

在经过了大量的测试之后，总算做出了一些改进。

最终需要sql查出来的结果需要时下面的格式：

Tablespace: TEST_TABLE SPACE_DATA

Status: OLN

Ext_MGR: LOCAL

Total: 29031.875MB

Free: 5.3125MB

Used: 29026.5625MB

PFree:   0%

Tablespace: TEST_TABLE2 SPACE_INDEX

Status: OLN

Ext_MGR: LOCAL

Total: 12876.8125MB

Free: 10.6875MB

Used: 12866.125MB

PFree:   0%

实现的sql的代码如下,这个时候已经不是单纯的脚本了，我叫它代码了。：)

select

'

'

Tablespace: '||d.tablespace_name||'',

'

Status: '||decode(d.status,

'ONLINE', 'OLN',

'READ ONLY', 'R/O',

d.status)||'

' status,

'

Ext_MGR: '||d.extent_management||'',

'

Total: '||trunc(NVL (a.bytes / 1024 / 1024, 0))||'MB' MB,

'

Free: '||trunc(NVL (f.bytes / 1024 / 1024, 0))||'MB' free,

'

Used: '||trunc((NVL (a.bytes / 1024 / 1024, 0) - NVL (f.bytes / 1024 / 1024, 0)))||'MB' used,

'

PFree: '||lpad(round((f.bytes/a.bytes)*100,0),3)||'%' pfree

FROM sys.dba_tablespaces d,

(SELECT   tablespace_name, SUM(bytes) bytes

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT   tablespace_name, SUM(bytes) bytes

FROM dba_free_space

GROUP BY tablespace_name) f,

(SELECT   tablespace_name, MAX(bytes) large

FROM dba_free_space

GROUP BY tablespace_name) l

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = f.tablespace_name(+)

AND d.tablespace_name = l.tablespace_name(+)

AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')

AND lpad(round((f.bytes/a.bytes)*100,0),3)<=10

UNION ALL

select

'

'

Tablespace:'||d.tablespace_name||'',

'

Status: '||decode(d.status,

'ONLINE', 'OLN',

'READ ONLY', 'R/O',

d.status)||'

' status,

'

Ext_MGR: '||d.extent_management||'',

'

Total: '||trunc(NVL (a.bytes / 1024 / 1024, 0))||'MB' MB,

'

Free: '||trunc((NVL (a.bytes / 1024 / 1024, 0) - NVL (t.bytes / 1024 / 1024, 0)))||'MB' free,

'

Used: '||trunc(NVL (t.bytes / 1024 / 1024, 0))||'MB' used,

'

PFree: '||lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3)||'%' pfree

FROM sys.dba_tablespaces d,

(SELECT   tablespace_name, SUM(bytes) bytes

FROM dba_temp_files

GROUP BY tablespace_name order by tablespace_name) a,

(SELECT   tablespace_name, SUM(bytes_used  ) bytes

FROM v$temp_extent_pool

GROUP BY tablespace_name) t,

(SELECT   tablespace_name, MAX(bytes_cached) large

FROM v$temp_extent_pool

GROUP BY tablespace_name order by tablespace_name) l

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = t.tablespace_name(+)

AND d.tablespace_name = l.tablespace_name(+)

AND d.extent_management LIKE 'LOCAL'

AND d.contents LIKE 'TEMPORARY'

AND lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3) <= 10

ORDER by 1;

可以在适当的时候来尝试使用一下，效果虽然还是丑了些，不过已经做过格式化了。

改进的方向其实还是很多。比如嵌入颜色，格式布局等等都是需要改进的方向。