创建存储过程

DELIMITER $$

CREATE
/[DEFINER = { user | CURRENT_USER }]/
PROCEDURE tmsp.tttt()
/LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’/
BEGIN
DECLARE id1 INT DEFAULT 0;//定义参数1
DECLARE id2 INT DEFAULT 0;//定义参数2
//DECLARE id3 VARCHAR(100);//定义参数3
DECLARE done INT DEFAULT -1;//设置值为-1，为后面的循环结束做准备
DECLARE cu CURSOR FOR //定义游标
//循环体
SELECT SUBSTRING_INDEX(GROUP_CONCAT(a.id ORDER BY a.id ASC), ‘,’, 1) AS id1,SUBSTRING_INDEX(GROUP_CONCAT(a.id ORDER BY a.id ASC), ‘,’, -1) AS id2
FROM
(SELECT id,username FROM tbl_users WHERE id>=8871 AND id<=9068
UNION
SELECT id,username FROM tbl_users WHERE username IN (SELECT username FROM tbl_users WHERE id>=8871 AND id<=9068)
) a
GROUP BY a.username;
//循环结束后赋值为1；
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cu;//打开游标
myLoop:LOOP
FETCH cu INTO id1,id2;//将游标的值赋值给循环体的值，游标值和循环体的值一一对应
IF done = 1 THEN
LEAVE myLoop;
END IF;
//执行的函数集合
UPDATE tbl_taskfilling_student SET userId=id1 WHERE userId=id2;
UPDATE tbl_test_groupstudent SET userId=id1 WHERE userId=id2;
DELETE FROM tbl_users WHERE id=id2;
SET done = -1;
END LOOP;
CLOSE cu;
END$$

DELIMITER ;

//调用存储过程
CALL tttt()；