简介针对Oracle数据库开发中需要合并多个sys_refcursor动态游标的典型场景这份PDF技术笔记提供了基于XML序列化与解析的完整解决方案。内容从存储过程PROC_A复用的实际需求切入指出重写逻辑、复制代码、建立临时表等方法在面对动态列或不固定结果集时的明显局限进而讲解如何利用XMLTYPE(refcursor)直接构造XML、通过getClobVal()获取完整CLOB、使用DBMS_LOB包的CREATE_TEMPORARY、WRITEAPPEND、APPEND等方法拼接多个游标中的ROW片段再以XMLTABLE解析合并后的文档并返回新的sys_refcursor。文中包含可直接参考的PL/SQL示例并覆盖DBMS_OUTPUT调试、XPath表达式/ROWSET/ROW提取节点等关键细节可帮助Oracle开发人员快速掌握游标合并与XML互操作技巧减少重复代码并降低复杂存储过程的维护成本。资源为1个PDF文档压缩包约75KB内容浓缩精炼适合作为日常开发的速查手册目前已有525人学习下载适合具备一定PL/SQL基础、希望提升存储过程开发效率的工程师阅读参考也可用于深入理解Oracle XML特性。1. 合并多个 sys_refcursor先搞清楚要合的是什么在 Oracle 存储过程里sys_refcursor是返回结果集最常用的方式接手过一个多模块汇总报表后你会发现真正的痛点不是“怎么打开一个游标”而是“怎么把好几个游标的结果合并成一个交出去”。调用方往往是 Java 的ResultSet或报表工具它们只认一个结果集你不可能在存储过程里把多个游标直接“加”起来——PL/SQL 没有这种操作符。合并的本质是把多个 REF CURSOR 指向的查询结果统一成一个结果集输出。实现上常见的有三条路一是改写 SQL用UNION ALL合并所有查询后一次性OPEN二是用管道表函数逐行输出三是先取出游标数据再做内存组装。绝大多数业务场景第一条路最干净后两条是兜底方案。这篇就沿着这三条路径展开把类型转换、排序、空值处理和性能这些坑一个个说清楚适合正在写报表存储过程或批量数据导出的开发人员。2. 用 OPEN FOR UNION ALL 合并最小可行方案2.1 为什么先考虑 UNION ALL 而不是逐行 FETCH合并多个sys_refcursor最简单的思路不是“合并游标”而是“合并产生游标的 SQL”。你手上有三个游标变量每个都来自一段OPEN cursor_var FOR SELECT ...如果这些查询的结构一致把它们的 SQL 文本用UNION ALL拼起来再一次性OPEN到一个新游标里调用方拿到的就是一个完整结果集。这么做的好处是结果集在数据库内部完成合并不用把数据拉到 PL/SQL 引擎再逐行搬性能损耗最小。而且UNION ALL不会去重适合报表里“多批数据直接追加”的语义——比如一月的销售数据和二月的销售数据合在一起本来就允许重复订单号用UNION反而会把重复行吞掉造成数据缺失。DECLARE c_merged SYS_REFCURSOR; BEGIN OPEN c_merged FOR SELECT dept_id, emp_name, salary FROM emp_01 UNION ALL SELECT dept_id, emp_name, salary FROM emp_02 UNION ALL SELECT dept_id, emp_name, salary FROM emp_03; END; /这段代码没有做任何内存操作只是把三个查询合并成一个 SQL 文本交给OPEN FOR。逻辑上要求每个SELECT的列数、列顺序、数据类型完全一致否则 ORA-01789 或 ORA-01790 会直接抛出来。参数方面没有额外设置合并后游标的列名由第一个SELECT决定这点在调用方按列名取数时要特别注意。2.2 结构一致的游标变量如何动态拼出合并 SQL实际开发里游标往往不是直接写死在代码里的而是从别的函数或过程中返回。比如你从三个子过程里分别拿到c1、c2、c3这三个游标的列结构一样但你没法把游标变量本身拼到 SQL 里。常见做法是让每个子过程不仅返回游标还返回一段对应的 SQL 文本然后统一拼接。PROCEDURE merge_cursors( p_sql_1 VARCHAR2, p_sql_2 VARCHAR2, p_sql_3 VARCHAR2, p_merged OUT SYS_REFCURSOR ) IS v_sql VARCHAR2(32767); BEGIN v_sql : SELECT * FROM ( || p_sql_1 || ) UNION ALL || SELECT * FROM ( || p_sql_2 || ) UNION ALL || SELECT * FROM ( || p_sql_3 || ); OPEN p_merged FOR v_sql; END; /这里的核心是把外部传入的 SQL 文本包一层子查询再用UNION ALL连接每段 SQL 里即使带了ORDER BY子查询包一层也能避免 ORA-00933。p_sql_1这类参数通常是动态拼出来的比如根据月份拼表名。参数上唯一要严格控制的是VARCHAR2的长度——拼接三四个长 SQL 很容易超过 32767 字节超了会报 ORA-00972 或 ORA-06502我一般会把 SQL 文本拆成多个变量再拼不要一股脑堆在一个字符串里。2.3 带了 ORDER BY 的合并必须包一层再排序报表场景里每个分查询为了分页或局部展示可能带了ORDER BY合并后你又希望整批数据按统一规则排序。直接把两个带ORDER BY的查询用UNION ALL拼起来Oracle 会报 ORA-00933: SQL command not properly ended。OPEN c_merged FOR SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary FROM emp_01 ORDER BY salary DESC ) UNION ALL SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary FROM emp_02 ORDER BY salary DESC ) ORDER BY salary DESC;注意最外层ORDER BY作用于整个合并结果子查询里的ORDER BY纯粹是为了满足局部业务需要。这里最容易踩坑的是ORDER BY后面如果写列别名别名必须在外层可见如果写列序号序号以合并后第一个查询的列顺序为准。我见过同事在这里用子查询里的列序号结果排序完全不对排查了很久才发现序号指向了不同的列。3. 类型不一致时用 CAST 统一合并前的必备步骤3.1 两个游标列类型不同UNION ALL 直接报错几个游标结构“看似一样”实际上列的精度或类型并不一致这是合并且报错的头号原因。典型场景一个游标里emp_id是NUMBER(6)另一个是VARCHAR2(10)或者一个游标里hire_date是DATE另一个是VARCHAR2(20)存的字符串日期。UNION ALL对类型匹配非常严格遇到这种差异直接抛 ORA-01790。这类问题最隐蔽的情况是列名相同、数据类型相同但长度不同比如VARCHAR2(20)和VARCHAR2(200)UNION ALL多数时候能过但结果集会按第一个查询的列长度截断第二个查询的数据。你在开发库测不出来上了生产才发现某些值被截了。OPEN c_merged FOR SELECT emp_id, emp_name, TO_CHAR(hire_date, YYYY-MM-DD) AS hire_date FROM emp_01 UNION ALL SELECT emp_id, emp_name, hire_date FROM emp_02;把hire_date统一TO_CHAR成字符串两个游标该列的返回类型就一致了。这里有个默认规则DATE转VARCHAR2建议显式指定格式不要依赖NLS_DATE_FORMAT因为会话级参数不同同一段代码在不同环境查出来的结果可能一个是2024-01-15另一个是15-JAN-24。3.2 NUMBER 转 VARCHAR2精度和格式都要管游标里如果有NUMBER列另一个游标对应列是VARCHAR2直接把NUMBER隐式转字符串会带来两个问题一是效率隐式转换会让该列上的索引失效如果后续有过滤条件二是格式不可控比如金额列 1234.50 可能变成 1234.5也可能变成 1,234.5取决于 NLS 参数。SELECT emp_id, TO_CHAR(salary, FM999999990.00) AS salary FROM emp_01 UNION ALL SELECT emp_id, salary FROM emp_02;FM格式修饰符的作用是去掉前导空格999999990.00定义了整数部分最大 10 位、小数固定两位。这里参数设计的要点是整数部分位数要按业务最大值留足少了会变成#小数位按需定义报表需求一般两位做数据交换的接口可能需要更多。合并完成后调用方拿到的salary就是固定格式的字符串不会再受 NLS 影响。3.3 NULL 值在 UNION ALL 里的行为差异UNION ALL不会去重所以 NULL 行会原样保留。但真正的问题是某个游标里该列恒为 NULL另一个游标里该列有值类型上仍要求一致。比如emp_01的bonus列是NUMBERemp_02的对应列查出来全是 NULLOracle 有时会推断为VARCHAR2或直接报类型错误。SELECT emp_id, emp_name, bonus FROM emp_01 UNION ALL SELECT emp_id, emp_name, TO_NUMBER(NULL) FROM emp_02;TO_NUMBER(NULL)显式声明该列类型为NUMBER避免 Oracle 在类型推断时产生歧义。这是合并游标时很实用的小技巧因为SELECT NULL FROM有时会被推断为VARCHAR2和前一个游标的NUMBER列冲突。4. 管道表函数方案游标结构复杂时的替代路径4.1 什么时候该放弃 UNION ALL 改用管道表函数有些场景UNION ALL拼不起来比如三个游标的过滤条件依赖前一个游标的执行结果必须逐行处理后再决定下一批数据或者你需要对合并后的每一行做校验、清洗、打标记SQL 层做不了。这时候就可以把sys_refcursor的数据 FETCH 出来通过管道表函数逐行输出。管道表函数适合“取数—处理—输出”三段式结构且结果集结构固定。代价是性能比纯 SQL 差——每一行都要经过 PL/SQL 引擎。但如果数据量在几万行以内这个差距可以接受换来的是逻辑可控、排查方便。CREATE OR REPLACE TYPE emp_row_type AS OBJECT ( emp_id NUMBER(6), emp_name VARCHAR2(50), salary NUMBER(10,2) ); / CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_row_type; / CREATE OR REPLACE FUNCTION merge_refcursors( p_c1 SYS_REFCURSOR, p_c2 SYS_REFCURSOR ) RETURN emp_tab_type PIPELINED IS v_emp emp_row_type : emp_row_type(NULL, NULL, NULL); v_id NUMBER(6); v_name VARCHAR2(50); v_sal NUMBER(10,2); BEGIN LOOP FETCH p_c1 INTO v_id, v_name, v_sal; EXIT WHEN p_c1%NOTFOUND; v_emp : emp_row_type(v_id, v_name, v_sal); PIPE ROW(v_emp); END LOOP; CLOSE p_c1; LOOP FETCH p_c2 INTO v_id, v_name, v_sal; EXIT WHEN p_c2%NOTFOUND; v_emp : emp_row_type(v_id, v_name, v_sal); PIPE ROW(v_emp); END LOOP; CLOSE p_c2; RETURN; END; /这个函数把两个游标逐行取出来封装成对象后通过PIPE ROW输出。注意 FETCH 的变量列数、顺序、类型必须和游标完全一致否则 ORA-06504 或 ORA-00932 会冒出来。p_c1%NOTFOUND是循环退出条件FETCH 不到行时返回 TRUE。每个游标处理完后记得CLOSE不然会话里的游标数会一直涨最后报 ORA-01000。4.2 管道表函数里嵌套查询数据处理后动态决定下一个游标更复杂的场景是第二个游标的参数依赖第一个游标查出来的值。比如先查出部门列表再遍历部门查员工。这种情况没法在一个OPEN FOR里完成管道表函数是天然载体。CREATE OR REPLACE FUNCTION merge_dept_emp( p_dept_cursor SYS_REFCURSOR ) RETURN emp_tab_type PIPELINED IS v_dept_id NUMBER(4); v_emp_cursor SYS_REFCURSOR; v_emp emp_row_type : emp_row_type(NULL, NULL, NULL); v_id NUMBER(6); v_name VARCHAR2(50); v_sal NUMBER(10,2); BEGIN LOOP FETCH p_dept_cursor INTO v_dept_id; EXIT WHEN p_dept_cursor%NOTFOUND; OPEN v_emp_cursor FOR SELECT emp_id, emp_name, salary FROM emp WHERE dept_id v_dept_id; LOOP FETCH v_emp_cursor INTO v_id, v_name, v_sal; EXIT WHEN v_emp_cursor%NOTFOUND; v_emp : emp_row_type(v_id, v_name, v_sal); PIPE ROW(v_emp); END LOOP; CLOSE v_emp_cursor; END LOOP; CLOSE p_dept_cursor; RETURN; END; /这里的关键是内层游标每次循环都要重新OPEN并且用完后立刻CLOSE。内存上要注意的是内层循环不能在外层游标CLOSE之后还在运行否则会报“cursor is closed”的异常。性能上这个写法会产生典型的 N1 查询——每查一个部门就执行一次员工查询部门数多时建议先批量取部门到集合里再用TABLE()或MEMBER OF优化。4.3 管道表函数与普通游标函数的取舍管道表函数不是万能的。相比直接返回SYS_REFCURSOR它要求先定义对象类型和嵌套表类型而且结果集结构必须事先完全确定没法动态适应“列数不确定”的需求。如果游标列的数量在编译期就确定只是值需要处理管道表函数适合如果列数量都是动态的那还是回到OPEN FOR动态 SQL 方案更实际。性能上还有个容易被忽视的点管道表函数在没有PARALLEL_ENABLE时是串行执行的大批量数据下不会比UNION ALL快。我一般只在这个函数需要被SELECT * FROM TABLE(...)方式反复调用、或需要和其他表做 JOIN 时才选它——这种场景下它比sys_refcursor更灵活因为游标不能直接在 SQL 里参与 JOIN。5. 合并 sys_refcursor 的常见坑与排查思路5.1 游标变量不能直接拼进 SQLORA-00900 / ORA-00942 反复出现现象写了OPEN merged FOR SELECT * FROM c1 UNION ALL SELECT * FROM c2编译报错 ORA-00942 或 ORA-00900换了几种写法都一样。原因sys_refcursor是游标变量不是数据库表或视图SQL 引擎不认识它不能把它直接当数据源。解决SQL 层合并游标内数据一定来自实际的表或视图游标变量本身只活在 PL/SQL 块里。要么回到第 2 章的动态 SQL 拼接方案要么用管道表函数逐行取数。你没法在纯 SQL 里“引用另一个游标”这跟 SQL Server 里临时表变量可以直接 SELECT 不一样是 Oracle 这类数据库在架构上的限制。5.2 未合并检查漏了 CLOSE 导致游标耗尽现象存储过程在循环里反复调用合并逻辑大概几百次后报 ORA-01000: maximum open cursors exceededDBA 查 v$open_cursor 发现大量游标状态是 OPEN。原因动态OPEN FOR打开的游标不会随过程结束自动关闭必须手动CLOSE。代码里如果只OPEN不CLOSE会话的游标上限很快被打满。解决每个OPEN FOR后必须CLOSE并且用异常处理包一层保证出错时也能关闭。常见写法是DECLARE c_merged SYS_REFCURSOR; BEGIN OPEN c_merged FOR SELECT ... FROM ...; -- 处理数据 CLOSE c_merged; EXCEPTION WHEN OTHERS THEN IF c_merged%ISOPEN THEN CLOSE c_merged; END IF; RAISE; END; /%ISOPEN先判断游标是否还开着避免重复 CLOSE 触发 ORA-00902。这条经验在批量报表和接口任务里特别重要游标泄漏是这类存储过程最常见的线上故障原因之一。5.3 隐式转换导致的合并结果偏差现象两个游标合并后某列在部分行里出现截断或者数字排序错乱。检查了很久发现游标里一个列是VARCHAR2另一个是NUMBER但数据库没报错。原因Oracle 在某些场景下做了隐式转换把数字转成字符串或者反过来把字符串转成数字。字符串按字典序排序时10会排在9前面这就是排序错乱的来源。解决合并前统一所有列的类型和格式不要依赖数据库的隐式转换。VARCHAR2列统一转NUMBER就用TO_NUMBER统一转字符串就用TO_CHAR且指定格式。转换后注意长度VARCHAR2(10)存不下转出来的 20 位字符串时要先把目标列类型设大。5.4 TO_CHAR 截断精度NUMBER 转字符串后值变了现象salary列在数据库里是NUMBER(12,2)用TO_CHAR转出来后某些行变成##########或者小数位丢失。原因TO_CHAR的格式模型里整数位数不够或者格式里没写小数位。比如9999999只给了 7 位整数空间而值有 10 位就会出现#。解决格式模型按最大可能值设定整数部分用9重复足够的位数小数部分固定写.00。如果对精度极其敏感比如金额合计场景直接从数据库侧用NUMBER输出不要在存储过程里转字符串把转换放到应用端做。5.5 合并后排序失效ORDER BY 位置不对现象外层加了ORDER BY但结果集没有按预期排序有的行有序有的行乱序。原因UNION ALL合并时若子查询有ORDER BY而外层没有Oracle 不保证合并结果的物理顺序。即使外层加了ORDER BY如果排序列的类型在合并后被隐式转成了别的类型排序仍可能按转换后的值进行顺序和业务预期不符。解决排序放到最外层排序表达式必须显式引用合并后结果集的列名或别名。需要对数字排序就先确保列的返回类型是NUMBER需要在字符串上按数字排序就先TO_NUMBER。经验是合并游标别依赖自然顺序任何顺序需求都明确写在外层ORDER BY。6. 合并结果验证用 MINUS 反向核对数据一致性合并代码写好了怎么证明合并结果和原始几个游标的数据完全一致最实用的验证手段是利用集合运算做差分对比。思路是把合并结果当作一个数据源和分别从原表查出的结果做MINUS对比。两边的差集都为空说明没有丢行、没有多行、列值没有变化。-- 合并前的三个游标对应的原始查询 SELECT dept_id, emp_name, salary FROM emp_01 MINUS SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary FROM emp_01 UNION ALL SELECT dept_id, emp_name, salary FROM emp_02 WHERE 1 0 );这个例子验证的是单向包含关系。更完整的做法是把合并后的结果存入临时表然后分别对原始表和临时表做双向MINUS。注意MINUS本身会去重所以它适用于验证“集合内容一致”不适用于验证“行数完全一样”。要验证重复行数量得用GROUP BY COUNT(*)对比。还有一个技巧验证列类型是否一致时直接查ALL_TAB_COLUMNS或USER_TAB_COLUMNS对比游标取数列的数据类型这个方法比逐行 FETCH 后看变量类型更快。合并前我先查一遍元数据类型一致再拼 SQL能省掉大部分试错时间。SELECT column_name, data_type, data_length FROM all_tab_columns WHERE table_name IN (EMP_01, EMP_02) AND column_name IN (EMP_ID, EMP_NAME, SALARY) ORDER BY table_name, column_id;看着这个结果你基本能预判UNION ALL会不会报类型错——EMP_NAME如果在两个表里一个是VARCHAR2(20)一个是VARCHAR2(50)虽然不会报错但你得知道结果集会按第一个查询的列长度截断需要提前处理。这是我在合并多个sys_refcursor上最常做的一步也是少走弯路的关键检查。这些验证步骤做完合并逻辑才算真正落地。你写的存储过程如果以后还要给别人维护建议把这些验证 SQL 保留在代码注释里或者单独写成一段脚本放在同一个包目录下省得后来人又要重新推一遍合并逻辑。这个方向本身不复杂但每个细节都踩过才知道怎么写最稳希望这些经验能帮到你。本文还有配套的精品资源点击获取 Oracle多游标合并：sys_refcursor的UNION ALL与管道表函数实战
简介针对Oracle数据库开发中需要合并多个sys_refcursor动态游标的典型场景这份PDF技术笔记提供了基于XML序列化与解析的完整解决方案。内容从存储过程PROC_A复用的实际需求切入指出重写逻辑、复制代码、建立临时表等方法在面对动态列或不固定结果集时的明显局限进而讲解如何利用XMLTYPE(refcursor)直接构造XML、通过getClobVal()获取完整CLOB、使用DBMS_LOB包的CREATE_TEMPORARY、WRITEAPPEND、APPEND等方法拼接多个游标中的ROW片段再以XMLTABLE解析合并后的文档并返回新的sys_refcursor。文中包含可直接参考的PL/SQL示例并覆盖DBMS_OUTPUT调试、XPath表达式/ROWSET/ROW提取节点等关键细节可帮助Oracle开发人员快速掌握游标合并与XML互操作技巧减少重复代码并降低复杂存储过程的维护成本。资源为1个PDF文档压缩包约75KB内容浓缩精炼适合作为日常开发的速查手册目前已有525人学习下载适合具备一定PL/SQL基础、希望提升存储过程开发效率的工程师阅读参考也可用于深入理解Oracle XML特性。1. 合并多个 sys_refcursor先搞清楚要合的是什么在 Oracle 存储过程里sys_refcursor是返回结果集最常用的方式接手过一个多模块汇总报表后你会发现真正的痛点不是“怎么打开一个游标”而是“怎么把好几个游标的结果合并成一个交出去”。调用方往往是 Java 的ResultSet或报表工具它们只认一个结果集你不可能在存储过程里把多个游标直接“加”起来——PL/SQL 没有这种操作符。合并的本质是把多个 REF CURSOR 指向的查询结果统一成一个结果集输出。实现上常见的有三条路一是改写 SQL用UNION ALL合并所有查询后一次性OPEN二是用管道表函数逐行输出三是先取出游标数据再做内存组装。绝大多数业务场景第一条路最干净后两条是兜底方案。这篇就沿着这三条路径展开把类型转换、排序、空值处理和性能这些坑一个个说清楚适合正在写报表存储过程或批量数据导出的开发人员。2. 用 OPEN FOR UNION ALL 合并最小可行方案2.1 为什么先考虑 UNION ALL 而不是逐行 FETCH合并多个sys_refcursor最简单的思路不是“合并游标”而是“合并产生游标的 SQL”。你手上有三个游标变量每个都来自一段OPEN cursor_var FOR SELECT ...如果这些查询的结构一致把它们的 SQL 文本用UNION ALL拼起来再一次性OPEN到一个新游标里调用方拿到的就是一个完整结果集。这么做的好处是结果集在数据库内部完成合并不用把数据拉到 PL/SQL 引擎再逐行搬性能损耗最小。而且UNION ALL不会去重适合报表里“多批数据直接追加”的语义——比如一月的销售数据和二月的销售数据合在一起本来就允许重复订单号用UNION反而会把重复行吞掉造成数据缺失。DECLARE c_merged SYS_REFCURSOR; BEGIN OPEN c_merged FOR SELECT dept_id, emp_name, salary FROM emp_01 UNION ALL SELECT dept_id, emp_name, salary FROM emp_02 UNION ALL SELECT dept_id, emp_name, salary FROM emp_03; END; /这段代码没有做任何内存操作只是把三个查询合并成一个 SQL 文本交给OPEN FOR。逻辑上要求每个SELECT的列数、列顺序、数据类型完全一致否则 ORA-01789 或 ORA-01790 会直接抛出来。参数方面没有额外设置合并后游标的列名由第一个SELECT决定这点在调用方按列名取数时要特别注意。2.2 结构一致的游标变量如何动态拼出合并 SQL实际开发里游标往往不是直接写死在代码里的而是从别的函数或过程中返回。比如你从三个子过程里分别拿到c1、c2、c3这三个游标的列结构一样但你没法把游标变量本身拼到 SQL 里。常见做法是让每个子过程不仅返回游标还返回一段对应的 SQL 文本然后统一拼接。PROCEDURE merge_cursors( p_sql_1 VARCHAR2, p_sql_2 VARCHAR2, p_sql_3 VARCHAR2, p_merged OUT SYS_REFCURSOR ) IS v_sql VARCHAR2(32767); BEGIN v_sql : SELECT * FROM ( || p_sql_1 || ) UNION ALL || SELECT * FROM ( || p_sql_2 || ) UNION ALL || SELECT * FROM ( || p_sql_3 || ); OPEN p_merged FOR v_sql; END; /这里的核心是把外部传入的 SQL 文本包一层子查询再用UNION ALL连接每段 SQL 里即使带了ORDER BY子查询包一层也能避免 ORA-00933。p_sql_1这类参数通常是动态拼出来的比如根据月份拼表名。参数上唯一要严格控制的是VARCHAR2的长度——拼接三四个长 SQL 很容易超过 32767 字节超了会报 ORA-00972 或 ORA-06502我一般会把 SQL 文本拆成多个变量再拼不要一股脑堆在一个字符串里。2.3 带了 ORDER BY 的合并必须包一层再排序报表场景里每个分查询为了分页或局部展示可能带了ORDER BY合并后你又希望整批数据按统一规则排序。直接把两个带ORDER BY的查询用UNION ALL拼起来Oracle 会报 ORA-00933: SQL command not properly ended。OPEN c_merged FOR SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary FROM emp_01 ORDER BY salary DESC ) UNION ALL SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary FROM emp_02 ORDER BY salary DESC ) ORDER BY salary DESC;注意最外层ORDER BY作用于整个合并结果子查询里的ORDER BY纯粹是为了满足局部业务需要。这里最容易踩坑的是ORDER BY后面如果写列别名别名必须在外层可见如果写列序号序号以合并后第一个查询的列顺序为准。我见过同事在这里用子查询里的列序号结果排序完全不对排查了很久才发现序号指向了不同的列。3. 类型不一致时用 CAST 统一合并前的必备步骤3.1 两个游标列类型不同UNION ALL 直接报错几个游标结构“看似一样”实际上列的精度或类型并不一致这是合并且报错的头号原因。典型场景一个游标里emp_id是NUMBER(6)另一个是VARCHAR2(10)或者一个游标里hire_date是DATE另一个是VARCHAR2(20)存的字符串日期。UNION ALL对类型匹配非常严格遇到这种差异直接抛 ORA-01790。这类问题最隐蔽的情况是列名相同、数据类型相同但长度不同比如VARCHAR2(20)和VARCHAR2(200)UNION ALL多数时候能过但结果集会按第一个查询的列长度截断第二个查询的数据。你在开发库测不出来上了生产才发现某些值被截了。OPEN c_merged FOR SELECT emp_id, emp_name, TO_CHAR(hire_date, YYYY-MM-DD) AS hire_date FROM emp_01 UNION ALL SELECT emp_id, emp_name, hire_date FROM emp_02;把hire_date统一TO_CHAR成字符串两个游标该列的返回类型就一致了。这里有个默认规则DATE转VARCHAR2建议显式指定格式不要依赖NLS_DATE_FORMAT因为会话级参数不同同一段代码在不同环境查出来的结果可能一个是2024-01-15另一个是15-JAN-24。3.2 NUMBER 转 VARCHAR2精度和格式都要管游标里如果有NUMBER列另一个游标对应列是VARCHAR2直接把NUMBER隐式转字符串会带来两个问题一是效率隐式转换会让该列上的索引失效如果后续有过滤条件二是格式不可控比如金额列 1234.50 可能变成 1234.5也可能变成 1,234.5取决于 NLS 参数。SELECT emp_id, TO_CHAR(salary, FM999999990.00) AS salary FROM emp_01 UNION ALL SELECT emp_id, salary FROM emp_02;FM格式修饰符的作用是去掉前导空格999999990.00定义了整数部分最大 10 位、小数固定两位。这里参数设计的要点是整数部分位数要按业务最大值留足少了会变成#小数位按需定义报表需求一般两位做数据交换的接口可能需要更多。合并完成后调用方拿到的salary就是固定格式的字符串不会再受 NLS 影响。3.3 NULL 值在 UNION ALL 里的行为差异UNION ALL不会去重所以 NULL 行会原样保留。但真正的问题是某个游标里该列恒为 NULL另一个游标里该列有值类型上仍要求一致。比如emp_01的bonus列是NUMBERemp_02的对应列查出来全是 NULLOracle 有时会推断为VARCHAR2或直接报类型错误。SELECT emp_id, emp_name, bonus FROM emp_01 UNION ALL SELECT emp_id, emp_name, TO_NUMBER(NULL) FROM emp_02;TO_NUMBER(NULL)显式声明该列类型为NUMBER避免 Oracle 在类型推断时产生歧义。这是合并游标时很实用的小技巧因为SELECT NULL FROM有时会被推断为VARCHAR2和前一个游标的NUMBER列冲突。4. 管道表函数方案游标结构复杂时的替代路径4.1 什么时候该放弃 UNION ALL 改用管道表函数有些场景UNION ALL拼不起来比如三个游标的过滤条件依赖前一个游标的执行结果必须逐行处理后再决定下一批数据或者你需要对合并后的每一行做校验、清洗、打标记SQL 层做不了。这时候就可以把sys_refcursor的数据 FETCH 出来通过管道表函数逐行输出。管道表函数适合“取数—处理—输出”三段式结构且结果集结构固定。代价是性能比纯 SQL 差——每一行都要经过 PL/SQL 引擎。但如果数据量在几万行以内这个差距可以接受换来的是逻辑可控、排查方便。CREATE OR REPLACE TYPE emp_row_type AS OBJECT ( emp_id NUMBER(6), emp_name VARCHAR2(50), salary NUMBER(10,2) ); / CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_row_type; / CREATE OR REPLACE FUNCTION merge_refcursors( p_c1 SYS_REFCURSOR, p_c2 SYS_REFCURSOR ) RETURN emp_tab_type PIPELINED IS v_emp emp_row_type : emp_row_type(NULL, NULL, NULL); v_id NUMBER(6); v_name VARCHAR2(50); v_sal NUMBER(10,2); BEGIN LOOP FETCH p_c1 INTO v_id, v_name, v_sal; EXIT WHEN p_c1%NOTFOUND; v_emp : emp_row_type(v_id, v_name, v_sal); PIPE ROW(v_emp); END LOOP; CLOSE p_c1; LOOP FETCH p_c2 INTO v_id, v_name, v_sal; EXIT WHEN p_c2%NOTFOUND; v_emp : emp_row_type(v_id, v_name, v_sal); PIPE ROW(v_emp); END LOOP; CLOSE p_c2; RETURN; END; /这个函数把两个游标逐行取出来封装成对象后通过PIPE ROW输出。注意 FETCH 的变量列数、顺序、类型必须和游标完全一致否则 ORA-06504 或 ORA-00932 会冒出来。p_c1%NOTFOUND是循环退出条件FETCH 不到行时返回 TRUE。每个游标处理完后记得CLOSE不然会话里的游标数会一直涨最后报 ORA-01000。4.2 管道表函数里嵌套查询数据处理后动态决定下一个游标更复杂的场景是第二个游标的参数依赖第一个游标查出来的值。比如先查出部门列表再遍历部门查员工。这种情况没法在一个OPEN FOR里完成管道表函数是天然载体。CREATE OR REPLACE FUNCTION merge_dept_emp( p_dept_cursor SYS_REFCURSOR ) RETURN emp_tab_type PIPELINED IS v_dept_id NUMBER(4); v_emp_cursor SYS_REFCURSOR; v_emp emp_row_type : emp_row_type(NULL, NULL, NULL); v_id NUMBER(6); v_name VARCHAR2(50); v_sal NUMBER(10,2); BEGIN LOOP FETCH p_dept_cursor INTO v_dept_id; EXIT WHEN p_dept_cursor%NOTFOUND; OPEN v_emp_cursor FOR SELECT emp_id, emp_name, salary FROM emp WHERE dept_id v_dept_id; LOOP FETCH v_emp_cursor INTO v_id, v_name, v_sal; EXIT WHEN v_emp_cursor%NOTFOUND; v_emp : emp_row_type(v_id, v_name, v_sal); PIPE ROW(v_emp); END LOOP; CLOSE v_emp_cursor; END LOOP; CLOSE p_dept_cursor; RETURN; END; /这里的关键是内层游标每次循环都要重新OPEN并且用完后立刻CLOSE。内存上要注意的是内层循环不能在外层游标CLOSE之后还在运行否则会报“cursor is closed”的异常。性能上这个写法会产生典型的 N1 查询——每查一个部门就执行一次员工查询部门数多时建议先批量取部门到集合里再用TABLE()或MEMBER OF优化。4.3 管道表函数与普通游标函数的取舍管道表函数不是万能的。相比直接返回SYS_REFCURSOR它要求先定义对象类型和嵌套表类型而且结果集结构必须事先完全确定没法动态适应“列数不确定”的需求。如果游标列的数量在编译期就确定只是值需要处理管道表函数适合如果列数量都是动态的那还是回到OPEN FOR动态 SQL 方案更实际。性能上还有个容易被忽视的点管道表函数在没有PARALLEL_ENABLE时是串行执行的大批量数据下不会比UNION ALL快。我一般只在这个函数需要被SELECT * FROM TABLE(...)方式反复调用、或需要和其他表做 JOIN 时才选它——这种场景下它比sys_refcursor更灵活因为游标不能直接在 SQL 里参与 JOIN。5. 合并 sys_refcursor 的常见坑与排查思路5.1 游标变量不能直接拼进 SQLORA-00900 / ORA-00942 反复出现现象写了OPEN merged FOR SELECT * FROM c1 UNION ALL SELECT * FROM c2编译报错 ORA-00942 或 ORA-00900换了几种写法都一样。原因sys_refcursor是游标变量不是数据库表或视图SQL 引擎不认识它不能把它直接当数据源。解决SQL 层合并游标内数据一定来自实际的表或视图游标变量本身只活在 PL/SQL 块里。要么回到第 2 章的动态 SQL 拼接方案要么用管道表函数逐行取数。你没法在纯 SQL 里“引用另一个游标”这跟 SQL Server 里临时表变量可以直接 SELECT 不一样是 Oracle 这类数据库在架构上的限制。5.2 未合并检查漏了 CLOSE 导致游标耗尽现象存储过程在循环里反复调用合并逻辑大概几百次后报 ORA-01000: maximum open cursors exceededDBA 查 v$open_cursor 发现大量游标状态是 OPEN。原因动态OPEN FOR打开的游标不会随过程结束自动关闭必须手动CLOSE。代码里如果只OPEN不CLOSE会话的游标上限很快被打满。解决每个OPEN FOR后必须CLOSE并且用异常处理包一层保证出错时也能关闭。常见写法是DECLARE c_merged SYS_REFCURSOR; BEGIN OPEN c_merged FOR SELECT ... FROM ...; -- 处理数据 CLOSE c_merged; EXCEPTION WHEN OTHERS THEN IF c_merged%ISOPEN THEN CLOSE c_merged; END IF; RAISE; END; /%ISOPEN先判断游标是否还开着避免重复 CLOSE 触发 ORA-00902。这条经验在批量报表和接口任务里特别重要游标泄漏是这类存储过程最常见的线上故障原因之一。5.3 隐式转换导致的合并结果偏差现象两个游标合并后某列在部分行里出现截断或者数字排序错乱。检查了很久发现游标里一个列是VARCHAR2另一个是NUMBER但数据库没报错。原因Oracle 在某些场景下做了隐式转换把数字转成字符串或者反过来把字符串转成数字。字符串按字典序排序时10会排在9前面这就是排序错乱的来源。解决合并前统一所有列的类型和格式不要依赖数据库的隐式转换。VARCHAR2列统一转NUMBER就用TO_NUMBER统一转字符串就用TO_CHAR且指定格式。转换后注意长度VARCHAR2(10)存不下转出来的 20 位字符串时要先把目标列类型设大。5.4 TO_CHAR 截断精度NUMBER 转字符串后值变了现象salary列在数据库里是NUMBER(12,2)用TO_CHAR转出来后某些行变成##########或者小数位丢失。原因TO_CHAR的格式模型里整数位数不够或者格式里没写小数位。比如9999999只给了 7 位整数空间而值有 10 位就会出现#。解决格式模型按最大可能值设定整数部分用9重复足够的位数小数部分固定写.00。如果对精度极其敏感比如金额合计场景直接从数据库侧用NUMBER输出不要在存储过程里转字符串把转换放到应用端做。5.5 合并后排序失效ORDER BY 位置不对现象外层加了ORDER BY但结果集没有按预期排序有的行有序有的行乱序。原因UNION ALL合并时若子查询有ORDER BY而外层没有Oracle 不保证合并结果的物理顺序。即使外层加了ORDER BY如果排序列的类型在合并后被隐式转成了别的类型排序仍可能按转换后的值进行顺序和业务预期不符。解决排序放到最外层排序表达式必须显式引用合并后结果集的列名或别名。需要对数字排序就先确保列的返回类型是NUMBER需要在字符串上按数字排序就先TO_NUMBER。经验是合并游标别依赖自然顺序任何顺序需求都明确写在外层ORDER BY。6. 合并结果验证用 MINUS 反向核对数据一致性合并代码写好了怎么证明合并结果和原始几个游标的数据完全一致最实用的验证手段是利用集合运算做差分对比。思路是把合并结果当作一个数据源和分别从原表查出的结果做MINUS对比。两边的差集都为空说明没有丢行、没有多行、列值没有变化。-- 合并前的三个游标对应的原始查询 SELECT dept_id, emp_name, salary FROM emp_01 MINUS SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary FROM emp_01 UNION ALL SELECT dept_id, emp_name, salary FROM emp_02 WHERE 1 0 );这个例子验证的是单向包含关系。更完整的做法是把合并后的结果存入临时表然后分别对原始表和临时表做双向MINUS。注意MINUS本身会去重所以它适用于验证“集合内容一致”不适用于验证“行数完全一样”。要验证重复行数量得用GROUP BY COUNT(*)对比。还有一个技巧验证列类型是否一致时直接查ALL_TAB_COLUMNS或USER_TAB_COLUMNS对比游标取数列的数据类型这个方法比逐行 FETCH 后看变量类型更快。合并前我先查一遍元数据类型一致再拼 SQL能省掉大部分试错时间。SELECT column_name, data_type, data_length FROM all_tab_columns WHERE table_name IN (EMP_01, EMP_02) AND column_name IN (EMP_ID, EMP_NAME, SALARY) ORDER BY table_name, column_id;看着这个结果你基本能预判UNION ALL会不会报类型错——EMP_NAME如果在两个表里一个是VARCHAR2(20)一个是VARCHAR2(50)虽然不会报错但你得知道结果集会按第一个查询的列长度截断需要提前处理。这是我在合并多个sys_refcursor上最常做的一步也是少走弯路的关键检查。这些验证步骤做完合并逻辑才算真正落地。你写的存储过程如果以后还要给别人维护建议把这些验证 SQL 保留在代码注释里或者单独写成一段脚本放在同一个包目录下省得后来人又要重新推一遍合并逻辑。这个方向本身不复杂但每个细节都踩过才知道怎么写最稳希望这些经验能帮到你。本文还有配套的精品资源点击获取