mysql判断循环游标是否为空_mysql 存储过程 游标 判断游标是否为空

标签：

BEGIN

DECLARE id long;

DECLARE Done INT DEFAULT 0;

DECLARE cashamount DECIMAL(10,2) DEFAULT 0.00;

DECLARE scorecamount INT DEFAULT 0;

DECLARE userids CURSOR FOR SELECT userid from info_user where isreal = 1;

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET Done = 1;

OPEN userids;

REPEAT

FETCH userids into id;

DELETE FROMinfo_blackWHERE userid=id;

DELETE FROMinfo_consignee WHERE userid=id;

SETcashamount = (SELECT balanceFROM trade_account AS b WHERE b.ownertype = 1 AND b.ownerid = idAND accounttype = 1);

UPDATE trade_account SET balance = balance + cashamount WHERE accountid = 6500000000000000;

SET scorecamount = (SELECT balanceFROM trade_account AS b WHERE b.ownertype = 1 AND b.ownerid = idAND accounttype = 0);

UPDATE trade_account SET balance = balance + scorecamount WHERE accountid = 3500000000000000;

DELETE a,b FROM trade_cashtrade AS a INNER JOIN trade_account AS b WHERE b.ownertype = 1 AND b.ownerid = idAND (a.payerid = b.accountid OR a.payeeid = b.accountid);

DELETE a,b FROM trade_scoretrade AS a INNER JOIN trade_account AS b WHERE b.ownertype = 1 AND b.ownerid = idAND (a.payerid = b.accountid OR a.payeeid = b.accountid);

UNTIL Done END REPEAT;

CLOSE userids;

END

标签：