MySQL错误码1242：子查询返回多行及存储过程执行异常求助

Hey there, let's break down what's causing that error and fix it up for you.

First off, the root cause is right in your first IF condition. The subquery (select situacaoPol from Poltrona where Poltrona.id_Oni_fk=id_Oni_fk) returns multiple rows when there are multiple seats (Poltrona) linked to the same id_Oni_fk. Since an IF statement can only evaluate a single scalar value, MySQL throws error 1242 when it gets multiple results here.

Looking at your error message Existem poltronas ocupadas, I assume your intended logic is: only allow inserting a Trecho_Viagem if all seats for the given id_Oni_fk are marked as 'LIVRE'. Let's adjust the procedure to handle that correctly, plus fix a few other small issues I noticed.

Revised Stored Procedure Code #

delimiter $$ 
create procedure Ex_XII(
    data_partidaTre date,
    data_chegadaTre date,
    horario_partidaTre time,
    horario_chegadaTre time,
    distanciaTre varchar(200), 
    id_CidOri_fk int, 
    id_CidDes_fk int, 
    id_Oni_fk int
) 
begin 
    DECLARE valor double;
    
    -- Fix: Check if NO seats are occupied for this id_Oni_fk (avoids multi-row subquery)
    IF NOT EXISTS (SELECT 1 FROM Poltrona WHERE id_Oni_fk = id_Oni_fk AND situacaoPol != 'LIVRE') THEN
        IF id_CidOri_fk != id_CidDes_fk THEN
            IF data_chegadaTre >= data_partidaTre THEN
                -- Fix: Convert distanciaTre to numeric to avoid string comparison bugs
                SET @distancia_num = CAST(distanciaTre AS UNSIGNED);
                
                IF @distancia_num > 0 AND @distancia_num < 50 THEN
                    SET valor = 30;
                    INSERT INTO Trecho_Viagem 
                    VALUES(null, data_partidaTre, data_chegadaTre, horario_partidaTre, horario_chegadaTre, distanciaTre, valor, id_CidOri_fk, id_CidDes_fk, id_Oni_fk);
                ELSEIF @distancia_num >= 50 AND @distancia_num < 200 THEN
                    SET valor = 50;
                    -- Fix: Add missing INSERT statement (you had only set valor here before)
                    INSERT INTO Trecho_Viagem 
                    VALUES(null, data_partidaTre, data_chegadaTre, horario_partidaTre, horario_chegadaTre, distanciaTre, valor, id_CidOri_fk, id_CidDes_fk, id_Oni_fk);
                ELSE
                    SET valor = 100;
                    -- Fix: Add missing INSERT statement here too
                    INSERT INTO Trecho_Viagem 
                    VALUES(null, data_partidaTre, data_chegadaTre, horario_partidaTre, horario_chegadaTre, distanciaTre, valor, id_CidOri_fk, id_CidDes_fk, id_Oni_fk);
                END IF;
            ELSE
                SELECT 'Isto não é uma máquina do tempo' AS feedback;
            END IF;
        ELSE
            SELECT 'Você já está nesta cidade' AS feedback;
        END IF;
    ELSE
        SELECT 'Existem poltronas ocupadas' AS feedback;
    END IF;
end; 
$$ 
delimiter ;

-- Test call (updated origin city to 2 to avoid the "same city" error)
call Ex_XII('2000/12/12','2000/12/17','12:12:12','12:12:12','51',1,2,1);

Key Fixes & Improvements #

• Eliminated the multi-row subquery issue:
  We replaced the problematic subquery with NOT EXISTS (SELECT 1 FROM Poltrona WHERE id_Oni_fk = id_Oni_fk AND situacaoPol != 'LIVRE'). This checks if there are no occupied seats for the given bus/vehicle, which aligns with your error message intent. It returns a single boolean value, so no more error 1242.

• Fixed string comparison bugs:
  Since distanciaTre is a varchar type, comparing it directly as a number (like distanciaTre>0) can lead to unexpected results (e.g., the string '10' would be considered smaller than '5'). We convert it to an unsigned integer with CAST(distanciaTre AS UNSIGNED) first.

• Added missing INSERT statements:
  In your original code, only the first distance range (0-50) had an INSERT call. The other two ranges just set valor but didn't actually insert the row into Trecho_Viagem—we fixed that.

• Improved feedback readability:
  Added an alias feedback to the status messages so your query results are clearer.

If your actual logic was supposed to be "allow insertion if at least one seat is free" instead of all seats, just swap the condition to:

IF EXISTS (SELECT 1 FROM Poltrona WHERE id_Oni_fk = id_Oni_fk AND situacaoPol = 'LIVRE') THEN

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