一般大家做报表的时候,会选择使用一些BI工具或者EXCEL表格来进行展现,因为需要让表格带点“颜色”,不像csv那样单调。
但是目前这些工具都不支持任意SQL直接生成带格式的表格,一般都要先做个模板,因为SQL的列数不确定。
并且这些工具最终生成的是一个文件,或者一个在线页面,如果是系统自动推送给用户,文件会占用用户设备的存储,在线页面则需要进行跳转或者二次登陆。
那么我们可不可以在存储过程里直接生成带简单颜色的报表,然后通过EMAIL或者企业微信发送出去呢?
我们来分析一下,
1.EMAIL展示的其实是纯文本信息或者静态网页信息,企业微信的图文信息也是个静态的网页网页
2.静态网页是支持显示表格的(参考 https://www.w3school.com.cn/html/html_tables.asp)
3.HTML的表格有table/th/tr/td四个标签,table表示这是一个表格,一组td内为一个单元格,一组tr内为一行,一组th表示字段名,结构大致如下
<html><body>
<table border="1">
<tr>
<th>Heading</th>
<th>Another Heading</th>
</tr>
<tr>
<td>row 1, cell 1</td>
<td>row 1, cell 2</td>
</tr>
<tr>
<td>row 2, cell 1</td>
<td>row 2, cell 2</td>
</tr>
</table>
</html></body>
显示效果如下
Heading | Another Heading |
---|---|
row 1, cell 1 | row 1, cell 2 |
row 2, cell 1 | row 2, cell 2 |
我们对这个html进行分析,假设我们已经有数据了,要用程序来生成这段,逻辑是怎样的
首先,先写个<table border="1">,
然后<tr>,
然后写个循环,把字段名生成
<th>Heading</th>
<th>Another Heading</th>
然后</tr>回来,字段名这一行就生成完了
然后按数据行循环,每行数据生成一组
<tr>
<td>row 1, cell 1</td>
<td>row 1, cell 2</td>
</tr>
中间每个字段也要循环一个个生成,
最后数据行生成完了,补个表格结束 </table>
可以看到是相当简单的几个步骤,但是对于不是那么熟悉ORACLE的人来说,如何获取一个SQL的各个字段名并存成游标来进行循环操作,不是那么简单的事,而且循环一般是一行一行循环,而这个还涉及到了按列来循环。
好在oracle是支持这个功能的,而且还不止一种方法,比如用dbms_sql包取每个字段再组成一个数组作为游标。但这次我们换一种更简单的方式
declare
l_sql varchar2(4000);
cur_ SYS_REFCURSOR;
L_TEXT varchar2(32000);
CURSOR get_columns IS
SELECT t2.column_value.getrootelement() name,
EXTRACTVALUE(t2.column_value, 'node()') VALUE
FROM (SELECT * FROM TABLE(XMLSEQUENCE(cur_))) t1,
TABLE(XMLSEQUENCE(EXTRACT(t1.column_value, '/ROW/node()'))) t2;
begin
OPEN cur_ FOR 'select * from (' || l_sql || ') where rownum=1';
FOR rec_ IN get_columns LOOP
L_TEXT := L_TEXT || '<th>' || rec_.name || '</th>';
END LOOP;
end;
然后后面的数据行也可以按照上面这个方式来循环。
接着我们要处理另一个问题,这样的表格虽然是可以在邮件中直接显示了,但是没有风格颜色,不太美观,所以我们要想办法加点颜色上去。
早期用ORACLE数据库的人可能知道,用sqlplus的spool可以轻松导出数据,但大多数时候都是生成的逗号分隔符的纯文本数据,有心的仔细阅读一下sqlplus的说明,可以发现,sqlplus也支持生成html网页文件,那么我们生成一个然后打开看看里面是怎么写的
<!DOCTYPE HTML><html><head>
<body><style type="text/css">
body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;}
p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;}
table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;}
h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;}
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;}
a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
</style>
<table>
.....
</table>
</body>
</html>
原来只是在前面加了一段css样式,所以我们在生成的时候,直接把这整段都拼到前面去就好了,或者也可以换其他的样式,比如
<style type="text/css">
body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;}
p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;}
table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#ffffff; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
th {font:bold 10pt Arial,Helvetica,sans-serif; color:White; background:#4a91f9; padding:0px 0px 0px 0px;}
h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;}
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;}
a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
</style>
那么基本思路都理清了,的确是可以实现的,然后我参考以上逻辑写了一个函数,调用时只需要传入sql查询语句即可返回对应的html代码字符串,比如
l_html:=SQL_TO_HTML('select name ,id from table');
完整源码请从我的github获取
https://github.com/Dark-Athena/sql_to_html-oracle
实际使用过程中,发现另一个问题,由于我们设定这是一个通用函数,能传入任意的查询sql,而sql的查询结果,是可能有某些字段的某些值为空值的,使用上面 "EXTRACTVALUE(t2.column_value, 'node()')"的这个方式查询不到空值的列,就会导致生成的数据错位,所以我们暂时只能保证传入的SQL的每个值不存在空值,建议提前用nvl处理好。
之后我们也可以沿用这个思路,使用其他的取值方式,来避免这个问题
最后,由于这个函数遍历了sql查询结果中的每一个值,速度会比较慢,因此不建议将数据结果太大的sql传入来生成。