Oracle如何关联两表匹配同section_id与party_id下的管理员数据

实现方案 #

1. 核心查询逻辑（无需存储过程可直接使用）

不需要循环查询单个emp_code，直接通过自关联employee表即可一次性查询所有员工对应的管理员数据，SQL如下：

SELECT
    e1.emp_code AS Emp_Code,
    e1.section_id AS Section_ID,
    e1.party_id AS Party_ID,
    e1.admin_type AS Admin_Type,
    e2.emp_code AS Admin,
    g."GROUP" AS Group,
    e2.admin_type AS Admin_Type_Admin
FROM employee e1
-- 关联同部门、同所属方下的管理员账号
INNER JOIN employee e2
    ON e1.section_id = e2.section_id
    AND e1.party_id = e2.party_id
    AND e2.admin_type IS NOT NULL
-- 关联管理员分组表，过滤分组为1的管理员
INNER JOIN group_admin g
    ON e2.admin_type = g.admin_type
    AND g."GROUP" = '1'
-- 过滤普通员工（Admin_Type为NULL的账号）
WHERE e1.admin_type IS NULL
ORDER BY e1.emp_code;

注意：GROUP是Oracle保留关键字，作为字段名使用时需要加双引号转义。

2. 封装为Oracle存储过程

如果需要通过存储过程返回结果集，使用SYS_REFCURSOR作为输出参数即可：

CREATE OR REPLACE PROCEDURE get_emp_admin_list(
    p_result OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN p_result FOR
        SELECT
            e1.emp_code AS Emp_Code,
            e1.section_id AS Section_ID,
            e1.party_id AS Party_ID,
            e1.admin_type AS Admin_Type,
            e2.emp_code AS Admin,
            g."GROUP" AS Group,
            e2.admin_type AS Admin_Type_Admin
        FROM employee e1
        INNER JOIN employee e2
            ON e1.section_id = e2.section_id
            AND e1.party_id = e2.party_id
            AND e2.admin_type IS NOT NULL
        INNER JOIN group_admin g
            ON e2.admin_type = g.admin_type
            AND g."GROUP" = '1'
        WHERE e1.admin_type IS NULL
        ORDER BY e1.emp_code;
END;
/

3. 存储过程调用示例

DECLARE
    v_result SYS_REFCURSOR;
    v_emp_code VARCHAR2(20);
    v_section_id VARCHAR2(20);
    v_party_id VARCHAR2(20);
    v_admin_type_emp VARCHAR2(10);
    v_admin VARCHAR2(20);
    v_group VARCHAR2(10);
    v_admin_type_admin VARCHAR2(10);
BEGIN
    get_emp_admin_list(v_result);
    LOOP
        FETCH v_result INTO v_emp_code, v_section_id, v_party_id, v_admin_type_emp, v_admin, v_group, v_admin_type_admin;
        EXIT WHEN v_result%NOTFOUND;
        -- 可自定义输出逻辑，示例为打印结果
        DBMS_OUTPUT.PUT_LINE(v_emp_code || ' | ' || v_section_id || ' | ' || v_party_id || ' | ' || NVL(v_admin_type_emp, 'NULL') || ' | ' || v_admin || ' | ' || v_group || ' | ' || v_admin_type_admin);
    END LOOP;
    CLOSE v_result;
END;
/

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