【ORACLE】在数据库中使用xml和xslt来实现sql查询转换成HTML表格-DA-技术分享

前言

之前有篇文章介绍了sql转html的一个方案，
【AIO】使用ORACLE数据库存储过程把任意SQL生成HTML网页表格
此方案存在缺点，即对空值无法处理，后来基于oracle18c添加的多态表函数PTF，解决了此问题(https://github.com/Dark-Athena/sql_to_html-oracle/blob/main/SQL_TO_HTML_PTF.pkg)。
但是个人感觉代码还是太过复杂，今天在打算优化pljson包的时候，想到它的sql转json是使用的xslt来对xml进行的转换，而且代码很简单，除了模板外，主体代码不到十行，所以就想自己试下。

分析

要准备一个xslt模板，是用来将xml转换成html的，必须要支持任意列，所以模板中不能指定列，网上找了下，这个可能能用上
https://stackoverflow.com/questions/5657549/xml-to-html-table-with-xslt

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/">
        <table>
            <xsl:apply-templates select="rows/row[1]" />
        </table>
    </xsl:template>
    <xsl:template match="row">
        <tr>
            <xsl:apply-templates mode="th" />
        </tr>
        <xsl:apply-templates select="../row" mode="td" />
    </xsl:template>
    <xsl:template match="row/*" mode="th">
        <th>
            <xsl:value-of select="local-name()" />
        </th>
    </xsl:template>
    <xsl:template match="row" mode="td">
        <tr>
            <xsl:apply-templates />
        </tr>
    </xsl:template>
    <xsl:template match="row/*">
        <td>
            <xsl:apply-templates />
        </td>
    </xsl:template>
</xsl:stylesheet>

使用dbms_xmlgen.newcontext将查询sql转换成xml文档
查看oracle中转换出来的xml文档格式，发现格式规律

<ROWSET>
 <ROW>
  <EMPLOYEE_ID>102</EMPLOYEE_ID>
  <START_DATE>13-1月 -01</START_DATE>
  <END_DATE>24-7月 -06</END_DATE>
  <JOB_ID>IT_PROG</JOB_ID>
  <DEPARTMENT_ID>60</DEPARTMENT_ID>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>101</EMPLOYEE_ID>
  <START_DATE>21-9月 -97</START_DATE>
  <END_DATE>27-10月-01</END_DATE>
  <JOB_ID>AC_ACCOUNT</JOB_ID>
  <DEPARTMENT_ID>110</DEPARTMENT_ID>
 </ROW>
</ROWSET>

匹配搜索到的模板，将模板中对应关键字进行修改，比如模板中的"rows"替换成"ROWSET","row"替换成"ROW"
主体代码

  l_ctx := dbms_xmlgen.newcontext(p_sql); --根据sql生成一个xml文档
  dbms_xmlgen.setnullhandling(l_ctx, dbms_xmlgen.empty_tag);--标记空值的处理方式
  l_xml := dbms_xmlgen.getxmltype(l_ctx, dbms_xmlgen.none);--获取xml内容
  l_num_rows := dbms_xmlgen.getnumrowsprocessed(l_ctx);--获取记录数
  dbms_xmlgen.closecontext(l_ctx);--关闭xml文档
  if l_num_rows > 0 then --记录数为0时转换会报错
    l_html := l_xml.transform(xmltype(l_xml_to_html_stylesheet));--根据模板转换成html格式
    return l_html.getclobval();--获取文本内容并返回
  end if;

这代码还能继续压缩...

成品

效果：

代码见我的github

https://github.com/Dark-Athena/sql_to_html-oracle/blob/main/sql_to_html_xlst.fnc

另外，我还加入了sqlplus导出html的css样式，如果想用其他样式，可以自行替换

最后

虽然转换速度很快，空值也不会错位，但是空值的表格边框没了，之后看模板还能不能再改下。

2021-10-28 21：51 更新

空值边框的问题，直接用字符串替换大法解决了，github上的代码已更新