PL/SQL局部变量找不到问题求助：用户继承角色查询脚本开发遇阻

我明白这种摸不着头绪的感觉——刚接触PL/SQL的时候，递归处理层级数据确实容易卡壳。你要实现的应该是递归查找指定角色所有继承的父角色，咱们一步步来完善你的代码。

首先先把你给出的代码片段整理清晰：

我目前十分困惑，正在编写一个用于查找用户所继承的所有角色的PL/SQL脚本，但该脚本仍在开发中。由于我对PL/SQL了解不足，开发进度十分缓慢。以下是我编写的部分代码：

-- 表类型声明
create or replace TYPE TEST AS TABLE OF VARCHAR(30);
-- 函数定义
create or replace FUNCTION FINDPARENTS ( ROLENAME IN VARCHAR2 ) RETURN TEST IS
tt_t TEST;
BEGIN
DECLARE
results test;
toadd test := TEST(ROLENAME);
counter nu...

现有代码的问题点 #

1. 变量声明层级冗余：PL/SQL函数的变量应该直接放在IS和BEGIN之间，没必要嵌套额外的DECLARE块；
2. 递归/循环逻辑缺失：要遍历角色的继承链，需要循环处理每一级父角色直到无更多层级；
3. 未处理多父角色、无父角色等边界情况，容易触发异常。

完善后的实现方案 #

假设你的角色继承关系存储在名为ROLES的表中，包含ROLE_NAME（角色名）和PARENT_ROLE（父角色名）字段，我帮你调整并补全函数：

-- 先确保自定义表类型存在
CREATE OR REPLACE TYPE TEST AS TABLE OF VARCHAR2(30);
/

CREATE OR REPLACE FUNCTION FINDPARENTS(p_role_name IN VARCHAR2) 
RETURN TEST 
IS
    l_results     TEST := TEST();       -- 存储最终所有继承的父角色
    l_current_roles TEST := TEST(p_role_name); -- 当前要处理的角色集合
    l_temp_roles  TEST := TEST();       -- 临时存储下一级父角色
BEGIN
    -- 循环遍历，直到没有更多父角色可查找
    WHILE l_current_roles.COUNT > 0 LOOP
        l_temp_roles.DELETE; -- 清空临时集合，准备存储新的父角色
        
        -- 批量查询当前所有角色的父角色，避免单条查询的异常
        SELECT DISTINCT parent_role
        BULK COLLECT INTO l_temp_roles
        FROM roles
        WHERE role_name IN (SELECT column_value FROM TABLE(l_current_roles))
          AND parent_role IS NOT NULL
          AND parent_role NOT IN (SELECT column_value FROM TABLE(l_results));
        
        -- 将新找到的父角色合并到结果集
        l_results := l_results MULTISET UNION DISTINCT l_temp_roles;
        -- 更新当前处理的角色为刚找到的父角色，进入下一轮循环
        l_current_roles := l_temp_roles;
    END LOOP;
    
    RETURN l_results;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 当没有父角色时，返回空结果集（可根据需求调整为返回初始角色）
        RETURN l_results;
END FINDPARENTS;
/

关键说明 #

• 批量收集（BULK COLLECT）：用批量查询替代单条SELECT INTO，能同时处理一个角色有多个父角色的场景，避免TOO_MANY_ROWS异常；
• 去重处理：MULTISET UNION DISTINCT确保结果集中不会出现重复的角色；
• 循环逻辑：通过不断迭代处理父角色集合，实现递归遍历整个继承链；
• 异常捕获：处理没有父角色的边界情况，让函数更健壮。

使用示例 #

调用函数并查看结果：

SELECT column_value AS inherited_parent_role
FROM TABLE(FINDPARENTS('你的目标角色名'));

你只需要根据自己实际的表结构（比如表名、字段名，或者如果是通过中间关联表存储继承关系）调整SELECT语句即可。

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