Mysql游标小记
CREATE DEFINER=`root`@`%` PROCEDURE `createMenuByrole`() BEGIN DECLARE done INT default false; DECLARE rid1 INT default 0; DECLARE total INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT rid FROM ut_role WHERE type = 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;# 打开定义的游标 OPEN cur;# 获取下一行数据 FETCH next from cur INTO rid1;# 遍历处理 WHILE not done DO SET total = total + 1;# 获取下一行数据 insert into abc(rid,mid,PERMISSION_ACTION) select rid1,mid,PERMISSION_ACTION from ut_permission_role where rid=396; #FETCH cur INTO rid1; fetch next from cur into rid1; END WHILE;# 关闭释放游标 CLOSE cur; SELECT total; END
小记:
1.声明变量时,要带着默认值,否则默认为null,一不留神后面判断时容易写错。
2.FETCH into 语句 = fetch next from 语句。
3.在while之前和内部都要添加fetch语句。