Mysql函数返回

Question

我有一个存储过程:

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertRaffleTicket`(IN userId BIGINT, IN raffleId BIGINT)
BEGIN
    INSERT INTO RaffleTicket(UserId, RaffleId)
    SELECT userId, raffleId
    FROM RaffleStatus rs
    WHERE rs.RaffleId = raffleId
    AND rs.SoldOut = 0;
END

如何将其转换为一个函数，该函数将在成功插入表后返回一个值？

我已经尝试过:

CREATE DEFINER=`root`@`localhost` FUNCTION `addTicket`(UserId int, RaffleId int) RETURNS int(11)
BEGIN
    INSERT INTO RaffleTicket(UserId, RaffleId)
    SELECT userId, raffleId
    FROM RaffleStatus rs
    WHERE rs.RaffleId = raffleId
    AND rs.SoldOut = 0;
    return rs.SoldOut;
RETURN 1;
END

但它在字段列表中给我一个错误 1109 Unkown table 'rs'。

Answer 1

CREATE DEFINER=`root`@`localhost` FUNCTION `InsertRaffleTicket`(
userId BIGINT,
raffleId BIGINT
) RETURNS int
BEGIN

DECLARE retValue int;

select SoldOut into retValue from RaffleStatus where RaffleId = raffleId;

INSERT INTO RaffleTicket(UserId, RaffleId)
SELECT userId, raffleId
FROM RaffleStatus rs
WHERE rs.RaffleId = raffleId
AND rs.SoldOut = 0;

RETURN retValue;

END