嵌套循环仅返回匹配行的存储过程结果修正及优化问询

问题描述 #

需要实现一个存储过程，在两张表中匹配员工的姓名（name）和邮箱（email），并标记匹配状态（MATCHED_ROW）。原实现采用两个嵌套游标循环遍历员工信息，但当前输出存在冗余行，希望得到指定格式的结果，同时咨询是否有替代嵌套循环的高效实现方案。

原存储过程代码 #

procedure proc_test IS 
Cursor a is Select 1.id  id,
           2.name name,
           2.email email,
           3.phone phone,
           4.address address from table 1 join table 2 on table 1 where table1.id=table2.id join table 3 on table 1 where table1.id=table3.id join table 4 on table 1 where table1.id=table4.id;

Cursor b is  Select 5.id,
       5.name,
       5.email from table 5 where 5.table_id=id;

    BEGIN 
 FOR p IN a LOOP    

id=p.id; name=p.name; email=p.email; phone=p.phone; address=p.address;

 FOR q in C LOOP

IF q.name=name and q.email=email then
    matched_row:='Y';
    dbms_output.put_line(id||name||email||phone||address||q.name||q.email||matched_row);
else 
 IF q.name=name OR q.email=email then  
    matched_row:='Y';
    dbms_output.put_line(id||name||email||phone||address||q.name||q.email||matched_row); 
 else 
   matched_row:='N';
   dbms_output.put_line(id||name||email||phone||address||q.name||q.email||matched_row);
     ENDIF; 
END IF; 

END LOOP; 
END LOOP; 
end proc_test;

当前输出（含冗余行） #

ID  Phone   ADDRESS TABLE_NAME      TABLE1_EMAIL          TABLE2_NAME       TABLE2_EMAIL               MATCHED_ROW
1   xxx     yyy     BRANDON CAFFEE  BCAFFEE@ZZZ.NET       Chandra White     CHANDRAWHITE72@11.COM       Y
1   xxx     yyy     Chandra White   CHANDRAWHITE72@11.COM BRANDON CAFFEE    BCAFFEE@ZZZ.NET             N
1   xxx     yyy     BRANDON CAFFEE  BCAFFEE@ZZZ.NET       BRANDON CAFFEE    BCAFFEE@ZZZ.NET             N
1   xxx     yyy     Chandra White   CHANDRAWHITE72@11.COM Chandra White     CHANDRAWHITE72@11.COM       Y
2   xxx     yyy     Mary Teresa     CLOVEST93@22.COM      Mary Teresa       CLOVEST93@22.COM            Y
2   xxx     yyy     Mary Teresa     CLOVEST93@22.COM                        CLT93@33.NET                N

期望输出（无冗余行） #

ID  Phone   ADDRESS TABLE_NAME      TABLE1_EMAIL          TABLE2_NAME       TABLE2_EMAIL               MATCHED_ROW
1   xxx     yyy     BRANDON CAFFEE  BCAFFEE@ZZZ.NET       Chandra White     CHANDRAWHITE72@11.COM       Y
1   xxx     yyy     Chandra White   CHANDRAWHITE72@11.COM Chandra White     CHANDRAWHITE72@11.COM       Y
2   xxx     yyy     Mary Teresa     CLOVEST93@22.COM      Mary Teresa       CLOVEST93@22.COM            Y
2   xxx     yyy     Mary Teresa     CLOVEST93@22.COM                        CLT93@33.NET                N   

源表数据 #

TABLE1 #

ID FIRST_NAME  LAST_NAME  EMAIL
 1  BRANDON     CAFFEE     BCAFFEE@ZZZ.NET 
 1  Chandra     White      CHANDRAWHITE72@11.COM
 2  Mary        Teresa     CLOVEST93@22.COM

TABLE2 #

ID NAME            EMAIL
 1  BRANDON CAFFEE  BCAFFEE@ZZZ.NET
 1  Chandra White   CHANDRAWHITE72@11.COM
 2  Mary Teresa     CLOVEST93@22.COM
 2                  CLT93@33.NET

解决方案 #

一、修复冗余行问题 #

原嵌套游标会对TABLE1和TABLE2做全量行匹配，导致生成大量无意义的不匹配组合（冗余行）。要得到期望输出，需调整逻辑：以TABLE2的每一行作为基准，匹配TABLE1中符合条件的行，避免全量遍历带来的冗余。

二、替代嵌套游标的高效实现方案 #

嵌套游标属于逐行处理，性能随数据量增长急剧下降。推荐使用SQL集合查询实现需求，数据库对集合操作的优化远优于游标逐行处理，代码更简洁且效率更高。

方案1：优化后的存储过程

CREATE OR REPLACE PROCEDURE proc_test IS
BEGIN
    FOR rec IN (
        -- 匹配TABLE2与TABLE1的有效行
        SELECT 
            t1.id,
            t3.phone,
            t4.address,
            t1.first_name || ' ' || t1.last_name AS table_name,
            t1.email AS table1_email,
            t2.name AS table2_name,
            t2.email AS table2_email,
            CASE
                WHEN t2.name = (t1.first_name || ' ' || t1.last_name) AND t2.email = t1.email THEN 'Y'
                WHEN t2.name = (t1.first_name || ' ' || t1.last_name) OR t2.email = t1.email THEN 'Y'
                ELSE 'N'
            END AS matched_row
        FROM table2 t2
        LEFT JOIN table1 t1 
            ON t2.id = t1.id 
            AND (t2.name = (t1.first_name || ' ' || t1.last_name) OR t2.email = t1.email)
        LEFT JOIN table3 t3 ON t1.id = t3.id
        LEFT JOIN table4 t4 ON t1.id = t4.id
        WHERE t1.id IS NOT NULL
        
        UNION ALL
        
        -- 补充TABLE2中无匹配的行
        SELECT
            t2.id,
            t3.phone,
            t4.address,
            NULL AS table_name,
            NULL AS table1_email,
            t2.name,
            t2.email,
            'N' AS matched_row
        FROM table2 t2
        LEFT JOIN table1 t1 
            ON t2.id = t1.id 
            AND (t2.name = (t1.first_name || ' ' || t1.last_name) OR t2.email = t1.email)
        LEFT JOIN table3 t3 ON t2.id = t3.id
        LEFT JOIN table4 t4 ON t2.id = t4.id
        WHERE t1.id IS NULL
        
        ORDER BY id
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            RPAD(rec.id, 3) || RPAD(rec.phone, 7) || RPAD(rec.address, 7) || 
            RPAD(rec.table_name, 17) || RPAD(rec.table1_email, 23) || 
            RPAD(rec.table2_name, 17) || RPAD(rec.table2_email, 27) || rec.matched_row
        );
    END LOOP;
END proc_test;
/

方案2：直接SQL查询（无需存储过程）

如果不需要封装成存储过程，直接执行以下SQL即可得到期望结果：

SELECT 
    COALESCE(t1.id, t2.id) AS id,
    COALESCE(t3.phone, t3a.phone) AS phone,
    COALESCE(t4.address, t4a.address) AS address,
    t1.first_name || ' ' || t1.last_name AS table_name,
    t1.email AS table1_email,
    t2.name AS table2_name,
    t2.email AS table2_email,
    CASE
        WHEN t2.name = (t1.first_name || ' ' || t1.last_name) AND t2.email = t1.email THEN 'Y'
        WHEN t2.name = (t1.first_name || ' ' || t1.last_name) OR t2.email = t1.email THEN 'Y'
        ELSE 'N'
    END AS matched_row
FROM table2 t2
LEFT JOIN table1 t1 
    ON t2.id = t1.id 
    AND (t2.name = (t1.first_name || ' ' || t1.last_name) OR t2.email = t1.email)
LEFT JOIN table3 t3 ON t1.id = t3.id
LEFT JOIN table4 t4 ON t1.id = t4.id
LEFT JOIN table3 t3a ON t2.id = t3a.id
LEFT JOIN table4 t4a ON t2.id = t4a.id
ORDER BY id;

逻辑说明

1. 用LEFT JOIN关联TABLE2和TABLE1，匹配条件为ID相同且姓名或邮箱匹配，直接通过CASE WHEN标记匹配状态。
2. 用UNION ALL补充TABLE2中完全无匹配的行，确保所有TABLE2的行都被输出。
3. 通过ORDER BY保证输出顺序与期望一致，且无冗余行。

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