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语句。