mysql 存储过程 嵌套查询_mysql 存储过程和 之间的嵌套

DELIMITER $$

USE `mydatabase`$$

DROP PROCEDURE IF EXISTS `updateA`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `updateA`()

BEGIN

DECLARE id INT ;

DECLARE STOP INT DEFAULT 0;

DECLARE cur CURSOR FOR SELECT a.id FROM a ;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;

OPEN cur;

FETCH cur INTO id ;

WHILE STOP <> 1 DO

-- 各种判断

CALL myProcs(id);

-- 读取下一行的数据

FETCH cur INTO id ;

END WHILE; -- 循环结束

CLOSE cur; -- 关闭游标

END$$

DELIMITER ;

DELIMITER $$

USE `mydatabase`$$

DROP PROCEDURE IF EXISTS `myProcs`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `myProcs`(IN MAIN_ID INT)

BEGIN

DECLARE ID INT ;

DECLARE brandId VARCHAR(255);

DECLARE l_find_pos INT;

DECLARE l_new_string VARCHAR(255);

DECLARE STOP INT DEFAULT 0;

DECLARE cur CURSOR FOR SELECT a.id ,aitem.brandId FROM a LEFT JOIN aitem ON a.id=aitem.aId WHERE a.id=MAIN_ID ;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;

SET l_new_string='';

OPEN cur;

FETCH cur INTO id,brandId;

WHILE STOP <> 1 DO

-- 各种判断

SET l_find_pos=INSTR(l_new_string,brandId);

IF (l_find_pos=0) THEN

SET l_new_string = CONCAT(brandId,',',l_new_string);

END IF;

-- 读取下一行的数据

FETCH cur INTO id,brandId;

END WHILE; -- 循环结束

CLOSE cur; -- 关闭游标

#SET ttt=l_new_string;

IF (LENGTH (l_new_string)>0) THEN

SET l_new_string = LEFT(l_new_string,LENGTH (l_new_string)-1);

END IF;

UPDATE a SET brandId = l_new_string WHERE a.id=MAIN_ID ;

END$$

DELIMITER ;