Oracle递归逻辑应移出PL/SQL，改用单条SQL递归CTE并封装为函数调用；WITH不可直接嵌套于游标或PL/SQL块中，CONNECT BY更可控且自带循环检测与伪列支持。
oracle 递归逻辑放在存储过程中，用 with 子句替代传统 connect by 递归，通常不能直接“优化性能”——反而容易出错或被忽略关键限制。真正能起效的场景，是把递归逻辑从 pl/sql 循环/游标中**完全移出、改写为单条 sql 的递归 cte**，再在过程里调用它。
递归 WITH 不能直接嵌套在 PL/SQL 块里执行
很多人想在存储过程中这样写：
DECLARE
CURSOR c IS
WITH RECURSIVE tree AS (
SELECT id, name, parent_id FROM org WHERE parent_id IS NULL
UNION ALL
SELECT o.id, o.name, o.parent_id FROM org o JOIN tree t ON o.parent_id = t.id
)
SELECT * FROM tree;
BEGIN
FOR r IN c LOOP ... -- 报错：PL/SQL 不支持 WITH 在游标定义中直接使用（尤其老版本）
END;常见错误是 PLS-00103: Encountered the symbol "WITH" 或运行时报 ORA-32034: unsupported use of WITH clause。原因很实在：
-
WITH子句必须出现在顶层SELECT/INSERT/UPDATE/DELETE中，不能作为 PL/SQL 变量声明或游标定义的一部分（除非 Oracle 12cR2+ 且启用特定兼容模式） - 即使语法通过，PL/SQL 解析器对递归 CTE 的循环检测和剪枝支持弱于 SQL 引擎本身
- 过程里手动拼接
EXECUTE IMMEDIATE调用递归 WITH，会丢失绑定变量安全性和执行计划缓存
真正可行的 WITH + 存储过程组合方式
把递归逻辑封装成视图或带参数的函数，再由存储过程调用。这是稳定、可维护、能走真实执行计划的路径：
- 用
CREATE VIEW定义递归 CTE 视图（需注意：Oracle 不允许视图含WITH RECURSIVE，但可建物化视图或包装为函数） - 更推荐：创建一个返回
SYS_REFCURSOR的函数，内部用纯 SQL 写递归 WITH，例如：
CREATE OR REPLACE FUNCTION get_org_tree(p_root_id IN VARCHAR2)
RETURN SYS_REFCURSOR IS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
WITH RECURSIVE org_tree AS (
SELECT id, name, parent_id, 1 lvl FROM org WHERE id = p_root_id
UNION ALL
SELECT o.id, o.name, o.parent_id, ot.lvl + 1
FROM org o
JOIN org_tree ot ON o.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY lvl;
RETURN l_cursor;
END;然后在存储过程中调用它：
PROCEDURE list_subtree(p_dept_id IN VARCHAR2, p_result OUT SYS_REFCURSOR) IS BEGIN p_result := get_org_tree(p_dept_id); -- 直接赋值，不触发 PL/SQL 解析 WITH END;
为什么不用 CONNECT BY？它比 WITH 更快吗？
不是绝对更快，而是更可控。关键差异在循环处理和终止条件：
-
CONNECT BY自带隐式循环检测（NOCYCLE），且LEVEL和CONNECT_BY_ISLEAF是原生伪列，无需手写层级计数 - 递归
WITH必须显式控制层级深度（如加WHERE lvl <= 10），否则可能无限递归并耗尽 PGA 内存 -
CONNECT BY在索引得当（如(parent_id, id)复合索引）时，执行计划常走 INDEX RANGE SCAN；而递归 WITH 默认倾向 HASH JOIN，若中间结果大，易触发磁盘临时段 - Oracle 12cR1+ 对递归 WITH 增强了物化能力（
MATERIALIZE提示可用），但需手动加/*+ MATERIALIZE */，且不保证生效
容易被忽略的三个硬性约束
这些不是“建议”，是实际跑不通就会卡住的点：
- Oracle 版本必须 ≥ 11.2.0.2 才支持
RECURSIVE关键字；11gR1 及更早版本只认CONNECT BY - 递归子查询中，
UNION ALL左右两边的列名、类型、顺序必须严格一致，否则报ORA-32033: unsupported column aliasing - 不能在递归成员中引用外部作用域的 PL/SQL 变量（比如
WHERE o.parent_id = v_parent_id），必须通过参数传入或改用CONNECT BY PRIOR配合绑定变量
递归逻辑一旦进入 PL/SQL 层，就绕不开解析上下文切换和执行计划隔离问题。最省事也最稳的方式：让递归发生在 SQL 层，过程只负责传参、打开游标、异常捕获——别试图在过程里“组装”递归。
