Begindeclare ID long;declare done int default 0;declare cashamount DECIMAL (10,2) default 0.00;declare scorecamount INT DEF Ault 0;declare userids CURSOR for SELECT userid from info_user where isreal = 1;declare CONTINUE HANDLER for SQLSTATE ' 020 xx ' SET done = 1;open userids; Repeatfetch 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); U PDATE trade_account SET balance = balance + scorecamount WHERE AccountId = 3500000000000000;delete, b from Trade_cashtra De 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);D elete 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
MySQL stored procedure cursor Determines whether the cursor is empty