日期:2014-05-16  浏览次数:20790 次

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 ;