如何在PL/SQL中编写含对象类型输出参数的多连接存储过程

你的原始存储过程用了内连接，这会导致几个问题：一是漏掉没有部门或资产的员工，二是完全查不到像ID4这种只在Assets表存在的员工，而且也没把多个部门/资产聚合成数组。咱们可以通过自定义对象和集合类型来完美解决这个需求，这种方式比多游标更简洁高效，当然多游标也能实现，但没必要绕远路。

首先，我们需要先定义几个自定义类型，用来封装数组和员工的完整信息：

-- 定义部门ID数组类型
CREATE OR REPLACE TYPE DeptIdArray AS TABLE OF VARCHAR2(10);
/

-- 定义资产名称数组类型
CREATE OR REPLACE TYPE AssetNameArray AS TABLE OF VARCHAR2(10);
/

-- 定义EmployeeInfo对象类型，包含所需的所有字段
CREATE OR REPLACE TYPE EmployeeInfoObj AS OBJECT (
    Emp_Id NUMBER,
    Emp_Name VARCHAR2(50),
    Dept_Ids DeptIdArray,
    Assets AssetNameArray
);
/

接下来，编写存储过程。核心思路是先获取所有存在的员工ID（三个表的并集），然后用左连接关联所有表，最后把每个员工的部门和资产聚合成数组：

CREATE OR REPLACE PROCEDURE PRC_TEST(employeeInfo OUT SYS_REFCURSOR) IS
BEGIN
    OPEN employeeInfo FOR
        SELECT
            -- 统一取员工ID，处理左连接后可能的NULL情况
            COALESCE(e.Emp_Id, d.Emp_Id, a.Emp_Id) AS Emp_Id,
            -- 员工姓名，不存在则返回NULL
            e.Emp_Name,
            -- 把同一个员工的所有部门ID聚合成数组，没有则返回空数组
            CAST(COLLECT(DISTINCT d.Dept_Id) AS DeptIdArray) AS Dept_Ids,
            -- 把同一个员工的所有资产名称聚合成数组，没有则返回空数组
            CAST(COLLECT(DISTINCT a.Asset_Name) AS AssetNameArray) AS Assets
        FROM
            -- 先收集所有出现过的员工ID，避免遗漏
            (SELECT Emp_Id FROM Employee
             UNION
             SELECT Emp_Id FROM Department
             UNION
             SELECT Emp_Id FROM Assets) all_emps
        -- 左连接确保即使没有对应数据也能保留记录
        LEFT JOIN Employee e ON all_emps.Emp_Id = e.Emp_Id
        LEFT JOIN Department d ON all_emps.Emp_Id = d.Emp_Id
        LEFT JOIN Assets a ON all_emps.Emp_Id = a.Emp_Id
        -- 按员工ID和姓名分组，聚合部门和资产
        GROUP BY COALESCE(e.Emp_Id, d.Emp_Id, a.Emp_Id), e.Emp_Name
        ORDER BY Emp_Id;
END;
/

咱们来拆解下这段代码的关键点：

• UNION获取所有员工ID：这样能覆盖到所有在Employee、Department、Assets里出现的员工，包括像ID4这种只在Assets里的员工。
• 左连接LEFT JOIN：和内连接不同，左连接会保留主表（这里是all_emps）的所有记录，即使关联的表没有对应数据，对应的字段会返回NULL，完美符合你要的“员工4姓名为NULL”的场景。
• COLLECT聚合函数：把同一个员工的多个部门或资产打包成数组，DISTINCT是为了避免如果部门/资产表有重复记录时，数组里出现重复值。
• COALESCE：因为左连接后，某个表的Emp_Id可能为NULL，所以用这个函数取第一个非空的员工ID，确保每个记录都有正确的Emp_Id。

如果想要测试这个存储过程，可以用这段PL/SQL块来输出结果：

DECLARE
    emp_cursor SYS_REFCURSOR;
    emp_info EmployeeInfoObj;
BEGIN
    PRC_TEST(emp_cursor);
    LOOP
        FETCH emp_cursor INTO emp_info;
        EXIT WHEN emp_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('Emp_Id: ' || emp_info.Emp_Id);
        DBMS_OUTPUT.PUT_LINE('Emp_Name: ' || NVL(emp_info.Emp_Name, 'null'));
        
        DBMS_OUTPUT.PUT('Dept_Ids: ');
        IF emp_info.Dept_Ids IS NOT NULL AND emp_info.Dept_Ids.COUNT > 0 THEN
            FOR i IN emp_info.Dept_Ids.FIRST..emp_info.Dept_Ids.LAST LOOP
                DBMS_OUTPUT.PUT(emp_info.Dept_Ids(i) || ', ');
            END LOOP;
        ELSE
            DBMS_OUTPUT.PUT('null');
        END IF;
        DBMS_OUTPUT.NEW_LINE();
        
        DBMS_OUTPUT.PUT('Assets: ');
        IF emp_info.Assets IS NOT NULL AND emp_info.Assets.COUNT > 0 THEN
            FOR i IN emp_info.Assets.FIRST..emp_info.Assets.LAST LOOP
                DBMS_OUTPUT.PUT(emp_info.Assets(i) || ', ');
            END LOOP;
        ELSE
            DBMS_OUTPUT.PUT('null');
        END IF;
        DBMS_OUTPUT.NEW_LINE();
        DBMS_OUTPUT.NEW_LINE();
    END LOOP;
    CLOSE emp_cursor;
END;
/

至于你问的多游标实现，其实也可以：先打开一个游标遍历所有员工ID，然后对每个ID分别查询部门和资产，再组装成EmployeeInfo对象，但这种方式需要多次查询数据库，数据量大的时候效率会很低，所以更推荐上面的聚合查询方式。

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