PLSQL存储SELECT结果并循环UPDATE遇报错：子查询返回多行

嘿，我来帮你搞定这个报错！你遇到的问题很明确：UPDATE语句里的子查询针对某些IID返回了多条I_NOME记录，而单个E_NOME字段只能接收一个值，所以PL/SQL就抛出了“子查询返回多行”的错误。而且你用循环逐条更新的方式效率其实很低，咱们直接从根源解决问题，同时优化代码。

问题根源分析 #

你原来的代码里，循环每条DATMCCN0的记录，然后用子查询去找对应的I_NOME，但如果DAT_CCNCONFIG0里有多个和当前DATMCCN0.CAPLIC匹配的I_NOME，子查询就会返回多行，这时候没法给E_NOME赋值，自然报错。另外，逐条循环UPDATE的方式在数据量大的时候会很慢，数据库更擅长批量操作。

最优解决方案：直接用关联UPDATE（无需循环） #

咱们可以直接把两张表关联起来做批量更新，这是效率最高的方式，代码也更简洁：

CREATE OR REPLACE PROCEDURE looping 
IS 
BEGIN
  UPDATE DATMCCN0 tgt
  SET E_NOME = (
    SELECT cfg.I_NOME 
    FROM DAT_CCNCONFIG0 cfg
    WHERE cfg.I_NOME = tgt.CAPLIC
  )
  -- 加上这个条件，避免没有匹配记录的行被更新为NULL
  WHERE EXISTS (
    SELECT 1 
    FROM DAT_CCNCONFIG0 cfg
    WHERE cfg.I_NOME = tgt.CAPLIC
  );
END;
/

EXECUTE looping;

说明： #

• 这里用tgt作为DATMCCN0的别名，直接和DAT_CCNCONFIG0关联，数据库会批量处理所有符合条件的行，比循环快得多。
• WHERE EXISTS子句确保只有在DAT_CCNCONFIG0里找到匹配记录的行才会被更新，防止E_NOME被意外设为NULL。

如果子查询确实会返回多行：明确取值规则 #

如果你的业务场景中，DAT_CCNCONFIG0里可能有多个和CAPLIC匹配的I_NOME，那你得明确要取哪一个值，比如取最大值、最小值或者第一条记录：

方法1：用聚合函数取唯一值（比如MAX/MIN） #

CREATE OR REPLACE PROCEDURE looping 
IS 
BEGIN
  UPDATE DATMCCN0 tgt
  SET E_NOME = (
    SELECT MAX(cfg.I_NOME) -- 替换成MIN，或者其他符合你业务的聚合函数
    FROM DAT_CCNCONFIG0 cfg
    WHERE cfg.I_NOME = tgt.CAPLIC
  )
  WHERE EXISTS (
    SELECT 1 
    FROM DAT_CCNCONFIG0 cfg
    WHERE cfg.I_NOME = tgt.CAPLIC
  );
END;
/

方法2：限制返回单行（Oracle 12c及以上支持） #

如果你需要取第一条匹配的记录，可以用FETCH FIRST 1 ROW ONLY：

CREATE OR REPLACE PROCEDURE looping 
IS 
BEGIN
  UPDATE DATMCCN0 tgt
  SET E_NOME = (
    SELECT cfg.I_NOME 
    FROM DAT_CCNCONFIG0 cfg
    WHERE cfg.I_NOME = tgt.CAPLIC
    FETCH FIRST 1 ROW ONLY -- 只取第一条
  )
  WHERE EXISTS (
    SELECT 1 
    FROM DAT_CCNCONFIG0 cfg
    WHERE cfg.I_NOME = tgt.CAPLIC
  );
END;
/

如果你坚持要用数组+循环的方式（不推荐，效率低） #

如果业务上必须用数组存储数据再循环更新，那可以用BULK COLLECT一次性把数据读到集合里，避免逐条查询的低效：

CREATE OR REPLACE PROCEDURE looping 
IS
  -- 定义记录类型，存储IID和对应的I_NOME
  TYPE rec_type IS RECORD (
    v_iid DATMCCN0.IID%TYPE,
    v_nome DAT_CCNCONFIG0.I_NOME%TYPE
  );
  -- 定义集合类型，存储多个rec_type记录
  TYPE rec_table IS TABLE OF rec_type;
  v_recs rec_table;
BEGIN
  -- 批量查询数据，确保每个IID对应唯一的I_NOME（这里用GROUP BY去重）
  SELECT tgt.IID, cfg.I_NOME
  BULK COLLECT INTO v_recs
  FROM DATMCCN0 tgt
  JOIN DAT_CCNCONFIG0 cfg ON cfg.I_NOME = tgt.CAPLIC
  GROUP BY tgt.IID, cfg.I_NOME;

  -- 循环集合更新每行数据
  FOR i IN v_recs.FIRST .. v_recs.LAST LOOP
    UPDATE DATMCCN0
    SET E_NOME = v_recs(i).v_nome
    WHERE IID = v_recs(i).v_iid;
  END LOOP;
END;
/

EXECUTE looping;

说明： #

• BULK COLLECT一次性把所有符合条件的数据读到集合里，比循环里逐条查询快很多，但还是不如直接关联UPDATE高效。
• 用GROUP BY确保每个IID只对应一个I_NOME，避免集合里出现重复IID导致重复更新。

内容的提问来源于stack exchange，提问作者José Nobre