【AIO】使用ORACLE数据库存储过程把任意SQL生成HTML网页表格

一般大家做报表的时候，会选择使用一些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>

显示效果如下

HeadingAnother Heading
row 1, cell 1row 1, cell 2
row 2, cell 1row 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传入来生成。

20211028 更新

【ORACLE】在数据库中使用xml和xlst来实现sql查询转换成HTML表格

posted on 2021-10-03 20:29  DarkAthena  阅读(283)  评论(0)  编辑  收藏  举报

导航