调用包含多个游标的存储过程后没有数据返回

Question

我有一个带有以下代码的存储过程。我使用游标的原因是连接表，该表会返回 NULL 值并导致记录消失。通过使用这种方法，我能够获取所有数据而不会丢失任何数据。

现在唯一的问题是，当我尝试调用存储的过程时，它返回 错误代码：1329 没有数据 - 提取、选择或处理零行

，但是当我从 TMOMain 执行手动选择 * 时，会创建表并且其中有数据，但 SignUpCur 和 UnSubCur 没有数据意味着它没有更新。

第一次使用 mysql 存储过程，所以可能会错过一些东西。

我的代码

ROOT:BEGIN

DECLARE pTotal,pShortCode,pSignUp,pUnSub,pJunk,pT INT;
DECLARE pTc NVARCHAR(10);
DECLARE SignTotal,UnSubTotal, JunkTotal INT;
DECLARE pSignTotal,pSignTeamID,pUnSubTotal,pUnSubT,pSignUpS,pUnSubS INT; 
DECLARE pSignTeam,pUnSubTeam NVARCHAR(10);
DECLARE no_more_rows BOOLEAN;

DECLARE MoMainCur CURSOR FOR
SELECT COUNT(*) AS GrandTotal,pShort,(CASE WHEN r= 1 THEN 'A'
WHEN r= 2 THEN 'B' WHEN r= 3 THEN 'C' ELSE 'UV' END) AS Team,recvTeamID
FROM tbli
INNER JOIN tblK ON keywordid = rkey
WHERE recvDate >='2011-11-15'  AND recvDate < '2011-11-16' 
GROUP BY pShort,Team,recvTeamID;

DECLARE SignUpCur CURSOR FOR
SELECT COUNT(*) AS SignUp,(CASE WHEN r= 1 THEN 'A'
WHEN r= 2 THEN 'B' WHEN r= 3 THEN 'C' ELSE 'UV' END) AS Team,
recvTeamID,pShort
FROM tbli INNER JOIN tbl_user ON recvphone = userphone
INNER JOIN tblK ON keywordid = userpublicstatus
WHERE userdatejoined >='2011-11-15' AND userdatejoined < '2011-11-16'
AND recvdate >='2011-11-15' AND recvdate < '2011-11-16'
GROUP BY Team,recvTeamID,pShort;

DECLARE UnSubCur CURSOR FOR
SELECT COUNT(*) AS UnSub,(CASE WHEN r= 1 THEN 'A'
WHEN r= 2 THEN 'B' WHEN r= 3 THEN 'C' ELSE 'UV' END) AS Team,
recvTeamID,pShort
FROM tbliINNER JOIN tbl_user ON recvphone = userphone 
INNER JOIN tblK ON keywordid = userpublicstatus 
WHERE userdateExpire >='2011-11-15' AND userdateExpire <'2011-11-16'
AND recvdate >='2011-11-15' AND recvdate < '2011-11-16'
GROUP BY Team,recvTeamID,pShort;

DROP TABLE IF EXISTS `TMoMain`;
CREATE TEMPORARY TABLE TMOMain
(GrandTotal INT,ShortCode INT,Team NVARCHAR(10),SignUp INT,UnSub INT, Junk INT, TeamID INT);    

OPEN MoMainCur;
-- Main Table
read_loop:LOOP
FETCH MoMainCur INTO pTotal,pShortCode,pTc,pT;

INSERT INTO TMOMain
VALUES
(pTotal,pShortcode,pTc,0,0,0,pT);   
END LOOP read_loop;

CLOSE MoMainCur;

-- Insert Signup Details into Main Table
OPEN SignUpCur;

SignUp_Loop:LOOP
FETCH SignUpCur INTO pSignTotal,pSignTeam,pSignTeamID,pSignUpS;

UPDATE TMOMain 
SET SignUp = pSignTotal
WHERE Team = pSignTeam AND Shortcode =pSignUpS;

END LOOP SignUp_Loop;       

CLOSE SignUpCur;

-- Insert UnSub Details into Main Table
OPEN UnSubCur;

UnSub_Loop:LOOP
FETCH UnSubCur INTO pUnSubTotal,pUnSubTeam,pUnSubT,pUnSubS;

UPDATE TMOMain 
SET UnSub = pSignTotal
WHERE Team = pUnSubTeam AND pShort = pUnSubShortCode;

END LOOP UnSub_Loop;        

CLOSE UnSubCur;

SELECT * FROM TMOMain;
END$$

Answer 1

请尝试一下：

添加此声明一次（在顶部）：

DECLARE curIsDone INT DEFAULT FALSE;

然后在声明光标后添加以下内容：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET curIsDone = TRUE;

在 FETCH 命令之后和您要执行的操作之前：

IF curIsDone THEN
    LEAVE read_loop;
END IF;