mysql把游标数据存入表中_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 DEFAULT0;

##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=1THEN

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 DEFAULT0;

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=1THEN

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 ;