编写Oracle PL/SQL存储过程：返回存储XML数据的CLOB输出参数

第一步：修正XML标签错误 #

原XML存在标签不匹配问题（例如<TRANSACTION_NUMBER>的闭合标签错误写成</SOURCE_TRANSACTION_NUMBER>），以下是修正后的有效XML：

<OutputParameters xmlns="http://xmlns.oracle.com/cloud/adapter/" xmlns:xsi="http://www.test.org/1998/XMLSchema-instance">
  <Header>
   <Header_item>
      <TRANSACTION_NUMBER>YSCPQ_9876</TRANSACTION_NUMBER>
      <TRANSACTION_SYSTEM>OPS</TRANSACTION_SYSTEM>
   <Lines>
    <lines_item>
      <TRANSACTION_ID>YSCPQ_9876</TRANSACTION_ID>
      <TRANSACTION_LINEID>1</TRANSACTION_LINEID>
    </lines_item>
    <lines_item>
      <TRANSACTION_ID>YSCPQ_9876</TRANSACTION_ID>
      <TRANSACTION_LINEID>2</TRANSACTION_LINEID>
    </lines_item>
   </Lines>
  </Header_item>
 </Header>     
</OutputParameters>

第二步：创建存储过程 #

以下是实现需求的Oracle存储过程，它会将修正后的XML内容存入CLOB变量并作为OUT参数返回：

CREATE OR REPLACE PROCEDURE get_output_xml(p_out_xml OUT CLOB)
IS
BEGIN
  -- 初始化临时CLOB
  DBMS_LOB.CREATETEMPORARY(p_out_xml, TRUE);
  
  -- 逐段写入XML内容，单引号需转义为两个单引号，换行用CHR(10)控制格式
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('<OutputParameters xmlns="http://xmlns.oracle.com/cloud/adapter/" xmlns:xsi="http://www.test.org/1998/XMLSchema-instance">' || CHR(10)),
                       '<OutputParameters xmlns="http://xmlns.oracle.com/cloud/adapter/" xmlns:xsi="http://www.test.org/1998/XMLSchema-instance">' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('  <Header>' || CHR(10)), '  <Header>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('   <Header_item>' || CHR(10)), '   <Header_item>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('      <TRANSACTION_NUMBER>YSCPQ_9876</TRANSACTION_NUMBER>' || CHR(10)),
                       '      <TRANSACTION_NUMBER>YSCPQ_9876</TRANSACTION_NUMBER>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('      <TRANSACTION_SYSTEM>OPS</TRANSACTION_SYSTEM>' || CHR(10)),
                       '      <TRANSACTION_SYSTEM>OPS</TRANSACTION_SYSTEM>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('   <Lines>' || CHR(10)), '   <Lines>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('    <lines_item>' || CHR(10)), '    <lines_item>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('      <TRANSACTION_ID>YSCPQ_9876</TRANSACTION_ID>' || CHR(10)),
                       '      <TRANSACTION_ID>YSCPQ_9876</TRANSACTION_ID>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('      <TRANSACTION_LINEID>1</TRANSACTION_LINEID>' || CHR(10)),
                       '      <TRANSACTION_LINEID>1</TRANSACTION_LINEID>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('    </lines_item>' || CHR(10)), '    </lines_item>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('    <lines_item>' || CHR(10)), '    <lines_item>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('      <TRANSACTION_ID>YSCPQ_9876</TRANSACTION_ID>' || CHR(10)),
                       '      <TRANSACTION_ID>YSCPQ_9876</TRANSACTION_ID>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('      <TRANSACTION_LINEID>2</TRANSACTION_LINEID>' || CHR(10)),
                       '      <TRANSACTION_LINEID>2</TRANSACTION_LINEID>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('    </lines_item>' || CHR(10)), '    </lines_item>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('   </Lines>' || CHR(10)), '   </Lines>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('  </Header_item>' || CHR(10)), '  </Header_item>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH(' </Header>' || CHR(10)), ' </Header>' || CHR(10));
  DBMS_LOB.WRITEAPPEND(p_out_xml, LENGTH('</OutputParameters>'), '</OutputParameters>');
  
EXCEPTION
  WHEN OTHERS THEN
    -- 异常时释放临时CLOB资源，避免内存泄漏
    IF DBMS_LOB.ISTEMPORARY(p_out_xml) = 1 THEN
      DBMS_LOB.FREETEMPORARY(p_out_xml);
    END IF;
    RAISE;
END get_output_xml;
/

调用示例 #

可以通过以下PL/SQL块调用存储过程，获取返回的CLOB格式XML：

DECLARE
  l_xml CLOB;
BEGIN
  get_output_xml(l_xml);
  -- 打印XML内容（按需使用）
  DBMS_OUTPUT.PUT_LINE(l_xml);
  -- 使用完成后释放临时CLOB
  IF DBMS_LOB.ISTEMPORARY(l_xml) = 1 THEN
    DBMS_LOB.FREETEMPORARY(l_xml);
  END IF;
END;
/

关键说明 #

• 使用DBMS_LOB.CREATETEMPORARY创建临时CLOB，支持处理较大体积的XML内容
• PL/SQL中字符串的单引号必须转义为两个单引号，避免语法错误
• 异常处理逻辑确保出错时释放临时CLOB资源，防止内存泄漏
• 已修正原XML的标签不匹配问题，保证XML结构有效

内容的提问来源于stack exchange，提问作者Anji007