用WITH更新语句重构低效PL/SQL游标遇问题求助

问题1：ORA-00928错误修正 #

Oracle的UPDATE语句不能直接引用WITH子句作为数据源，原写法语法不符合规范。改用MERGE语句是处理这类关联更新场景的标准方案：

WITH gexc_cursor AS (      
    SELECT * 
    FROM ( 
        SELECT tmp.*, rownum rn 
        FROM (
            SELECT id, oib_dob AS oib, opis_grupe, smjer  
            FROM GRU_EXC 
            ORDER BY ID 
        ) tmp 
    )
    WHERE rn BETWEEN 1 AND 5 
), 
gd_cursor AS (
    SELECT gc.id, gd.id_grupe 
    FROM GRU_DOK gd 
    JOIN gexc_cursor gc ON gc.opis_grupe = gd.opis_grupe
    WHERE gc.smjer = 'ON' 
      AND gd.id_vlasnika IN ( 
          SELECT id_vlasnika FROM VLAS WHERE oib_vlasnika = gc.oib 
      )
    UNION
    SELECT gc.id, gd.id_grupe 
    FROM GRU_DOK gd 
    JOIN gexc_cursor gc ON gc.opis_grupe = gd.opis_grupe
    WHERE gc.smjer = 'OFF' 
      AND gd.id_vlasnika NOT IN ( 
          SELECT id_vlasnika FROM VLAS WHERE oib_vlasnika = gc.oib 
      )
)
MERGE INTO GRU_EXC ge
USING gd_cursor gc
ON (ge.id = gc.id)
WHEN MATCHED THEN
    UPDATE SET ge.id_grupe = gc.id_grupe;

问题2：更新所有行的修正 #

原写法中，当子查询无匹配结果时会返回NULL，导致UPDATE将所有行的id_grupe设为NULL。添加WHERE EXISTS子句可限制仅更新有匹配结果的行：

UPDATE GRU_EXC ge 
SET id_grupe = (
    WITH gexc_cursor AS (      
        SELECT * 
        FROM ( 
            SELECT tmp.*, rownum rn 
            FROM (
                SELECT id, oib_dob AS oib, opis_grupe, smjer  
                FROM GRU_EXC 
                ORDER BY ID 
            ) tmp 
        )
        WHERE rn BETWEEN 1 AND 5 
    )
    SELECT gd.id_grupe 
    FROM GRU_DOK gd 
    JOIN gexc_cursor gc ON gc.id = ge.id AND gc.opis_grupe = gd.opis_grupe
    WHERE (gc.smjer = 'ON' AND gd.id_vlasnika IN ( 
              SELECT id_vlasnika FROM VLAS WHERE oib_vlasnika = gc.oib 
          ))
       OR (gc.smjer = 'OFF' AND gd.id_vlasnika NOT IN ( 
              SELECT id_vlasnika FROM VLAS WHERE oib_vlasnika = gc.oib 
          ))
)
WHERE EXISTS (
    WITH gexc_cursor AS (      
        SELECT * 
        FROM ( 
            SELECT tmp.*, rownum rn 
            FROM (
                SELECT id, oib_dob AS oib, opis_grupe, smjer  
                FROM GRU_EXC 
                ORDER BY ID 
            ) tmp 
        )
        WHERE rn BETWEEN 1 AND 5 
    )
    SELECT 1 
    FROM GRU_DOK gd 
    JOIN gexc_cursor gc ON gc.id = ge.id AND gc.opis_grupe = gd.opis_grupe
    WHERE (gc.smjer = 'ON' AND gd.id_vlasnika IN ( 
              SELECT id_vlasnika FROM VLAS WHERE oib_vlasnika = gc.oib 
          ))
       OR (gc.smjer = 'OFF' AND gd.id_vlasnika NOT IN ( 
              SELECT id_vlasnika FROM VLAS WHERE oib_vlasnika = gc.oib 
          ))
);

更简洁的优化方案 #

将WITH子句提至UPDATE外层，避免重复编写子查询，逻辑更清晰且性能更优：

WITH gexc_cursor AS (      
    SELECT * 
    FROM ( 
        SELECT tmp.*, rownum rn 
        FROM (
            SELECT id, oib_dob AS oib, opis_grupe, smjer  
            FROM GRU_EXC 
            ORDER BY ID 
        ) tmp 
    )
    WHERE rn BETWEEN 1 AND 5 
), 
gd_cursor AS (
    SELECT gc.id, gd.id_grupe 
    FROM GRU_DOK gd 
    JOIN gexc_cursor gc ON gc.opis_grupe = gd.opis_grupe
    WHERE (gc.smjer = 'ON' AND gd.id_vlasnika IN ( 
              SELECT id_vlasnika FROM VLAS WHERE oib_vlasnika = gc.oib 
          ))
       OR (gc.smjer = 'OFF' AND gd.id_vlasnika NOT IN ( 
              SELECT id_vlasnika FROM VLAS WHERE oib_vlasnika = gc.oib 
          ))
)
UPDATE GRU_EXC ge
SET id_grupe = (SELECT id_grupe FROM gd_cursor gc WHERE gc.id = ge.id)
WHERE EXISTS (SELECT 1 FROM gd_cursor gc WHERE gc.id = ge.id);

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