简介在Oracle数据库开发中合并多个动态游标sys_refcursor是常见需求尤其当存储过程逻辑复杂且需循环调用同一逻辑时重写逻辑或复制代码往往造成冗余。此PDF文档正是为解决该问题而整理面向有一定PL/SQL基础、希望避免重复开发的中高级开发人员。文档以序列化游标为XML为主线详细讲解使用xmltype构造函数将游标转为XML、通过getClobVal获取序列化结果、利用XPath提取/ROWSET/ROW节点以及结合DBMS_LOB.CREATETEMPORARY、WRITEAPPEND、APPEND等方法将多段行数据合并为一个CLOB再经XMLTABLE解析并封装成新的sys_refcursor。此外还包含背景分析、代码示例与执行结果可帮助读者掌握整个合并流程并直接应用到自己的存储过程改造中。资源为单个PDF文件大小约75KB篇幅紧凑、重点突出适合快速查阅和落地实践。已有525人学习/浏览是处理Oracle动态游标合并场景的实用参考。1. 为什么要合并 sys_refcursor两个存储过程之间的代码复制困境在 Oracle 数据开发里合并多个sys_refcursor这个需求十有八九是从复用存储过程逻辑这个场景里长出来的。你手头有一个写好的PROC_A业务逻辑复杂代码几百行跑得好好的过段时间要写PROC_B核心逻辑和PROC_A一样但要在循环里反复调用PROC_A还得把每次返回的动态游标攒到一起。这时候两条路摆在面前一条是把PROC_A读透在PROC_B里重写一遍代码翻倍维护翻车另一条是直接复制PROC_A的代码拼出一个更长更难看的存储过程。还有第三条路建临时表往里头插数据但每次游标返回的列结构不固定临时表建起来就是一场灾难。这三条路都是黑匣子要么费人要么费性能。真正可行的办法是借 Oracle 对 XML 的原生支持把sys_refcursor序列化成 XML再把多个游标的ROW节点合并到一个 CLOB 里最后用XMLTABLE重新解析成新的sys_refcursor返回给调用方。这篇笔记就把这套方案的原理、完整 PL/SQL 代码和我在实际开发中踩过的坑一次讲透适合被动态游标无法直接拼接卡住的中级 Oracle 开发人员。2. sys_refcursor 合并的核心思路为什么选中 XML 序列化方案2.1 sys_refcursor 和普通 CURSOR 的本性差异先搞清楚sys_refcursor和普通cursor的区别这决定了你能不能直接拼接。普通cursor是强类型的声明时就把查询语句和返回列固定死了它能用OPEN、FETCH、CLOSE操作但只能在存储过程、函数、包内部使用不能作为参数传给外部。sys_refcursor是弱类型的动态游标可以在存储过程参数里进进出出灵活性高得多但它是个无法直接OPEN、FETCH、CLOSE的黑匣子。正因为它是弱类型两个sys_refcursor之间没有内置的合并机制你不能写ref_cur1 UNION ALL ref_cur2这种 SQL。这也是为什么很多人被卡住——想拼数据但游标本身不给直接操作的入口。能把两个游标的数据拼在一起的思路是绕道而行把游标的内容先倒成一种可以被处理的数据结构。Oracle 对 XML 的良好支持就是这个绕道的桥。XMLTYPE类型可以直接接收SYS_REFCURSOR作为构造参数把游标结果集序列化成 XML 文档这就等于给动态游标装了一个取数端口。序列化之后游标就不再是黑匣子了而是一段结构化的 XML 文本可以用 XPath 提取、用DBMS_LOB拼接、用XMLTABLE再查出来。整个方案的核心思路就是动态游标 → XML 文档 → 按行节点提取 → CLOB 合并 → 再查回游标。2.2 为什么临时表方案在这里是死路有开发经验的朋友可能会想我在循环里开一个临时表每轮把游标里的数据 INSERT 进去最后再 SELECT 出来返回一个游标不也能合并吗这个思路在数据列固定的时候完全可行但问题恰恰出在列不固定上。PROC_A每次返回的sys_refcursor可能列名、列数、数据类型都不同今天返回F_USERNAME、F_USERCODE明天可能就变成F_ORDER_ID、F_AMOUNT加一个日期列。临时表只能在建表时把列结构定义死一旦列变了要么ALTER TABLE动态加列要么就得用动态 SQL 拼CREATE TABLE后者的维护成本比复制代码还可怕。XML 方案对列结构完全免疫因为 XML 是自描述的每个ROW节点内部带着自己的列名和值合并时你根本不需要关心列的数量和名字只管提取节点、拼接节点就行。这就是 XML 序列化路线在这个场景里不可替代的原因。2.3 前置知识准备DBMS_LOB 和 XMLTYPE 两个关键包动手写代码之前有两个包的能力边界需要先对齐。第一个是DBMS_LOB它负责处理 CLOB 类型的大对象。为什么需要它因为游标序列化后的 XML 是一个完整的?xml version1.0?ROWSET文档如果业务表有几十个字段、上万行数据这段 XML 的长度会轻松超过VARCHAR2的 4000 字节上限。我一般会明确建议任何游标合并的场景都不要用VARCHAR2去接序列化结果直接上DBMS_LOB.CREATETEMPORARY创建临时 CLOB再用DBMS_LOB.WRITEAPPEND和DBMS_LOB.APPEND往里写内容。第二个是XMLTYPE它有从游标构造文档的能力也有EXTRACT方法按 XPath 提取片段还有GETCLOBVAL方法把 XML 转成 CLOB。这两个包配合起来就完成了游标 → XML → CLOB → 合并的完整链路。下面是官方文档的相关地址你可以收藏备查需要掌握的包官方文档用途DBMS_LOBdocs.oracle.com/cd/E11882_01/timesten.112/e21645/d_lob.htmCLOB 创建、追加、读写XMLTYPEdocs.oracle.com/cd/B19306_01/appdev.102/b14258/t_xml.htmXML 类型构造、XPath 提取、序列化XMLTABLEdocs.oracle.com/cd/B19306_01/server.102/b14200/functions228.htmXML 反序列化为关系型结果集我在实际开发中遇到游标列数不确定、又要循环拼接的场景基本都直接走这套组合不再考虑临时表方案。3. 完整实现XML 序列化、节点提取与 CLOB 拼接的 PL/SQL 实战3.1 第一步单个游标序列化与行节点提取合并的前提是能拿到单个游标的行数据。看这段基础代码它演示了如何把游标变成 XML再只提取ROW节点DECLARE x xmltype; rowxml clob; ref_cur SYS_REFCURSOR; BEGIN -- 打开一个动态游标查询用户表 OPEN ref_cur FOR SELECT F_USERNAME, F_USERCODE, F_USERID FROM Tb_System_User WHERE F_USERID 1; -- 关键步骤把游标直接作为 XMLTYPE 构造参数 x : xmltype(ref_cur); -- 打印完整 XML 结构观察序列化后的格式 DBMS_OUTPUT.PUT_LINE(完整的REFCURSOR结构); DBMS_OUTPUT.PUT_LINE(x.getClobVal()); -- 只提取 ROW 节点部分用于后续合并 rowxml : x.extract(/ROWSET/ROW).getClobVal(0, 0); DBMS_OUTPUT.PUT_LINE(只提取行信息); DBMS_OUTPUT.PUT_LINE(rowxml); END; /这段代码的输出结果是这样的完整的REFCURSOR结构 ?xml version1.0? ROWSET ROW F_USERNAME系统管理员/F_USERNAME F_USERCODEadmin/F_USERCODE F_USERID1/F_USERID /ROW /ROWSET 只提取行信息 ROW F_USERNAME系统管理员/F_USERNAME F_USERCODEadmin/F_USERCODE F_USERID1/F_USERID /ROW这里需要注意三个要点。第一xmltype(ref_cur)的构造函数是 Oracle 内置支持的不需要额外安装任何组件它会把游标当前的结果集完整序列化为 XML。第二x.extract(/ROWSET/ROW)用的是标准 XPath 语法从文档根节点开始找ROWSET下的所有ROW节点返回的是一个XMLTYPE片段。第三getClobVal(0, 0)的两个参数是offset和length传0, 0表示返回整个 CLOB 内容我当时第一次用的时候传了别的值结果只拿到了一部分 XML排查了半天。提取行节点而不是提取整个文档是为了最后拼回ROWSET时有控制权——你希望合并后的文档只有一个根节点而不是一堆独立文档堆在一起。3.2 第二步对每个游标执行提取 追加合并操作现在进入正式合并流程。假设业务上有两个游标分别查不同用户你需要把它们的结果合并成一个 XML 文档最终能通过一个游标返回。这是完整的可执行代码DECLARE x xmltype; rowxml clob; mergeXml clob; ref_cur SYS_REFCURSOR; ref_cur2 SYS_REFCURSOR; ref_cur3 SYS_REFCURSOR; BEGIN -- 创建临时 CLOB用于存放合并后的 XML 文档 DBMS_LOB.CREATETEMPORARY(mergeXml, TRUE); -- 写入根节点开标签 DBMS_LOB.WRITEAPPEND(mergeXml, 8, ROWSET); -- ---------- 第一个游标 ---------- OPEN ref_cur FOR SELECT F_USERNAME, F_USERCODE, F_USERID FROM Tb_System_User WHERE F_USERID 1; x : xmltype(ref_cur); DBMS_OUTPUT.PUT_LINE(完整的REFCURSOR结构); DBMS_OUTPUT.PUT_LINE(x.getClobVal()); rowxml : x.extract(/ROWSET/ROW).getClobVal(0, 0); DBMS_OUTPUT.PUT_LINE(只提取行信息); DBMS_OUTPUT.PUT_LINE(rowxml); -- 把行节点追加到合并 CLOB 中 DBMS_LOB.APPEND(mergeXml, rowxml); -- ---------- 第二个游标 ---------- OPEN ref_cur2 FOR SELECT F_USERNAME, F_USERCODE, F_USERID FROM Tb_System_User WHERE F_USERID 1000; x : xmltype(ref_cur2); rowxml : x.extract(/ROWSET/ROW).getClobVal(0, 0); DBMS_LOB.APPEND(mergeXml, rowxml); -- 写入根节点结束标签形成完整 XML 文档 DBMS_LOB.WRITEAPPEND(mergeXml, 9, /ROWSET); -- 观察合并结果 DBMS_OUTPUT.PUT_LINE(合并后的信息); DBMS_OUTPUT.PUT_LINE(mergeXml); -- 关闭游标释放资源 CLOSE ref_cur; CLOSE ref_cur2; END; /这段代码的核心逻辑分四层。第一层DBMS_LOB.CREATETEMPORARY(mergeXml, TRUE)在临时表空间创建一个 CLOB第二个参数TRUE表示这个临时 LOB 会在会话结束时自动释放不需要手动FREE如果你传FALSE就必须要自己调用DBMS_LOB.FREETEMPORARY否则会话内存会一直占着。第二层DBMS_LOB.WRITEAPPEND(mergeXml, 8, ROWSET)的第一个参数是目标 CLOB第二个参数8是写入字符串的字节数第三个参数是内容——这里的 8 是ROWSET的字符个数结尾的/ROWSET是 9 个字符这两个数字千万不能写错写多了会带入多余的字符写少了会把字符串截断。第三层DBMS_LOB.APPEND(mergeXml, rowxml)是纯粹的 CLOB 追加把每个游标提取出来的ROW节点依次接到根节点内部。第四层循环处理多个游标时只需要把OPEN ref_cur FOR和xmltype(ref_cur)这两行放到LOOP结构里每个游标都执行同样的提取行节点 → 追加到 mergeXml操作最后再统一写闭合标签。执行后的输出验证了方案的可行性合并后的信息 ROWSET ROW F_USERNAME系统管理员/F_USERNAME F_USERCODEadmin/F_USERCODE F_USERID1/F_USERID /ROW ROW F_USERNAME黄燕/F_USERNAME F_USERCODEHUANGYAN/F_USERCODE F_USERID1000/F_USERID /ROW /ROWSET到这一步两个游标的数据已经在物理上拼到一起了而且列结构完全不同也能兼容——XML 不在乎你的列对不对得上。但注意现在mergeXml还只是一个 CLOB调用方需要的是游标所以还差最后一步。3.3 第三步用 XMLTABLE 把合并后的 CLOB 解析回游标合并完 XML 只是中场休息真正的考验是把这段 XML 重新变成调用方能用的sys_refcursor。Oracle 的XMLTABLE函数就是干这个的它能把 XML 文档按 XPath 拆成关系型行集。接续上面代码DECLARE mergeXml clob; ref_cur3 SYS_REFCURSOR; BEGIN -- 假设 mergeXml 已经包含合并后的 XML 文档 -- 通过 XMLTABLE 把 ROW 节点解析为虚拟表再返回为游标 OPEN ref_cur3 FOR SELECT * FROM xmltable( /ROWSET/ROW PASSING xmltype(mergeXml) COLUMNS F_USERNAME varchar2(100) PATH F_USERNAME, F_USERCODE varchar2(100) PATH F_USERCODE ); -- 此时 ref_cur3 就可以作为存储过程的返回参数传给调用方了 END; /XMLTABLE的语法有三个动词需要理解。第一个是第一个参数/ROWSET/ROW这是 XPath告诉 OracleROW节点藏在文档的哪个位置从根找就是/ROWSET/ROW如果你想处理嵌套结构比如找每行里的子表数据那就要写相对路径比如/ROWSET/ROW/ITEMS/ITEM。第二个是PASSING xmltype(mergeXml)它把 CLOB 内容实例化为一个XMLTYPE对象这里要注意如果mergeXml不是合法的 XML 文档这一步会直接报ORA-31011: XML parsing failed。第三个是COLUMNS子句它定义返回结果集的列名、类型和数据来源路径PATH F_USERNAME表示取当前ROW节点下F_USERNAME子节点的文本值如果不写PATHOracle 默认用列名作为节点名去匹配。一个容易忽视的地方XMLTABLE返回的是虚表OPEN ref_cur3 FOR SELECT * FROM xmltable(...)是把它包成一个动态游标的标准写法。这样一来ref_cur3就完全等价于一个把多个游标内容 UNION ALL 之后的结果集调用方拿到的就是一个干净的游标完全不知道底层经历了 XML 序列化和反向解析。我在真实项目中就是把这个三段式逻辑封装成一个函数输入是一组游标输出是合并后的新游标业务代码里一行调用就搞定。3.4 参数与选型对照关键点的取舍写这种代码时参数选型直接决定调试体验。我把几个关键决策点整理成表方便你对照自己的场景决策点推荐做法不推荐做法理由存储中间 XML 的变量类型CLOBDBMS_LOB操作VARCHAR2直接拼接XML 长度易超 4000VARCHAR2 会报ORA-06502提取行节点的方式.extract(/ROWSET/ROW)后取getClobVal(0,0)直接getClobVal()全量再截取全量拿的是完整文档拼到目标 CLOB 里会重复根标签合并多个游标的循环写法FOR循环逐个OPENextractAPPEND手写 N 个游标的重复代码游标数量变化时循环只改数组定义XMLTABLE的列传参按业务需要的列显式声明SELECT *不声明列游标是动态的不声明列多取数方反而不知道列名临时 CLOB 生命周期会话结束自动释放TRUE手动FREETEMPORARY释放放在事务里手动释放容易漏掉导致临时段膨胀这套参数的取舍逻辑本质是把动态问题转化成静态问题。你用XMLTABLE显式声明列看起来是写死了结构但每个游标传进来的时候列名和值都可以不同解析时只要节点名对得上就行这比临时表 DDL 的写死列结构且无法变更要灵活得多。4. 避坑指南游标合并中最容易翻车的五个细节4.1 现象ORA-06502: PL/SQL: numeric or value error上百行 XML 拼接时报错现象两个游标各自只有几十行数据你以为 Varchar2 够用直接用VARCHAR2变量接收getClobVal()的结果结果一执行就报ORA-06502提示字符缓冲区太小。原因序列化后的 XML 有文档头、根标签、所有行节点列多行多时长度轻松破万Varchar2 上限是 4000 字节而且DBMS_OUTPUT.PUT_LINE对超长 CLOB 也有显示截断。解决所有中间变量统一用CLOB拼接走DBMS_LOB.APPEND。我现在的习惯是只要代码里出现xmltype(...).getClobVal()立刻建一个CLOB变量去接绝不用 Varchar2。DBMS_OUTPUT打日志时如果需要看完整内容用DBMS_LOB.SUBSTR(mergeXml, 2000, 1)分段打印。4.2 现象合并后的 XML 文档出现两个ROWSET根标签现象把每个游标的x.getClobVal()直接拼到目标 CLOB 里最后得到的内容是ROWSET.../ROWSETROWSET.../ROWSET用XMLTABLE解析时报ORA-00932或ORA-31011。原因getClobVal()返回的是完整 XML 文档包含ROWSET开闭标签拼接到目标 CLOB 时等于把多个完整文档拼在了一起形成了多根节点文档这不符合 XML 规范。解决只用.extract(/ROWSET/ROW)提取行节点绝不用完整文档参与拼接。我自己在开发中会把提取行节点封装成一个私有函数保证所有游标合并走同一道工序。4.3 现象DBMS_LOB.WRITEAPPEND后字符串多出来几个字符现象写入ROWSET后打印合并结果发现变成了ROWSETXYZ后面跟着莫名奇妙的字符。原因WRITEAPPEND的第二个参数是字节数不是字符数。ROWSET是 8 个字符但你如果写成9它会顺带把/ROWSET的前几个字符也写入或者把空字符填进去中文字符一个占 3 字节UTF-8如果第三个参数里混了中文字节数更难算。解决不要手动数长度用LENGTH()函数动态计算DBMS_LOB.WRITEAPPEND(mergeXml, LENGTH(ROWSET), ROWSET)。这是我踩过最冤枉的坑数错一次整个 XML 结构就变形了。4.4 现象XMLTABLE解析时报ORA-19279: XPTY0004 - invalid token现象合并的 XML 里有列名相同但值不同甚至列值包含特殊字符比如、、解析时报类型错误或 token 错误。原因原始数据里的特殊字符没有进行 XML 转义。游标序列化为 XML 时Oracle 会自动把数据里的转成lt;但如果你手动拼 CLOB可能破坏了这种转义关系导致 XML 结构不合法。解决不要手动拼接 CLOB 里的数据节点尽量保持xmltype(游标)→extract→APPEND的完整链路让 Oracle 来处理转义。如果你确实需要手动构造 XML 片段记得用XMLTYPE的构造函数来包装。4.5 现象循环合并大量游标时临时表空间膨胀现象在循环里合并几百个游标会话的临时表空间占用快速上涨数据库告警日志出现临时表空间不足。原因每个xmltype(ref_cur)都会在内存中构造完整 XMLCLOB临时段也会随着APPEND不断增长循环结束后没有及时释放游标和临时 LOB导致临时段回收不及时。解决每个游标用完后立即CLOSE每个 CLOB 变量在不再使用时调用DBMS_LOB.FREETEMPORARY如果游标数量极大考虑分批合并每合并 50 个就落一次临时表避免单个 CLOB 无限膨胀。这个坑在数据量小的时候完全看不出来一旦上了生产环境就会被临时表空间告警打醒。5. 进阶技巧把合并逻辑封装成通用工具函数一次定义到处复用如果你只需要合并两三个游标写上面的匿名块就够了。但真实业务里合并游标的逻辑会被多个存储过程反复调用每次都复制那一大段DBMS_LOB代码不现实。我通常的做法是把它封装成一个独立的存储过程或函数输入一个SYS_REFCURSOR集合可以用TABLE OF SYS_REFCURSOR作为集合类型输出一个合并后的SYS_REFCURSOR。核心逻辑是循环遍历游标数组对每个游标执行提取行节点并追加到 CLOB最后统一用XMLTABLE返回。在封装时有两点是需要特别留意的。第一游标数组参数要定义成TABLE OF SYS_REFCURSOR但存储过程的参数类型不能直接使用这个集合类型必须先创建独立的 TYPE 或者使用包内定义的类型。第二合并后的列结构取决于最后XMLTABLE中COLUMNS声明的列因为游标是动态的设计函数时就要约定好业务上游标必须包含哪些公共列比如F_USERNAME、F_USERCODE这样下游取数才稳定。以每次返回的游标列可能都不相同这个前提来看函数设计上通常要约定一个最小公共列集合比如F_USERNAME、F_USERCODE、F_USERID取数方只消费这几个字段。验证合并结果是否正确我一直用这个方法写一个测试脚本先分别打印每个游标的getClobVal()再打印合并后的mergeXml最后FETCH合并游标的前十行检查行数是否等于两个源游标行数之和列值是否对得上。这个验证能同时发现行节点漏提取和列映射错位两类问题。我一般还会特意用一个列名带下划线的表做测试因为F_USERNAME节点名和列名一致时XMLTABLE的PATH可以省略但一旦列名是USERNAME和节点名对不上省略就会拿不到数据。从那以后我每次写XMLTABLE的都强制写全PATH子句不省这个事再小的列映射也显式声明因为动态游标场景下默认按列名匹配这个隐含约定是最容易翻车的地方。这套方案的核心价值在于给动态游标无法拼接提供了一个不依赖表结构的通用解你在自己的项目里把源游标替换成实际业务查询再把COLUMNS改成业务需要的列就能直接跑通。希望帮到你。本文还有配套的精品资源点击获取