【ORACLE】任意sql查询结果利用xslt模板转换成csv文件-DA-技术分享

背景

其实这是我在修改ORACLE官方包dbms_cloud时想出来的,
【ORACLE】骚操作，个人修改oracle官方dbms_cloud包来支持本地数据库导出数据到云存储

在DBMS_CLOUD.EXPORT_DATA中,oracle自治数据库先将一个sql的多个字段进行拼接,变成一个字段,然后再把此游标发到C函数里输出成一个文本内容。由于本地数据库不包含这个C函数,于是我需要解决如何在数据库中将多行数据放在一个变量中。

目前网上大多数的方案都是逐个字段拼接,然后逐行写入文件,如果数据量大,数据库需要遍历每一个值进行操作,就像小鸡啄米一样让人着急。

于是我想到了上次我使用xslt转换一个sql到html中的方案,
【ORACLE】在数据库中使用xml和xlst来实现sql查询转换成HTML表格
想着是不是也可以用这种方式生成csv,于是开搞。

分析及修改

我先把上次转html的xslt模板拿出来

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/">
    <table border="1">
    <xsl:apply-templates select="ROWSET/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>

把<table>和</table>去掉
在 </td> 和 </th> 后面加上逗号
在td前面加上回车和换行
去掉所有的td/tr/th标签

得到一个这样子的

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
  <xsl:apply-templates select="ROWSET/ROW[1]" />
  </xsl:template>
  <xsl:template match="ROW">
  <xsl:apply-templates mode="th" />
  <xsl:apply-templates select="../ROW" mode="td" />
  </xsl:template>
  <xsl:template match="ROW/*" mode="th">
  <xsl:value-of select="local-name()" />
  <xsl:text>:1</xsl:text>
  </xsl:template>
  <xsl:template match="ROW" mode="td">
  <xsl:text>&#xd;</xsl:text><xsl:text>&#xa;</xsl:text>
  <xsl:apply-templates />
  </xsl:template>
  <xsl:template match="ROW/*">
  <xsl:apply-templates />
  <xsl:text>:1</xsl:text>
  </xsl:template>
  </xsl:stylesheet>

但是,每行后面多了一个逗号,于是搜索了一下"xslt 逗号",想看看相关的一些例子,

https://www.javaroad.cn/questions/21377

真幸运,第一个就是我想要的。

我先尝试了这个问答中说的 separator,但是由于这是xml2.0的,oracle不识别这个属性,然后选择了下面这个方案

<xsl:if test="position() != last()">
      <xsl:text>,</xsl:text>
   </xsl:if>

然后我又想到可能会有自定义分隔符的需求,就加了个自定义分隔符的可选参数。

所以模板代码修改为

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
  <xsl:apply-templates select="ROWSET/ROW[1]" />
  </xsl:template>
  <xsl:template match="ROW">
  <xsl:apply-templates mode="th" />
  <xsl:apply-templates select="../ROW" mode="td" />
  </xsl:template>
  <xsl:template match="ROW/*" mode="th">
  <xsl:value-of select="local-name()" />
  <xsl:if test="position() != last()">
  <xsl:text>:1</xsl:text>
  </xsl:if>
  </xsl:template>
  <xsl:template match="ROW" mode="td">
  <xsl:text>&#xd;</xsl:text><xsl:text>&#xa;</xsl:text><xsl:apply-templates />
  </xsl:template>
  <xsl:template match="ROW/*">
  <xsl:apply-templates />
  <xsl:if test="position() != last()">
  <xsl:text>:1</xsl:text>
  </xsl:if>
  </xsl:template>
  </xsl:stylesheet>

最后把通过此模板生成的clob文本信息,通过dbms_lob.clob2file保存到操作系统即可。

成品

--example 1:
begin
  sql_to_csv_xslt('select a,b,c from tab',
                  'demo.csv',
                  'DATA_PUMP_DIR');
END;
/
--example 2:
begin
  sql_to_csv_xslt('select a,b,c from tab',
                  'demo.csv',
                  'DATA_PUMP_DIR',
                  '|');
END;
/

完整代码见我的github
https://github.com/Dark-Athena/sql_to_csv_xslt-oracle