Oracle SQL循环计算账户余额遇ORA-01422错误求助

问题描述 #

编写PL/SQL For循环计算账户总余额时，触发错误：ORA_01422: exact fetch returns more than requested number of row。原脚本如下：

DECLARE
v_area          VARCHAR2(3);
v_account_no   VARCHAR2(20);
v_cur        VARCHAR2(3);
v_local_bal    NUMBER;
v_fo_bal    NUMBER;

BEGIN
  FOR y IN (SELECT area,cur,account_no FROM accounts 
    WHERE account_no IN ('1123456879','2222222222','3333333333','4444444444','5555555555','6666666666','7777777777'))
    LOOP
      SELECT area,accno,cur,SUM(DECODE(drcr, 'C', local_amt, -local_amt)) local_bal,NVL(SUM(DECODE(drcr, 'C', fo_amt, -fo_amt)), 0) fo_bal
      INTO v_area, v_account_no, v_cur, v_local_bal, v_fo_bal
      FROM transactions
      WHERE acc_number = y.account_no
      AND transdate <= '29Dec2023'
      AND area = '234'
      GROUP BY area, acc_number, cur;

    -- Display individual account balances
    DBMS_OUTPUT.PUT_LINE('Account: ' || y.account_no|| ', Area: ' || v_area || ', Curr: ' || v_cur);
    DBMS_OUTPUT.PUT_LINE('Local Balance: ' || v_local_bal);
    DBMS_OUTPUT.PUT_LINE('FO Balance: ' || v_fo_bal);

   
END LOOP;

END;/

期望输出：

Account: 1123456879, Area: 234 , Curr: XYX, Local Balance: 2000 FO Balance: 500
Account: 2222222222, Area: 234 , Curr: XXX, Local Balance: 1000 FO Balance: 0
Account: 3333333333, Area: 234 , Curr: XXX, Local Balance: 1000 FO Balance: 0

错误原因 #

ORA-01422错误的核心是：SELECT ... INTO语句要求仅返回单行结果，但你的循环内查询中，同一个账户可能对应多种货币（cur），GROUP BY area, acc_number, cur后会生成多行数据，无法存入单个变量组中。

修复方案 #

根据期望输出的维度（账户+货币），直接关联两张表并分组查询，避免嵌套查询的单行限制。修改后的脚本如下：

DECLARE
BEGIN
  -- 关联账户表与交易表，按账户+货币分组统计
  FOR rec IN (
    SELECT 
      a.account_no,
      t.area,
      t.cur,
      SUM(DECODE(t.drcr, 'C', t.local_amt, -t.local_amt)) AS local_bal,
      NVL(SUM(DECODE(t.drcr, 'C', t.fo_amt, -t.fo_amt)), 0) AS fo_bal
    FROM accounts a
    LEFT JOIN transactions t 
      ON a.account_no = t.acc_number
      AND t.transdate <= TO_DATE('29Dec2023', 'DDMonYYYY')
      AND t.area = '234'
    WHERE a.account_no IN ('1123456879','2222222222','3333333333','4444444444','5555555555','6666666666','7777777777')
    GROUP BY a.account_no, t.area, t.cur
    ORDER BY a.account_no
  ) LOOP
    -- 输出每个账户+货币维度的余额
    DBMS_OUTPUT.PUT_LINE('Account: ' || rec.account_no || ', Area: ' || rec.area || ', Curr: ' || rec.cur || ', Local Balance: ' || rec.local_bal || ' FO Balance: ' || rec.fo_bal);
  END LOOP;
END;
/

关键修改点 #

• 用LEFT JOIN关联accounts和transactions，一次性获取所有需要统计的分组数据，避免嵌套查询的单行限制
• 将日期字符串转换为TO_DATE类型，避免隐式转换导致的潜在错误
• 直接遍历分组后的结果集，每个分组对应一行输出，完全匹配期望格式
• 保留LEFT JOIN确保无交易记录的账户也能被输出（余额为0）

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