mysql存储过程+游标循环遍历 判断 赋值 等实例二

1.接着上一篇，本次的存储过程场景和功能和上一篇（放一下上一篇的连接http://blog.csdn.net/ice166/article/details/78830905）的类似，唯一的区别就是本次修改的是2个字段，而且逻辑也有点变化，因为推荐人是有关系等级的，大致为1,2,3,4 四类，第一篇的是没有等级要求的，这个不能隔代推荐，只能是1推荐成为2,2推荐成为3，以此类推，如果是4推荐的话那么推荐的类别还是4，本次维护的是2个字段，推荐关系链和parent_id,如果用户的推荐人的类别是3以上，那么该用户的parent_id就是推荐人的uid，如果用户的推荐人的类别是4，那么用户的parent_id就是该用户推荐人的parent_id。sql如下

修改推荐关系链的函数

CREATE DEFINER=`mall`@`%` FUNCTION `getInviteRelationStr`(`parUid` int) RETURNS varchar(3000) CHARSET utf8
BEGIN
	DECLARE inviteRelationStr VARCHAR(3000);
	DECLARE iUid INT;
	DECLARE iStr INT;

	SET inviteRelationStr = '';

	SELECT invite_id INTO iUid FROM t_members WHERE uid = parUid;
	SET iStr = iUid;
	
	WHILE iUid IS NOT NULL OR iUid != '' DO
		SET inviteRelationStr = CONCAT(iUid,'-',inviteRelationStr);
		SELECT invite_id INTO iUid FROM t_members WHERE uid = iUid;
	END WHILE;

	RETURN inviteRelationStr;
END

主体存储过程，在该过程中会调用上面的函数

CREATE DEFINER=`mall`@`%` PROCEDURE `updateTMembersData`()
BEGIN
	DECLARE uType INT;
	DECLARE tInviteId INT;
	DECLARE tParentId INT;
	DECLARE tUid INT;
	DECLARE myUType INT;
	DECLARE paramUid INT;
	-- 创建自定义控制游标循环变量
	DECLARE done TINYINT DEFAULT FALSE;
	-- 创建自定义游标 并输入结果集
	DECLARE my_cursor CURSOR FOR (SELECT uid FROM t_members);
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	-- 打开游标
	OPEN my_cursor;
	
	-- 循环开始
	my_loop: LOOP
		FETCH NEXT FROM my_cursor INTO paramUid;

			IF done THEN
				LEAVE my_loop;
			END IF;
	
			SELECT user_type INTO myUType FROM t_members WHERE uid = paramUid;
			SELECT invite_id INTO tInviteId FROM t_members WHERE uid = paramUid;
			SELECT parent_id INTO tParentId FROM t_members WHERE uid = tInviteId;
			SELECT uid INTO tUid FROM t_members WHERE uid = tInviteId;
			SELECT user_type INTO uType FROM t_members WHERE uid = tInviteId;

			-- 调用自定义函数 修正 推荐关系链数据
			UPDATE t_members SET invite_relation = (SELECT getInviteRelationStr(paramUid)) WHERE uid = paramUid;
			
			-- 如果是门店及以上，那么parent_id就是该推荐人uid，
			-- 如果为普通用户，parent_id为推荐人的parent_id
			IF myUType != 1 THEN
				IF uType = 4 THEN
						UPDATE t_members SET parent_id = tParentId WHERE uid = paramUid;
				ELSE
						UPDATE t_members SET parent_id = tUid WHERE uid = paramUid;
				END IF;
			ELSE
				UPDATE t_members SET parent_id = NULL,invite_id = NULL,invite_relation = '' WHERE uid = paramUid;
			END IF;
			
			-- 数据全部置空
			SET myUType = null;
			SET tInviteId = null;
			SET tParentId = null;
			SET tUid = null;
			SET uType = null;

	END LOOP my_loop;
	CLOSE my_cursor;	-- 关闭游标

END