mysql存储过程使用游标循环插入数据

DELIMITER $$


DROP PROCEDURE IF EXISTS `procedure_course`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `topsale`.`procedure_course`()
    
    BEGIN
    
    DECLARE c_user_login_id VARCHAR(100); 
    
    DECLARE done INT DEFAULT 0; 
    ##1.定义游标stock_cursor   根据指定用户id
    DECLARE stock_cursor CURSOR FOR SELECT ul.USER_LOGIN_ID FROM user_login ul WHERE ul.party_id='999' AND user_login_id='eric.zhang';##获取本部所有用户id
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN stock_cursor;##2.打开游标
    
    cursor_loop: LOOP 
    
    FETCH stock_cursor INTO c_user_login_id; ##3.提取游标
    
    IF done=1 THEN 
          LEAVE cursor_loop; 
    END IF;
    
    CALL procedure_course_mapping(c_user_login_id);
    
    END LOOP cursor_loop; 
    CLOSE stock_cursor; ##关闭游标    
    
    END$$

DELIMITER ;

 

DELIMITER $$

USE `topsale`$$

DROP PROCEDURE IF EXISTS `procedure_course_mapping`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_course_mapping`(v_user_login VARCHAR(30))
BEGIN

    DECLARE c_msg_role_id VARCHAR(50); 
    DECLARE c_count VARCHAR(50); 
    
    DECLARE done INT DEFAULT 0; 
    
    DECLARE stock_cursor CURSOR FOR SELECT t.msg_role_id FROM training_course c,t_msg_text t WHERE c.course_id=t.course_id;##获取所有课件的msg_role_id
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
    
    OPEN stock_cursor;
    
    cursor_loop: LOOP 
        
    FETCH stock_cursor INTO c_msg_role_id;
    IF done=1 THEN 
          LEAVE cursor_loop; 
    END IF;
    
    SELECT COUNT(*) INTO c_count FROM t_msg_mapping WHERE msg_role_id=c_msg_role_id AND user_login_id=v_user_login;
    IF(c_count=0) THEN
    
    INSERT INTO t_msg_mapping(user_login_id,msg_role_id,msg_state) VALUES(v_user_login,c_msg_role_id,0);
    
    END IF;
    
    END LOOP cursor_loop; 
    CLOSE stock_cursor;
    
    END$$

DELIMITER ;

 

SELECT * FROM information_schema.events//获取所有事件

SET GLOBAL event_scheduler = 1;//开启事件状态，1为开始，0为未开启

 

SHOW VARIABLES LIKE 'event_scheduler';//显示事件是否开启

DELIMITER $$

USE `topsale`$$

DROP PROCEDURE IF EXISTS `procedure_course`$$

CREATE DEFINER=`top`@`%` PROCEDURE `procedure_course`()
BEGIN
    
    DECLARE c_user_login_id VARCHAR(100); 
    
    DECLARE done INT DEFAULT 0; 
    ##1.定义游标stock_cursor   根据指定用户id
    DECLARE stock_cursor CURSOR FOR SELECT ul.user_login_id FROM user_role_mapping urm,user_login ul WHERE ul.`USER_LOGIN_ID`=urm.`USER_LOGIN_ID` AND urm.`ROLE_ID` LIKE 'prom%';##获取prom所有用户id
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN stock_cursor;##2.打开游标
    
    cursor_loop: LOOP 
    
    FETCH stock_cursor INTO c_user_login_id; ##3.提取游标
    
    IF done=1 THEN 
          LEAVE cursor_loop; 
    END IF;
    
    CALL procedure_del_msg_user(c_user_login_id);
    
    END LOOP cursor_loop; 
    CLOSE stock_cursor; ##关闭游标    
    
    END$$

DELIMITER ;

DELIMITER $$

USE `topsale`$$

DROP PROCEDURE IF EXISTS `procedure_del_msg_user`$$

CREATE DEFINER=`top`@`%` PROCEDURE `procedure_del_msg_user`(v_user_login VARCHAR(30))
BEGIN
    DECLARE c_msg_role_id VARCHAR(100);
    DECLARE c_count VARCHAR(30);
    
    DECLARE done INT DEFAULT 0; 
    
    DECLARE stock_cursor CURSOR FOR SELECT t.msg_role_id FROM training_course c,t_msg_text t WHERE c.course_id=t.course_id AND c.creater_party_id='999';##获取本部所有课件
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
    
    OPEN stock_cursor;
    
    cursor_loop: LOOP 
        
    FETCH stock_cursor INTO c_msg_role_id;
    
    IF done=1 THEN 
          LEAVE cursor_loop; 
    END IF;

    DELETE FROM t_msg_mapping WHERE msg_role_id=c_msg_role_id AND user_login_id=v_user_login;##删除消息映射表中prom的角色

    
    END LOOP cursor_loop; 
    CLOSE stock_cursor;
    END$$

DELIMITER ;