Oracle存储过程调用执行顺序查询结果不符问题求助

我有4个存储过程，其中pr_X是主存储过程，代码如下：

CREATE OR REPLACE NONEDITIONABLE PROCEDURE pr_X AS
    v_first_name VARCHAR2(100);
    pr_y(v_first_name);
    pr_z(v_first_name);

pr_X会按顺序调用pr_Y和pr_Z；pr_Y的代码如下：

CREATE OR REPLACE NONEDITIONABLE PROCEDURE pr_y AS
    v_first_name VARCHAR2(100);
    pr_t(v_first_name);

pr_Y内部会调用pr_T，因此实际执行顺序应为pr_Y→pr_T→pr_Z。但通过递归查询all_dependencies得到的结果不符合预期，现需获取正确存储过程实际执行顺序的解决方案。

当前查询结果 #

期望结果 #

解决方案 #

all_dependencies仅记录存储过程间的依赖关系，不会存储代码中调用语句的先后顺序，因此无法直接通过它得到符合实际执行流程的结果。要获取正确的执行顺序，需解析存储过程的源代码，提取调用语句的顺序，再结合依赖关系递归展开。

具体可按以下步骤实现：

1. 从user_source（或all_source）视图中读取目标存储过程的源代码。
2. 解析代码内容，提取其中的存储过程调用语句，记录它们的出现顺序。
3. 对每个被调用的存储过程，递归重复步骤1-2，将子过程的调用序列插入到父过程调用的位置之后。
4. 最终按展开后的完整序列生成执行顺序，并计算对应的调用深度和执行序号。

以下是基于Oracle数据库的示例SQL思路：

WITH proc_calls AS (
    -- 提取主过程pr_X的调用语句及顺序
    SELECT 
        'pr_X' AS parent_proc,
        REGEXP_SUBSTR(text, 'pr_[A-Z]+', 1, LEVEL) AS called_proc,
        ROW_NUMBER() OVER (ORDER BY line, LEVEL) AS call_order
    FROM user_source
    WHERE name = 'PR_X' AND type = 'PROCEDURE'
    CONNECT BY REGEXP_SUBSTR(text, 'pr_[A-Z]+', 1, LEVEL) IS NOT NULL
    GROUP BY line, REGEXP_SUBSTR(text, 'pr_[A-Z]+', 1, LEVEL)
    
    UNION ALL
    
    -- 递归提取子过程的调用语句
    SELECT 
        pc.parent_proc || '->' || pc.called_proc AS parent_proc,
        REGEXP_SUBSTR(us.text, 'pr_[A-Z]+', 1, LEVEL) AS called_proc,
        pc.call_order + (ROW_NUMBER() OVER (ORDER BY us.line, LEVEL) * 0.1) AS call_order
    FROM proc_calls pc
    JOIN user_source us ON us.name = UPPER(pc.called_proc) AND us.type = 'PROCEDURE'
    CONNECT BY REGEXP_SUBSTR(us.text, 'pr_[A-Z]+', 1, LEVEL) IS NOT NULL
    GROUP BY pc.parent_proc, pc.called_proc, pc.call_order, us.line, REGEXP_SUBSTR(us.text, 'pr_[A-Z]+', 1, LEVEL)
)
SELECT 
    called_proc AS CALLED_PROCEDURE,
    LENGTH(parent_proc) - LENGTH(REPLACE(parent_proc, '->', '')) AS DEPTH,
    ROW_NUMBER() OVER (ORDER BY call_order) AS EXECUTION_ORDER
FROM proc_calls
ORDER BY EXECUTION_ORDER;

注意：该SQL基于简单的调用语句格式编写，若存储过程中存在动态SQL、条件分支调用等复杂语法，需要调整正则表达式或增加更完善的代码解析逻辑。

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