存储过程-根据一个表对另一个表进行更新

 

编写mysql存储过程，通过查询一个表中数据，更新到另外一个表中

举例来说，存在两张表roledata{role_id,role_score},scoreinfo{role_id,score},

下面存储过程，通过游标，遍历查询scoreinfo表中scroe的值来更新表roledata中的role_scroe值

DROP PROCEDURE IF EXISTS `proc_updatescore`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_updatescore`()
BEGIN
	#Routine body goes here...

	DECLARE _id INT;
	DECLARE flag INT   DEFAULT true;
	DECLARE chk INT;
	DECLARE cur CURSOR FOR SELECT role_id FROM scoreinfo;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = FALSE;
	OPEN cur;
	
	REPEAT
	FETCH cur INTO _id;
	SELECT COUNT(*) INTO chk FROM roledata WHERE roledata.role_id=_id;
	IF chk = 1 THEN
	update roledata,scoreinfo SET roledata.role_score= (SELECT score FROM scoreinfo WHERE role_id=_id) WHERE roledata.role_id=_id;
	END IF;
	UNTIL flag = FALSE
	END REPEAT ;
	CLOSE cur ;
END
;;
DELIMITER ;

　　

 

转载于:https://www.cnblogs.com/bodboy/p/11319907.html