oracle PL/SQL 分组依据 - ORA-01422：精确提取返回的行数超过请求的行数

PL/SQL Group By - ORA-01422: exact fetch returns more than requested number of rows

提问by Splunk

I am writing the following query that I want to display car registration, car group name, model name, cost and the number of bookings for each car. I have to use an explicit cursor and I have to use an implicit cursor to calculate the number of bookings that belong to each car.

我正在编写以下查询，我想显示汽车注册、汽车组名称、型号名称、成本和每辆车的预订数量。我必须使用显式游标，我必须使用隐式游标来计算属于每辆车的预订数量。

My query is as follows:

我的查询如下：

    SET SERVEROUTPUT ON FORMAT WRAP SIZE 12000
Declare
v_count number;
cursor carcur IS
SELECT * FROM i_car;
v_car carcur%ROWTYPE;
Begin
Select COUNT (registration)
INTO v_count
from i_booking
group by registration;
FOR v_car IN carcur LOOP
DBMS_OUTPUT.PUT_LINE('Registration:'|| '  '|| v_car.registration);
DBMS_OUTPUT.PUT_LINE('Car Group:'|| ' ' ||v_car.car_group_name);
DBMS_OUTPUT.PUT_LINE('Model Name:'|| ' '||v_car.model_name);
DBMS_OUTPUT.PUT_LINE('Cost:'|| ' '||v_car.cost);
DBMS_OUTPUT.PUT_LINE('Total Bookings:'|| ' '||v_count);
DBMS_OUTPUT.NEW_LINE;

END LOOP;
End;

The output I am getting is as follows: Declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 7

我得到的输出如下：在第 1 行声明 * 错误：ORA-01422：精确获取返回超过请求的行数 ORA-06512：在第 7 行

I am sure it has something to do with the return values being put into the variable, but I have no idea how to rectify this.

我确信这与将返回值放入变量有关，但我不知道如何纠正这一点。

Any advice would be greatly appreciated.

任何建议将不胜感激。

Many thanks.

非常感谢。

采纳答案by Splunk

This did the trick for me. I moved my implicit SELECT statement into the cursor for loop and added a WHERE clause saying WHERE registration = v_car.registration;

这对我有用。我将隐式 SELECT 语句移到游标 for 循环中，并添加了一个 WHERE 子句，说明 WHERE registration = v_car.registration;

SET SERVEROUTPUT ON FORMAT WRAP SIZE 12000
    Declare
    v_count number;
    cursor carcur IS
    SELECT * FROM i_car;
    v_car carcur%ROWTYPE;
    Begin
    FOR v_car IN carcur LOOP
    Select COUNT (registration)
    INTO v_count
    from i_booking
    WHERE registration = v_car.registration;
    DBMS_OUTPUT.PUT_LINE('Registration:'|| '  '|| v_car.registration);
    DBMS_OUTPUT.PUT_LINE('Car Group:'|| ' ' ||v_car.car_group_name);
    DBMS_OUTPUT.PUT_LINE('Model Name:'|| ' '||v_car.model_name);
    DBMS_OUTPUT.PUT_LINE('Cost:'|| ' '||v_car.cost);
    DBMS_OUTPUT.PUT_LINE('Total Bookings:'|| ' '||v_count);
    DBMS_OUTPUT.NEW_LINE;
    END LOOP;
    End;

Many thanks to everyone for you assistance.

非常感谢大家的帮助。

回答by Rachcha

The error you are facing is because you are trying to assign multiple values to a single valued variable.

您面临的错误是因为您试图将多个值分配给一个单值变量。

Your query is returning multiple values primarily because you are using a group by. What a group bydoes is, in your case, finds the total count of values in column registrationfor every distinct value in that column.

您的查询返回多个值主要是因为您使用的是group by. group by在您的情况下，a 的作用是查找该列registration中每个不同值的列中值的总数。

Suppose, let's see an example-

假设，让我们看一个例子——

registration | other columns ...
1            | ...
1            | ...
1            | ...
2            | ...
3            | ...
3            | ...

Hence the output of your query with the group bywould be

因此，您的查询的输出group by将是

registration | count(registration)
1            | 3
2            | 1
3            | 2

Note that the column registrationis not selected in your selectlist, only count(registration)is selected, which according to the example can contain multiple values.

请注意，该列registration未在您的select列表count(registration)中选择，只是被选中，根据示例可以包含多个值。

Hence now there are three cases now-

所以现在分三种情况——

1. If the above is your desired output, clieu's answer above is useful.

2. If you want to get just the count of all the non-null values in the column registration, just remove the group byclause and you'll be fine.

3. If you want the count of all the DISTINCTnon-null values in the column registration, you can use count(distinct registration)and remove group byas follows:

   SET SERVEROUTPUT ON FORMAT WRAP SIZE 12000
   Declare
   v_count number;
   cursor carcur IS
   SELECT * FROM i_car;
   v_car carcur%ROWTYPE;
   Begin
   Select COUNT (DISTINCT registration)
   INTO v_count
   from i_booking;
   FOR v_car IN carcur LOOP
   DBMS_OUTPUT.PUT_LINE('Registration:'|| '  '|| v_car.registration);
   DBMS_OUTPUT.PUT_LINE('Car Group:'|| ' ' ||v_car.car_group_name);
   DBMS_OUTPUT.PUT_LINE('Model Name:'|| ' '||v_car.model_name);
   DBMS_OUTPUT.PUT_LINE('Cost:'|| ' '||v_car.cost);
   DBMS_OUTPUT.PUT_LINE('Total Bookings:'|| ' '||v_count);
   DBMS_OUTPUT.NEW_LINE;
   
   END LOOP;
   End;
   /
   

1. 如果以上是您想要的输出，则上述 clieu 的回答很有用。

2. 如果您只想获取列中所有非空值的计数registration，只需删除该group by子句即可。

3. 如果你想要列中所有DISTINCT非空值的计数registration，你可以使用count(distinct registration)和删除group by如下：

   SET SERVEROUTPUT ON FORMAT WRAP SIZE 12000
   Declare
   v_count number;
   cursor carcur IS
   SELECT * FROM i_car;
   v_car carcur%ROWTYPE;
   Begin
   Select COUNT (DISTINCT registration)
   INTO v_count
   from i_booking;
   FOR v_car IN carcur LOOP
   DBMS_OUTPUT.PUT_LINE('Registration:'|| '  '|| v_car.registration);
   DBMS_OUTPUT.PUT_LINE('Car Group:'|| ' ' ||v_car.car_group_name);
   DBMS_OUTPUT.PUT_LINE('Model Name:'|| ' '||v_car.model_name);
   DBMS_OUTPUT.PUT_LINE('Cost:'|| ' '||v_car.cost);
   DBMS_OUTPUT.PUT_LINE('Total Bookings:'|| ' '||v_count);
   DBMS_OUTPUT.NEW_LINE;
   
   END LOOP;
   End;
   /
   

回答by clieu

The error occurs when more than one value is returned from the select count(registration) since you are selecting it into a single variable v_count.

当从 select count(registration) 返回多个值时会发生错误，因为您将它选择到单个变量 v_count 中。

If you are trying to select all of the counts for the various registrations, you'll want to use an array type for v_count, like this:

如果您尝试选择各种注册的所有计数，您需要为 v_count 使用数组类型，如下所示：

declare
v_count number;
v_counts is table of v_count

Begin
    Select COUNT (registration)
    bulk collect INTO v_counts
    from i_booking
group by registration;

if (v_counts > 0) then
   for i in v_counts.first..last loop
    <do your printing>
   end loop;
end if;
end;
/

I also added in an optimization that uses a bulk collect since that will give you slightly faster performance.

我还添加了一个使用批量收集的优化，因为这会给你稍微快一点的性能。

回答by shirin

the error is in the group byclause. You are getting multiple rows for count(registration)because you have used group byon it. The temporary solution for now is to remove the group byclause.

错误在group by子句中。count(registration)因为你已经使用group by过它，所以你得到了多行。现在的临时解决方案是删除该group by子句。