Mysql 游标(加强篇)
今天和沈老师又学到了一招,(*^__^*) 嘻嘻……——游标的更高级的使用
相关字段有glstr , username , rename,pos以图例讲解几者关系:
username:该会员的用户名
prename: 该会员的上级接点会员的用户名,如Bl,Br的prename都是A
pos: 该会员的位置,1=左,2=右
glstr: 树状图所经过的节点,即该会员的所有上级接点,如C1r会员的glstr为 :A,Bl
问题:求取dg_users表中字段username的所有会员的左右两边的人数(左右子节点数);
解析:
1. 查询出所有的username,遍历结果集,
select `username` from `dg_users` ;
2. 比如username为*A*的左右两个直接子节点Y_left,Y_right(注:该会员可能不存在下级,也可能只有一个左子节点,或右子节点)
select `username` ,`pos` from `dg_users` where `prename`='*A*';
(注:这里要判断pos,为1时为左,为2时为右);
3.求Y_left,Y_right两个子节点的子节点数count_L , count_R
select count(id) from `dg_users` where `glstr` likeCONCAT("%,",*A*,",%") orCONCAT("%,",*A*) orCONCAT(*A*,",%");
4. 加法计算,count_L+1,count_R+1就是username为**的左右子节点数了
update `dg_users` set `y_left`=count_L+1,`y_right`=count_R+1 where `username` = '*A*';
5.现在重点来啦!!!我们用前面学到的游标对上面的sql语句进行整理,(要用到两个游标哦,好期待ing~~)
(1)、2,3步骤写为一个游标函数:
```
BEGIN
declare_sibegin_pos VARCHAR(50);
DECLARE_sibegin_username VARCHAR(50);
DECLAREdone INT DEFAULT FALSE;
DECLAREcurCURSOR FORSELECT pos,username FROM jingluovip.dg_users WHERE glstr LIKE CONCAT(sibegin_username,"%");
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO _sibegin_pos,_sibegin_username;
IF done THEN
LEAVE read_loop;
END IF;
# 按用户名称查询数据
IF _sibegin_pos = 1 THEN
# left
SELECT count(glstr) INTO out_left FROM jingluovip.dg_users WHERE glstr LIKE CONCAT("%,",_sibegin_username,",%") OR glstr LIKE CONCAT(_sibegin_username,",%") OR glstr LIKE CONCAT("%,",_sibegin_username);
ELSE
SELECT count(glstr) INTO out_right FROM jingluovip.dg_users WHERE glstr LIKE CONCAT("%,",_sibegin_username,",%") OR glstr LIKE CONCAT(_sibegin_username,",%") OR glstr LIKE CONCAT("%,",_sibegin_username);
END IF;
END LOOP;
CLOSE cur;
END
```
该函数命名为sp_user_jiesuan2
(2)、1,4步骤写为一个游标函数:
BEGIN```
DECLAREsibegin_username VARCHAR(50);
DECLAREsibegin_left INT;
DECLAREsibegin_right INT;
DECLARE done INT DEFAULT FALSE;
-- DECLARE cur CURSOR FOR SELECT `username` FROM jingluovip.dg_users_copy WHERE username = '13507378136';
DECLARE cur CURSOR FOR SELECT `username` FROM jingluovip.dg_users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO sibegin_username;
IF done THEN
LEAVE read_loop;
END IF;
# 按用户名称查询数据
call sp_user_jiesuan2(sibegin_username,@l,@r);
UPDATE jingluovip.dg_users SET jingluovip.dg_users.y_left = @l + 1, jingluovip.dg_users.y_right = @r + 1 WHERE username = sibegin_username;
END LOOP;
CLOSE cur;
END
```
6.结束语:
啦啦啦,怎么样,是不是感觉很高大上(⊙o⊙)? 对沈老师的膜拜又上升了一个高度 O(∩_∩)O哈哈~