PL/SQL查询符合条件订单时报ORA-01422错误，求问题原因

问题描述 #

编写了如下PL/SQL代码，意图通过HAVING子句筛选总金额大于500000、商品数量在10-12之间的订单：

DECLARE
  o_id    Order_Items.Order_ID%TYPE;
  i_id    NUMBER(10);
  qun     Order_Items.Quantity%TYPE;
  u_price Order_Items.Unit_Price%TYPE;
  total   INT;

  CURSOR Orders IS SELECT * FROM Order_Items;    
BEGIN
  total := u_price * qun;
  SELECT Order_Items.order_id, COUNT(item_id) item_count, SUM(unit_price * quantity) total
    INTO o_id, i_id, total
    FROM Order_items
   GROUP BY order_id
  HAVING SUM(unit_price * quantity) > 500000 
     AND COUNT(item_id) BETWEEN 10 AND 12
   ORDER BY total DESC, item_count DESC;

  dbms_output.put_line(LPAD('-', 60, '-'));
  dbms_output.put_line(RPAD('Order ID', 20) || RPAD('Item Count', 20) ||RPAD('Total', 20));
  dbms_output.put_line(LPAD('-', 60, '-'));
  dbms_output.put_line(RPAD(o_id, 20) || RPAD(i_id, 20) || RPAD(total, 20));
  dbms_output.put_line(LPAD('-', 60, '-'));

END;
/

运行时出现错误：ORA-01422: exact fetch returns more than requested number of rows

问题分析 #

1. 核心错误原因：SELECT ... INTO语句要求查询结果只能返回0行或1行，但你的分组查询后，符合总金额>500000且商品数量10-12条件的订单不止一个，返回了多行结果，单个变量无法接收多行数据，因此触发该错误。
2. 冗余无效代码：
   • 声明了游标Orders但全程未使用，属于多余代码。
   • 开头total := u_price * qun;无意义，u_price和qun未初始化赋值，计算结果为空，且后续会被查询语句覆盖。

解决方法 #

要处理多行查询结果，推荐使用游标循环或FOR循环遍历所有符合条件的订单。以下是修正后的代码示例：

DECLARE
  CURSOR valid_orders IS
    SELECT order_id, 
           COUNT(item_id) item_count, 
           SUM(unit_price * quantity) total_amount
      FROM Order_items
     GROUP BY order_id
    HAVING SUM(unit_price * quantity) > 500000 
       AND COUNT(item_id) BETWEEN 10 AND 12
     ORDER BY total_amount DESC, item_count DESC;
BEGIN
  dbms_output.put_line(LPAD('-', 60, '-'));
  dbms_output.put_line(RPAD('Order ID', 20) || RPAD('Item Count', 20) || RPAD('Total Amount', 20));
  dbms_output.put_line(LPAD('-', 60, '-'));
  
  -- 遍历游标输出所有符合条件的订单
  FOR order_rec IN valid_orders LOOP
    dbms_output.put_line(RPAD(order_rec.order_id, 20) || RPAD(order_rec.item_count, 20) || RPAD(order_rec.total_amount, 20));
  END LOOP;
  
  dbms_output.put_line(LPAD('-', 60, '-'));
END;
/

如果确实只需要返回一行结果（比如取总金额最高的订单），可以在查询末尾添加FETCH FIRST 1 ROW ONLY（Oracle 12c及以上）或ROWNUM = 1，但这种场景不符合你的筛选需求。

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