mysql存储过程游标 mysql 存储过程游标循环

• 业务需求：通过遍历A表，获得该表所有的主键id，循环为B表插入数据

DROP PROCEDURE IF EXISTS sumVotePro;
delimiter //
CREATE PROCEDURE sumVotePro()
BEGIN
	-- 自定义变量
	DECLARE $shopId int(11);
	DECLARE $now int default unix_timestamp();
	
	
 	-- 遍历数据结束标志
	DECLARE done INT DEFAULT FALSE;
	
	-- 游标
	DECLARE cur_columns CURSOR FOR select id from tab_a;
	
	-- 将结束标志绑定到游标
 	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

	

  	-- 打开游标
  	OPEN cur_columns;

	-- 遍历
	read_loop: LOOP
		
		-- 取值 取多个字段
		FETCH NEXT from cur_columns INTO $shopId;
			IF done THEN
			LEAVE read_loop;
			END IF;
		
			-- 做你自己想做的操作
			insert into tab_b values
			(null, 0, $shopId, $now,null), 
			(null, 0, $shopId, $now,null), 
			(null, 0, $shopId, $now,null), 
			(null, 0, $shopId, $now,null), 
			(null, 0, $shopId, $now,null), 
			(null, 0, $shopId, $now,null), 
			(null, 0, $shopId, $now,null), 
			(null, 0, $shopId, $now,null), 
			(null, 0, $shopId, $now,null), 
			(null, 0, $shopId, $now,null);
		
			
	END LOOP;

	CLOSE cur_columns;

END//
call sumVotePro();