sql嵌入html格式顯示報表
在使用監控系統報警的時候,如果顯示的報警資訊為純粹的文字,會枯燥很多,而且看起來很不清晰。
比如我們要監控表空間的使用情況,輸出列有表空間名,狀態,區管理方式,總共的空間,使用的空間,剩餘的空間等。
如果顯示成下面的形式,儘管在輸出中嘗試使結果看起來清晰一些,但是還是事與願違。
對於這種情況,大多數情況下就是監控指標能夠實現,但是展現出來的效果不明顯,這樣很可能就會使得這個功能沒有什麼實用價值。
儘管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查出來的結果需要時下面的格式:
<table border='1' width='90%' align='center' ><tr><td width="40%">Tablespace: TEST_TABLE
SPACE_DATA</td>
<td width="10%">Status: OLN</td> <td width="10%">Ext_MGR: LOCAL</td>
<td width="10%">Total: 29031.875MB</td>
<td width="10%">Free: 5.3125MB</td>
<td width="10%">Used: 29026.5625MB</td>
<td width="10%">PFree: 0%</td></tr></table>
<table border='1' width='90%' align='center' ><tr><td width="40%">Tablespace: TEST_TABLE2
SPACE_INDEX</td>
<td width="10%">Status: OLN</td> <td width="10%">Ext_MGR: LOCAL</td>
<td width="10%">Total: 12876.8125MB</td>
<td width="10%">Free: 10.6875MB</td>
<td width="10%">Used: 12866.125MB</td>
<td width="10%">PFree: 0%</td></tr></table>
實現的sql的程式碼如下,這個時候已經不是單純的指令碼了,我叫它程式碼了。:)
select
'<table border='||chr(34)||'1'||chr(34)||' width='||chr(34)||'90%'||chr(34)||' align='||chr(34)||'center'||chr(34)||'<tr>'||
'<td width='||chr(34)||'40%'||chr(34)||'>Tablespace: '||d.tablespace_name||'</td>',
'<td width='||chr(34)||'10%'||chr(34)||'>Status: '||decode(d.status,
'ONLINE', 'OLN',
'READ ONLY', 'R/O',
d.status)||'</td>' status,
'<td width='||chr(34)||'10%'||chr(34)||'>Ext_MGR: '||d.extent_management||'</td>',
'<td width='||chr(34)||'10%'||chr(34)||'>Total: '||trunc(NVL (a.bytes / 1024 / 1024, 0))||'MB</td>' MB,
'<td width='||chr(34)||'10%'||chr(34)||'>Free: '||trunc(NVL (f.bytes / 1024 / 1024, 0))||'MB</td>' free,
'<td width='||chr(34)||'10%'||chr(34)||'>Used: '||trunc((NVL (a.bytes / 1024 / 1024, 0) - NVL (f.bytes / 1024 / 1024, 0)))||'MB</td>' used,
'<td width='||chr(34)||'10%'||chr(34)||'>PFree: '||lpad(round((f.bytes/a.bytes)*100,0),3)||'%</td></tr></table>' 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
'<table border='||chr(34)||'1'||chr(34)||' width='||chr(34)||'90%'||chr(34)||' align='||chr(34)||'center'||chr(34)||'<tr>'||
'<td width='||chr(34)||'40%'||chr(34)||'>Tablespace:'||d.tablespace_name||'</td>',
'<td>Status: '||decode(d.status,
'ONLINE', 'OLN',
'READ ONLY', 'R/O',
d.status)||'</td>' status,
'<td width='||chr(34)||'10%'||chr(34)||'>Ext_MGR: '||d.extent_management||'</td>',
'<td width='||chr(34)||'10%'||chr(34)||'>Total: '||trunc(NVL (a.bytes / 1024 / 1024, 0))||'MB</td>' MB,
'<td width='||chr(34)||'10%'||chr(34)||'>Free: '||trunc((NVL (a.bytes / 1024 / 1024, 0) - NVL (t.bytes / 1024 / 1024, 0)))||'MB</td>' free,
'<td width='||chr(34)||'10%'||chr(34)||'>Used: '||trunc(NVL (t.bytes / 1024 / 1024, 0))||'MB</td>' used,
'<td width='||chr(34)||'10%'||chr(34)||'>PFree: '||lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3)||'%</td></tr></table>' 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;
可以在適當的時候來嘗試使用一下,效果雖然還是醜了些,不過已經做過格式化了。
改進的方向其實還是很多。比如嵌入顏色,格式佈局等等都是需要改進的方向。
比如我們要監控表空間的使用情況,輸出列有表空間名,狀態,區管理方式,總共的空間,使用的空間,剩餘的空間等。
如果顯示成下面的形式,儘管在輸出中嘗試使結果看起來清晰一些,但是還是事與願違。
showtsps:Tablespace: TEST_INDEX-->Status: OLN-->Ext_MGR: LOCAL-->Total: 301843.875MB-->Free: 30945.25MB-->Used: 270898.625MB-->PFree: 10%--> <br />Tablespace: TEST_DATA-->Status: OLN-->Ext_MGR: LOCAL-->Total: 7960MB-->Free: 422.5MB-->Used: 7537.5MB-->PFree: 5%--> <br />Tablespace: ADVER_INDEX-->Status: OLN-->Ext_MGR: LOCAL-->Total: 550MB-->Free: 55.25MB-->Used: 494.75MB-->PFree: 10%--> <br />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查出來的結果需要時下面的格式:
<table border='1' width='90%' align='center' ><tr><td width="40%">Tablespace: TEST_TABLE
SPACE_DATA</td>
<td width="10%">Status: OLN</td> <td width="10%">Ext_MGR: LOCAL</td>
<td width="10%">Total: 29031.875MB</td>
<td width="10%">Free: 5.3125MB</td>
<td width="10%">Used: 29026.5625MB</td>
<td width="10%">PFree: 0%</td></tr></table>
<table border='1' width='90%' align='center' ><tr><td width="40%">Tablespace: TEST_TABLE2
SPACE_INDEX</td>
<td width="10%">Status: OLN</td> <td width="10%">Ext_MGR: LOCAL</td>
<td width="10%">Total: 12876.8125MB</td>
<td width="10%">Free: 10.6875MB</td>
<td width="10%">Used: 12866.125MB</td>
<td width="10%">PFree: 0%</td></tr></table>
實現的sql的程式碼如下,這個時候已經不是單純的指令碼了,我叫它程式碼了。:)
select
'<table border='||chr(34)||'1'||chr(34)||' width='||chr(34)||'90%'||chr(34)||' align='||chr(34)||'center'||chr(34)||'<tr>'||
'<td width='||chr(34)||'40%'||chr(34)||'>Tablespace: '||d.tablespace_name||'</td>',
'<td width='||chr(34)||'10%'||chr(34)||'>Status: '||decode(d.status,
'ONLINE', 'OLN',
'READ ONLY', 'R/O',
d.status)||'</td>' status,
'<td width='||chr(34)||'10%'||chr(34)||'>Ext_MGR: '||d.extent_management||'</td>',
'<td width='||chr(34)||'10%'||chr(34)||'>Total: '||trunc(NVL (a.bytes / 1024 / 1024, 0))||'MB</td>' MB,
'<td width='||chr(34)||'10%'||chr(34)||'>Free: '||trunc(NVL (f.bytes / 1024 / 1024, 0))||'MB</td>' free,
'<td width='||chr(34)||'10%'||chr(34)||'>Used: '||trunc((NVL (a.bytes / 1024 / 1024, 0) - NVL (f.bytes / 1024 / 1024, 0)))||'MB</td>' used,
'<td width='||chr(34)||'10%'||chr(34)||'>PFree: '||lpad(round((f.bytes/a.bytes)*100,0),3)||'%</td></tr></table>' 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
'<table border='||chr(34)||'1'||chr(34)||' width='||chr(34)||'90%'||chr(34)||' align='||chr(34)||'center'||chr(34)||'<tr>'||
'<td width='||chr(34)||'40%'||chr(34)||'>Tablespace:'||d.tablespace_name||'</td>',
'<td>Status: '||decode(d.status,
'ONLINE', 'OLN',
'READ ONLY', 'R/O',
d.status)||'</td>' status,
'<td width='||chr(34)||'10%'||chr(34)||'>Ext_MGR: '||d.extent_management||'</td>',
'<td width='||chr(34)||'10%'||chr(34)||'>Total: '||trunc(NVL (a.bytes / 1024 / 1024, 0))||'MB</td>' MB,
'<td width='||chr(34)||'10%'||chr(34)||'>Free: '||trunc((NVL (a.bytes / 1024 / 1024, 0) - NVL (t.bytes / 1024 / 1024, 0)))||'MB</td>' free,
'<td width='||chr(34)||'10%'||chr(34)||'>Used: '||trunc(NVL (t.bytes / 1024 / 1024, 0))||'MB</td>' used,
'<td width='||chr(34)||'10%'||chr(34)||'>PFree: '||lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3)||'%</td></tr></table>' 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;
可以在適當的時候來嘗試使用一下,效果雖然還是醜了些,不過已經做過格式化了。
改進的方向其實還是很多。比如嵌入顏色,格式佈局等等都是需要改進的方向。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23718752/viewspace-1777927/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- JS/HTML格式化顯示JSHTML
- strings.xml顯示html格式XMLHTML
- ALV1:使用函式顯示ALV格式報表函式
- Devexpress 報表 顯示devExpress
- 專案需求討論: 文字顯示排版— Html格式HTML
- 顯示Smartforms報表程式碼ORM
- SAP CRM WebClient UI html 格式的 Text 顯示邏輯WebclientUIHTML
- BIP 報表內容下顯示報表引數值
- Html 顯示pdfHTML
- 關於oracle日期格式顯示格式Oracle
- HTML特殊字元顯示HTML字元
- 【TOOLS】PL/SQL DEVELOPER 時間格式顯示效果調整方法SQLDeveloper
- Layui表格日期格式顯示UI
- WebView 顯示HTML富文字WebViewHTML
- SAP CRM WebClient UI上以html格式顯示note的問題討論WebclientUIHTML
- django 設定日期顯示格式Django
- ssh三大框架簡單整合,struts2整合JasperReport報表、圖表,解決HTML顯示圖片不出來,PDF中文不顯示的問題框架HTML
- 顯示MS-SQL表結構,包括表和列的註釋SQL
- c# 嵌入銳浪報表C#
- oracle impdp 匯入大表報告顯示 out of rowsOracle
- PHP列印格式化顯示利器PHP
- asp.net 時間顯示格式ASP.NET
- 將SQL Server中所有表的列資訊顯示出來SQLServer
- ultraedit高亮顯示pl/sqlSQL
- 報表載入大資料時顯示進度條大資料
- ALV報表不能正確顯示資料問題
- Elements皮膚顯示HTML註釋HTML
- iOS UILabel顯示html標籤iOSUIHTML
- T-SQL 儲存過程建立 PDF 格式檔案(報表)SQL儲存過程
- pl/sql developer中關於TIMESTAMP顯示格式的疑問和學習SQLDeveloper
- IE無法顯示.shtml格式網頁HTML網頁
- 修改資料庫的日期顯示格式資料庫
- PL/SQL Developer顯示行號SQLDeveloper
- 報表的查詢皮膚怎麼顯示在左側?
- 設計好的報表是如何在 web 上顯示的Web
- SAP WM 顯示TR ITEM的標準報表LX09
- 解決jenkins下使用HTML Publisher外掛後檢視html報告顯示不正常JenkinsHTML
- HTML 滑鼠放上顯示懸浮視窗HTML