mysql存储过程使用游标循环插入数据-白红宇

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

阅读量：4322 次

发布时间：2019-06-06

本文共 3660 字，大约阅读时间需要 12 分钟。

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 ;

转载于:https://www.cnblogs.com/Yusco/p/9584948.html

你可能感兴趣的文章