一个 MYSQL的存储过程 实现基于树型子结点的查询

http://bluewens.blog.163.com/blog/static/6991307200952995322354/

 

 

由于用mysql实现动态光标比较困难，这方面资料很难找得到，因此，只好定义字符串，结合条件来实现。

-- Procedure "showSubInfoList" DDL

CREATE DEFINER=`root`@`localhost ` PROCEDURE `showSubInfoList`(IN rootId INT,IN tablename varchar(50))
BEGIN

       declare      stmt varchar(1000);

       declare str varchar(100);

       drop table tmpLst;

       CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst

        (sno int primary key auto_increment,id int,depth int);

       DELETE FROM tmpLst;

       CALL createSubInfoList(rootId,0,tablename);

       set @strsql = concat('select A.sno, B.*  from tmpLst A,' , tablename ,' B where A.id =B.id order by  A.sno');

       prepare stmt from @strsql ;

       EXECUTE   stmt;

END;
-- Procedure "createSubInfoList" DDL

CREATE DEFINER=`root`@`localhost ` PROCEDURE `createSubInfoList`(IN rootId INT,IN nDepth INT ,IN strTableName varchar(50))
BEGIN

   declare done INT DEFAULT 0;

   declare mId INT;

   declare      stmt varchar(1000);

   declare str varchar(100);

  declare cur1 CURSOR FOR SELECT id FROM  messagetypeinfo where pid= rootId ;

  declare cur2 CURSOR FOR SELECT id FROM  roleinfo where pid= rootId ;

  declare cur3 CURSOR FOR SELECT id FROM  unitinfo where pid= rootId ;

  declare cur4 CURSOR FOR SELECT id FROM  prodtypeinfo where pid= rootId ;

 

  declare CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  set global max_sp_recursion_depth = 255;

  set max_sp_recursion_depth=255;

  CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst

  (sno int primary key auto_increment,id int,depth int);

  insert into tmpLst values (null,rootId,nDepth);

 

  IF  (strTableName = 'messagetypeinfo') then

    OPEN cur1 ;

    FETCH cur1 INTO mId;

      WHILE done=0 DO

           CALL createSubInfoList(mId,nDepth+1, strTableName);

           FETCH cur1 INTO mId;

       END WHILE;

     CLOSE cur1;

   elseif (strTableName = 'roleinfo') then

    OPEN cur2 ;

    FETCH cur2 INTO mId;

      WHILE done=0 DO

           CALL createSubInfoList(mId,nDepth+1, strTableName);

           FETCH cur2 INTO mId;

       END WHILE;

     CLOSE cur2;

   elseif (strTableName = 'unitinfo') then

    OPEN cur3 ;

    FETCH cur3 INTO mId;

      WHILE done=0 DO

           CALL createSubInfoList(mId,nDepth+1, strTableName);

           FETCH cur3 INTO mId;

       END WHILE;

     CLOSE cur3;

   elseif (strTableName = 'prodtypeinfo') then

    OPEN cur4 ;

    FETCH cur4 INTO mId;

      WHILE done=0 DO

           CALL createSubInfoList(mId,nDepth+1, strTableName);

           FETCH cur4 INTO mId;

       END WHILE;

     CLOSE cur4;

   end if;

  END;

调用过程：

call showSubInfoList(2,'messagetypeinfo');