如何解决ORA-01422错误：精确提取返回行数超出请求数量

错误原因 #

ORA-01422的本质是SELECT INTO语句返回了多行数据，但你声明的单个变量（p_id、p_email等）只能存储单条记录的值，无法容纳多行结果，因此触发报错。你的查询中where status = 'draft'匹配到了多条记录，才会出现这个问题。

修复方案分两种场景 #

场景1：只需要处理单条draft记录（比如最新的一条或任意一条） #

在查询语句中添加行限制，确保只返回一行数据。可以用ROWNUM = 1或者FETCH FIRST 1 ROW ONLY（Oracle 12c及以上版本支持）：

修改后的代码示例：

DECLARE  
    p_id number;  
    p_email varchar2(500); 
    p_amount number; 
    p_fees number;
    p_reference varchar2(500);
BEGIN
 SELECT
    p.id,
    jt.email, 
    jt.amount, 
    jt.fees,
    jt.pr_reference
  INTO
    p_id,
    p_email, 
    p_amount, 
    p_fees,
    p_reference
 FROM paygate_payload p,  
      JSON_TABLE(
         p.payload,'$'
         COLUMNS (
             email varchar2(500) path '$.details.customer_email',
             amount varchar2(500) path '$.details.data.amount',
             fees varchar2(500) path '$.details.data.chargeamount',
             pr_reference varchar2(500) path '$.details.data.paymentreference'
       )
      ) jt
 WHERE p.status = 'draft'
 -- 添加行限制，确保只取一行
 AND ROWNUM = 1; 
 -- 或者Oracle 12+版本可替换为：
 -- FETCH FIRST 1 ROW ONLY;

 update paygate_payload set status = 'done' where id = p_id;
 pgt_app.register_paygate_event(p_email, p_amount, p_fees, p_reference);
END;
/

场景2：需要处理所有draft记录 #

如果要批量处理所有状态为draft的记录，不能用单个变量存储，需要用游标循环遍历每一条记录，逐条执行更新和存储过程调用：

修改后的代码示例：

BEGIN
 -- 用FOR循环遍历所有draft记录
 FOR rec IN (
     SELECT
        p.id,
        jt.email, 
        jt.amount, 
        jt.fees,
        jt.pr_reference
     FROM paygate_payload p,  
          JSON_TABLE(
             p.payload,'$'
             COLUMNS (
                 email varchar2(500) path '$.details.customer_email',
                 amount varchar2(500) path '$.details.data.amount',
                 fees varchar2(500) path '$.details.data.chargeamount',
                 pr_reference varchar2(500) path '$.details.data.paymentreference'
           )
          ) jt
     WHERE p.status = 'draft'
 ) LOOP
     -- 更新当前记录的状态
     update paygate_payload set status = 'done' where id = rec.id;
     -- 调用存储过程
     pgt_app.register_paygate_event(rec.email, rec.amount, rec.fees, rec.pr_reference);
 END LOOP;
 COMMIT; -- 批量处理后记得提交事务
END;
/

额外注意点 #

1. 原代码中JSON_TABLE的amount和fees列定义为varchar2，但变量p_amount、p_fees是number类型，可能存在隐式转换问题，建议在JSON_TABLE中直接转成number：

   amount number path '$.details.data.amount',
   fees number path '$.details.data.chargeamount'
   

2. 批量处理时，添加事务控制（COMMIT）可以避免未提交的事务占用数据库资源。

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