Oracle中SELECT调用返回游标函数结果重复的解决方法咨询

问题原因 #

你执行SELECT numOfOrders('john') FROM customer;时，Oracle会遍历Customer表的每一行，对每一行都调用一次numOfOrders函数，所以结果会重复显示，次数等于Customer表的总行数。这不是函数本身的问题，而是调用方式错误导致的。

根源解决方法 #

方法1：使用dual表调用函数

dual是Oracle提供的单行虚表，用它调用函数只会执行一次，返回唯一结果：

SELECT numOfOrders('john') FROM dual;

执行后只会返回一个游标，里面是你需要的统计数据，不会重复。

方法2：修改函数返回类型（如果不需要游标）

如果你的场景不需要返回游标，而是直接返回统计结果，可以修改函数返回单一数值或自定义记录类型，让调用更直观：

示例1：返回订单数量数值

CREATE OR REPLACE FUNCTION numOfOrders(name VARCHAR2) RETURN NUMBER AS
  order_count NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO order_count
    FROM Customer c
   INNER JOIN Orders o
      ON o.O_CustKey = c.C_CustKey
   WHERE C_Name = name
   GROUP BY C_CustKey, C_Name;

  RETURN order_count;
END;
/

调用时用：

SELECT numOfOrders('john') FROM dual;

直接得到10这个结果。

示例2：返回包含多字段的记录

先定义自定义对象类型：

CREATE TYPE OrderCountRecord AS OBJECT (
  C_CustKey VARCHAR2(10),
  C_Name VARCHAR2(50),
  Num_Of_Orders NUMBER
);
/

再修改函数返回该类型：

CREATE OR REPLACE FUNCTION numOfOrders(name VARCHAR2) RETURN OrderCountRecord AS
  result_rec OrderCountRecord;
BEGIN
  SELECT OrderCountRecord(c.C_CustKey, c.C_Name, COUNT(*))
    INTO result_rec
    FROM Customer c
   INNER JOIN Orders o
      ON o.O_CustKey = c.C_CustKey
   WHERE C_Name = name
   GROUP BY C_CustKey, C_Name;

  RETURN result_rec;
END;
/

调用时可以直接获取拆分后的字段：

SELECT rec.C_CustKey, rec.C_Name, rec.Num_Of_Orders
  FROM (SELECT numOfOrders('john') rec FROM dual);

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