前言
之前有篇文章介绍了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上的代码已更新