PL/SQL中的嵌套游标与递归

Question

我是PL/SQL新手，刚刚开始使用游标。我正在编写一个小程序来打印员工->主管的递归跟踪，一直到大老板。本质上，我的查询将输出employee，supervisor1，然后输出supervisor1，supervisor2，一直到监督，null。我的目标是编写一个简短的PL/SQL程序，为每个员工迭代这个过程。我的代码如下：

set serveroutput on;

DECLARE
    v_iteration number := 0;

BEGIN
    for x in (select * from e)
    loop
        dbms_output.put_line('recursive trace #' || v_iteration);
        v_iteration := v_iteration + 1;
        for y in 
            (WITH sup_hierarch (employee, supervisor) AS
            (select x.ssn, x.superssn FROM e
            UNION ALL
            select supervisor, superssn
            from sup_hierarch join e on sup_hierarch.supervisor = e.ssn
            where sup_hierarch.supervisor is not null)
            select * from sup_hierarch)


        loop
            dbms_output.put_line(y.employee || ', ' || y.supervisor);
        end loop;


    end loop;
END;
/

我使用了两个隐式游标，外部隐式游标用于遍历employee表(为了打印每个雇员的递归跟踪)，内部隐式游标用于遍历递归跟踪本身。代码有点工作，示例输出：

recursive trace #6
666884444, 333445555
666884444, 333445555
666884444, 333445555
666884444, 333445555
666884444, 333445555
666884444, 333445555
666884444, 333445555
666884444, 333445555
333445555, 888665555
333445555, 888665555
333445555, 888665555
333445555, 888665555
333445555, 888665555
333445555, 888665555
333445555, 888665555
333445555, 888665555
888665555,
888665555,
888665555,
888665555,
888665555,
888665555,
888665555,
888665555,

根据我的全部输出，遍历每个员工的循环都是正确工作的。此外，每个员工的递归跟踪都是正确工作的(也就是说，每个员工都有正确的递归深度&堆栈)。但是，递归跟踪中的每一行都打印number_of_employee时间，而我希望它只打印一次。为什么会这样呢？

Answer 1

代码的问题在于with子句的第一部分:它为'e‘中的行返回相同的日期。为什么不这样做呢？

    DECLARE
        v_iteration number := 0; -- useless in the version

    BEGIN
        for y in 
                (WITH sup_hierarch (employee, supervisor) AS
                (select x.ssn, x.superssn FROM e
                UNION ALL
                select supervisor, superssn
                from sup_hierarch join e on sup_hierarch.supervisor = e.ssn
                where sup_hierarch.supervisor is not null)
                select * from sup_hierarch)
        loop
            dbms_output.put_line(y.employee || ', ' || y.supervisor);
        end loop;

    END;
    /

如果您想要两个循环，只需获得迭代：

    DECLARE
        v_iteration number := 0;

    BEGIN
        for x in (select * from e)
        loop
            dbms_output.put_line('recursive trace #' || v_iteration);
            v_iteration := v_iteration + 1;
            for y in 
                (WITH sup_hierarch (employee, supervisor) AS
                (select x.ssn, x.superssn FROM dual
                UNION ALL
                select supervisor, superssn
                from sup_hierarch join e on sup_hierarch.supervisor = e.ssn
                where sup_hierarch.supervisor is not null)
                select * from sup_hierarch)


            loop
                dbms_output.put_line(y.employee || ', ' || y.supervisor);
            end loop;


        end loop;
    END;
    /