请问各位大侠mysql游标如何多重循环呢?我的代码如下:
DROP PROCEDURE IF EXISTS `GetUserList`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `GetUserList`(IN sampleId varchar(32))
BEGIN
/* get user list*/
DECLARE groupId varchar(32);
DECLARE logingUser varchar(60);
DECLARE userName varchar(60);
DECLARE userGroup varchar(32);
DECLARE is_group_found integer DEFAULT 1;
DECLARE is_user_found integer DEFAULT 1;
/*declare usergroup cursor*/
DECLARE cus_group CURSOR FOR SELECT user_group_id FROM tbl_user_group_v010
WHERE user_group_id IN (SELECT user_group_id FROM tbl_group_sample_v010
WHERE sample_id = sampleId);
DECLARE cus_user CURSOR FOR SELECT user_name,user_group_id FROM tbl_user_v010;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_group_found = 0;
DELETE FROM tbl_tempuser_v010;
OPEN cus_group;
FETCH cus_group INTO groupId;
WHILE is_group_found = 1 DO
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_user_found = 0;
OPEN cus_user;
FETCH cus_user INTO userName,userGroup;
WHILE is_user_found = 1 DO
IF groupId = userGroup THEN /*find user*/
INSERT INTO tbl_tempuser_v010 SELECT * FROM tbl_user_v010 WHERE user_name IN( SELECT loging_user FROM tbl_session_v010 WHERE loging_user = userName AND client_stat = 1);
END IF;
FETCH cus_user INTO userName,userGroup;
END WHILE;
CLOSE cus_user;
END;
FETCH cus_group INTO groupId;
END WHILE;
CLOSE cus_group;
COMMIT;
END;内层游标只循环一次,不知道为什么,请各位帮忙看下啊。
DROP PROCEDURE IF EXISTS `GetUserList`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `GetUserList`(IN sampleId varchar(32))
BEGIN
/* get user list*/
DECLARE groupId varchar(32);
DECLARE logingUser varchar(60);
DECLARE userName varchar(60);
DECLARE userGroup varchar(32);
DECLARE is_group_found integer DEFAULT 1;
DECLARE is_user_found integer DEFAULT 1;
/*declare usergroup cursor*/
DECLARE cus_group CURSOR FOR SELECT user_group_id FROM tbl_user_group_v010
WHERE user_group_id IN (SELECT user_group_id FROM tbl_group_sample_v010
WHERE sample_id = sampleId);
DECLARE cus_user CURSOR FOR SELECT user_name,user_group_id FROM tbl_user_v010;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_group_found = 0;
DELETE FROM tbl_tempuser_v010;
OPEN cus_group;
FETCH cus_group INTO groupId;
WHILE is_group_found = 1 DO
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_user_found = 0;
OPEN cus_user;
FETCH cus_user INTO userName,userGroup;
WHILE is_user_found = 1 DO
IF groupId = userGroup THEN /*find user*/
INSERT INTO tbl_tempuser_v010 SELECT * FROM tbl_user_v010 WHERE user_name IN( SELECT loging_user FROM tbl_session_v010 WHERE loging_user = userName AND client_stat = 1);
END IF;
FETCH cus_user INTO userName,userGroup;
END WHILE;
CLOSE cus_user;
END;
FETCH cus_group INTO groupId;
END WHILE;
CLOSE cus_group;
COMMIT;
END;内层游标只循环一次,不知道为什么,请各位帮忙看下啊。
解决方案 »
- mysql两表相减问题
- 求:sql如何掌握好的学习方法?
- Mysql怎样得到这样的结果?
- select count(*) from fdp.reuters_rkd_pm; 9分钟,这是怎么回事!
- 我要如何打开一个没有打开的端口呀。如我机器现在没有打开3306,而我现在要用它,也就是要打开3306这个端口,要如何操作呀?(3306是mysql
- 暂时关闭mysql里面 的自增列id
- 急!请教一个mysql查询where语句关键词中空格的问题
- 建立一个账号abc 对test库的test表有DELETE 测试的时候竟然能插入 能查询 求大神给指点
- MySQL查询
- mysql忘了创建database了 脚本执行后建的表去哪了?
- 哪位能帮我看看下面,从维基百科下载的sql文件,为什么在windows下导入全是乱码,我试着改了所有的字符集都不行,但是在linux下面就可以,求解?
- mysql开发书籍 高性能MySQL(第2版)中文版 MySQL必知必会 下载
FETCH cus_group INTO groupId;
CREATE DEFINER=`root`@`%` PROCEDURE `GetUserList`(IN sampleId varchar(32))
BEGIN
/* get user list*/
DECLARE groupId varchar(32);
DECLARE logingUser varchar(60);
DECLARE userName varchar(60);
DECLARE userGroup varchar(32);
DECLARE is_group_found integer DEFAULT 1;
DECLARE is_user_found integer DEFAULT 1;
/*declare usergroup cursor*/
DECLARE cus_group CURSOR FOR SELECT user_group_id FROM tbl_user_group_v010
WHERE user_group_id IN (SELECT user_group_id FROM tbl_group_sample_v010
WHERE sample_id = sampleId);
DECLARE cus_user CURSOR FOR SELECT user_name,user_group_id FROM tbl_user_v010;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_group_found = 0;
DELETE FROM tbl_tempuser_v010;
OPEN cus_group;
FETCH cus_group INTO groupId;
WHILE is_group_found = 1 DO
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_user_found = 0;
OPEN cus_user;
FETCH cus_user INTO userName,userGroup;
WHILE is_user_found = 1 DO
IF groupId = userGroup THEN /*find user*/
INSERT INTO tbl_tempuser_v010 SELECT * FROM tbl_user_v010 WHERE user_name IN( SELECT loging_user FROM tbl_session_v010 WHERE loging_user = userName AND client_stat = 1);
END IF;
FETCH cus_user INTO userName,userGroup;
END WHILE;
CLOSE cus_user;
END;
SET is_group_found = 1;
FETCH cus_group INTO groupId;
END WHILE;
CLOSE cus_group;
COMMIT;
END;
的前面,我这样写可以吗?