oracle /SQL存储函数ORA-01422和ORA-06512

Question

我想要编写一个PL/SQL存储函数，该函数以司机的雇员号作为参数，并返回司机的全名、他访问过的城市以及他作为嵌套表访问城市的次数。

我编写了这个函数，并成功地编译了它。下面是嵌套表的代码：

    CREATE OR REPLACE TYPE C_V 
AS OBJECT
(   FULLNAME VARCHAR(150),
    CITIES_VISITED VARCHAR(30),
    TOT_VISITS NUMBER(3)
);

CREATE OR REPLACE TYPE D_V_C
IS TABLE OF C_V;

以下是功能：

CREATE OR REPLACE FUNCTION DRIVERVISITEDCITIES ( D_E# NUMBER)
RETURN D_V_C
IS
  D_FULLNAME VARCHAR(150);
  CITIES_VISITED_BY VARCHAR (30);
  TOTAL_VISITS NUMBER(3);

CITY_VIS_DETAIL D_V_C := D_V_C();

BEGIN

  CITY_VIS_DETAIL.EXTEND();
  SELECT DISTINCT EMPLOYEE.FNAME || EMPLOYEE.INITIALS || EMPLOYEE.LNAME AS FULLNAME,
       UPPER(TRIPLEG.DESTINATION),
       COUNT(TRIPLEG.DESTINATION)
  INTO 
        D_FULLNAME,
        CITIES_VISITED_BY,
        TOTAL_VISITS
FROM EMPLOYEE
INNER JOIN DRIVER
ON DRIVER.E# = EMPLOYEE.E#
INNER JOIN TRIP
ON TRIP.L# = DRIVER.L#
INNER JOIN TRIPLEG
ON TRIPLEG.T# = TRIP.T#
WHERE EMPLOYEE.E# = D_E#
GROUP BY EMPLOYEE.FNAME||EMPLOYEE.INITIALS||EMPLOYEE.LNAME, TRIPLEG.DESTINATION
ORDER BY COUNT(TRIPLEG.DESTINATION) DESC;
RETURN CITY_VIS_DETAIL;
END;

但是，当我尝试测试该函数时，它显示：

Error starting at line 1 in command:
SELECT DRIVERVISITEDCITIES(1) FROM DUAL
Error report:
SQL Error: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DRIVERVISITEDCITIES", line 13
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

有人能帮我吗？

Answer 1

您已经定义了一个集合变量，但是没有填充它。而是选择标量变量。显然，您的查询返回多个行(因为一个驱动程序已经进行了多次旅行)，这就是为什么要获得TOO_MANY_ROWS异常的原因。

你需要选择到那个集合中。最简单的方法是批量收集：

SELECT DISTINCT EMPLOYEE.FNAME || EMPLOYEE.INITIALS || EMPLOYEE.LNAME AS FULLNAME,
       UPPER(TRIPLEG.DESTINATION),
       COUNT(TRIPLEG.DESTINATION)
bulk collect into city_vis_detail  -- populate the collection like this
        D_FULLNAME,
        CITIES_VISITED_BY,
        TOTAL_VISITS
FROM EMPLOYEE
INNER JOIN DRIVER
ON DRIVER.E# = EMPLOYEE.E#
INNER JOIN TRIP
ON TRIP.L# = DRIVER.L#
INNER JOIN TRIPLEG
ON TRIPLEG.T# = TRIP.T#
WHERE EMPLOYEE.E# = D_E#
GROUP BY EMPLOYEE.FNAME||EMPLOYEE.INITIALS||EMPLOYEE.LNAME, TRIPLEG.DESTINATION
ORDER BY COUNT(TRIPLEG.DESTINATION) DESC;

Answer 2

  SELECT DISTINCT EMPLOYEE.FNAME || EMPLOYEE.INITIALS || EMPLOYEE.LNAME AS FULLNAME,
       UPPER(TRIPLEG.DESTINATION),
       COUNT(TRIPLEG.DESTINATION)
  INTO 
        D_FULLNAME,
        CITIES_VISITED_BY,
        TOTAL_VISITS
FROM EMPLOYEE

查询将返回多个值，并将其存储到标量变量中。用对象类型变量替换标量变量，并使用批量收集。