mysql的另一种迭代查询方式

 

USE acexeym_packcode;

DROP PROCEDURE IF EXISTS UP_Code_GetByParentCode_test;

CREATE PROCEDURE `UP_Code_GetByParentCode_test`(IN _Code nvarchar(100),
IN _Layer int,
IN _CompanyId bigint)
BEGIN
  DECLARE _sql varchar(4000);
  DECLARE _codes varchar(4000);
  DECLARE _count,roundcount,_lastcodelength int;
  SET _count=1,roundcount=0,_lastcodelength=0;
  SET _codes=CONCAT('''',_Code,'''');


  read_while:
  WHILE  _count>0 DO 
    SET  roundcount=roundcount+1;
    IF roundcount>5 THEN  
        LEAVE read_while;
    END IF;  
    SET @a='',@b='';      
    SET _sql= CONCAT('SELECT title INTO @b FROM (','select  @a:=CONCAT(@a,'','''''',packcode,'''''''') AS title from code_',
    _CompanyId,' WHERE parentcode in(',_codes,') and companyid=? )a ORDER BY title DESC  LIMIT 1');
    SET @sql=_sql;
    SET @companyid=_CompanyId;
    PREPARE sqlstr FROM @sql;
    EXECUTE sqlstr USING @companyid;
   
    IF _lastcodelength=LENGTH(@b) THEN
      SET _count=0;
      LEAVE read_while;
    END IF;
    SET _lastcodelength= LENGTH(@b);

    IF(length(@b)>3) THEN
      if length(@b)>3 THEN
         SET _codes =CONCAT(_codes,',', SUBSTRING(@b,2));
      ELSE  
         SET _codes =SUBSTRING(@b,2);
      END IF;
    ELSE
      LEAVE read_while;
    END IF;
  END WHILE;
 
  SET _sql=CONCAT('SELECT  * FROM code_',_CompanyId,'  WHERE parentcode in(',_codes,') and companyid=?');
  SET @sql=_sql;
  SET @companyid=_CompanyId;
  PREPARE sqlstr FROM @sql;
  EXECUTE sqlstr USING @companyid;
  DEALLOCATE PREPARE sqlstr;

END