Así que he estado intentando hacer este procedimiento simple, pero por alguna razón cuando ejecuto el código en una declaración de llamada, aparece un error que dice "[ERROR in query 1] El resultado consistió en más de una fila". Ejecuté cada una de las declaraciones en el procedimiento individualmente y funcionan perfectamente, devolviendo una fila. Entonces, obviamente, debo tener un error de sintaxis en alguna parte. Si alguien pudiera arrojar luz sobre esto, sería genial.

    DELIMITER ;;

    CREATE PROCEDURE userInfo(in uID int, out fullname varchar(50), out occupation varchar(50), out NumOrders int(11), out DVDCount int(11), out VehicleCount int(11))
    BEGIN
    SELECT concat(firstname, ' ', lastname) INTO fullname FROM users WHERE userid = uID;

    SELECT occupation into occupation FROM occupation JOIN users ON users.occupationid = occupation.occupationid WHERE userid = uID;

    SELECT count(*) INTO NumOrders FROM orderitem
    JOIN orders ON orders.orderid = orderitem.orderid
    JOIN users ON orders.userid = users.userid
    WHERE users.userid = uID;

    SELECT count(*) INTO DVDCount FROM userDVD WHERE userid = uID;

    SELECT count(*) INTO VehicleCount FROM userVehicle WHERE userid = uID;
    END;;
0
the_lonely_doge 28 ene. 2016 a las 04:05

2 respuestas

La mejor respuesta
DELIMITER ;;

CREATE PROCEDURE userInfo(in uID int, out fullname varchar(50), out occupation varchar(50), out NumOrders int(11), out DVDCount int(11), out VehicleCount int(11))
BEGIN
SELECT concat(firstname, ' ', lastname) INTO fullname FROM users WHERE userid = uID LIMIT 1;

SELECT COALESCE(occupation, 'no occupation') into occupation FROM occupation JOIN users ON users.occupationid = occupation.occupationid WHERE userid = uID LIMIT 1;

SELECT count(*) INTO NumOrders FROM orderitem
JOIN orders ON orders.orderid = orderitem.orderid
JOIN users ON orders.userid = users.userid
WHERE users.userid = uID;

SELECT count(*) INTO DVDCount FROM userDVD WHERE userid = uID;

SELECT count(*) INTO VehicleCount FROM userVehicle WHERE userid = uID;
END;;
0
teliaz 2 sep. 2016 a las 12:07

El problema es su out occupation varchar(50), que es una variable de cadena pero está seleccionando varias filas. Debería considerar usar una tabla temporal para el caso (O) simplemente devuelva el conjunto de resultados como

CREATE PROCEDURE userInfo(
            in uID int, 
            out fullname varchar(50), 
            out NumOrders int(11), 
            out DVDCount int(11), out VehicleCount int(11))
BEGIN
SELECT concat(firstname, ' ', lastname) INTO fullname 
FROM users WHERE userid = uID;

SELECT occupation FROM occupation 
JOIN users ON users.occupationid = occupation.occupationid 
WHERE userid = uID; // this query

SELECT count(*) INTO NumOrders FROM orderitem
JOIN orders ON orders.orderid = orderitem.orderid
JOIN users ON orders.userid = users.userid
WHERE users.userid = uID;

SELECT count(*) INTO DVDCount FROM userDVD WHERE userid = uID;

SELECT count(*) INTO VehicleCount FROM userVehicle WHERE userid = uID;
END;
0
Rahul 28 ene. 2016 a las 01:10