mysql存储过程

关于存储过程的设计以及调用。

本次需要实现：根据传入的id（可能是学校也可能是城市）来找到这个id底下对应的所有专业。城市与专业之间存在的层级是不确定的。

因为城市学校专业都是在一张表中，存在pid的关系，所以这是一个递归。从传入的id开始不断查找直到专业为止。

mysql中有限制，在存储过程中不能使用递归。不能自身调用自身 。

所以，关于这一点的解决办法采用了，使用两个存储过程。一个用于查找传入id的下级节点，另一个用于判断返回的节点是不是专业id,如果不是的就继续调用前一个存储过程。直到所有专业节点都输出为止。

关于具体实现

在这个过程中使用了游标。取得查询结果的每一条结果进行判断。输出的结果分成专业id和非专业id。

使用游标时，会出现循环次数多一次的情况。这个时候的解决方法是加入两次判断。一次放在循环之前，一次放在取值之后。

CREATE DEFINER = 'zpt'@'%'PROCEDURE xz.procedure1(IN ids varchar(255), OUT pids varchar(255), OUT mids varchar(255))BEGIN  DECLARE nos int DEFAULT 0;  DECLARE done int DEFAULT FALSE;  DECLARE counter int;  DECLARE idtmp int;  DECLARE pidtemp int;  DECLARE namev varchar(255);  DECLARE typev varchar(255);  DECLARE temps varchar(255);  DECLARE rs CURSOR FOR  SELECT    id,    pid,    NAME,    type  FROM t_organ  WHERE  find_in_set(pid,ids);  -- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  DECLARE CONTINUE HANDLER FOR NOT FOUND SET nos = 1; #如果读取完，设置no为1    /* create temporary table if not exists xz.temp(          temp_cid varchar(4000),          temp_ctype varchar(50)      );   */  --  SET @max_sp_recursion_depth = 10;  SET counter = 0; # 计数，循环了几次    OPEN rs; # 打开游标    WHILE nos != 1    DO # 循环读取游标数据      SET counter = counter + 1;    -- set @mySql = CONCAT('insert into temp(temp_cid,temp_ctype) values("',idtmp,'","',typev,'")');        REPEAT      FETCH rs INTO idtmp, pidtemp, namev, typev;    IF nos != 1       THEN        IF typev != 'major'          THEN          IF ISNULL(pids) || LENGTH(TRIM(pids)) < 1            THEN            SET pids = idtmp;          ELSE            SET pids = CONCAT(pids, ',', idtmp);          END IF;                --  prepare stmt from @mySql;          --   execute stmt;          --     CALL xz.procedure1(pids);        ELSE          IF ISNULL(mids)|| LENGTH(TRIM(mids)) < 1            THEN            SET mids = idtmp;          ELSE            SET mids = CONCAT(mids, ',', idtmp);          END IF;        END IF;    END IF;    UNTIL nos    END REPEAT;  END WHILE;  CLOSE rs; # 关闭游标  -- SELECT * FROM xz.temp;END

在另一个存储过程里调用这个存储过程。

CREATE DEFINER = 'zpt'@'%'PROCEDURE xz.findMajor1(IN ids varchar(255),     OUT  midss varchar(255))BEGIN DECLARE pidss varchar(255); --   DECLARE midss varchar(255);  DECLARE pids varchar(255);  DECLARE mids varchar(255);  DECLARE rmids varchar(255);  DECLARE idtmp int;  DECLARE pidtemp int;  DECLARE namev varchar(255);  DECLARE typev varchar(255);  DECLARE temps varchar(255);  DECLARE num int;  DECLARE _done int DEFAULT 0;  DECLARE nos int DEFAULT 0;  DECLARE counter int;  DECLARE flag int DEFAULT 0;  /* DECLARE rs CURSOR FOR SELECT    id,    pid,    NAME,    type  FROM t_organ  WHERE NAME = zhuanye;    DECLARE rs1 CURSOR FOR  SELECT    temp_cid,    temp_ctype  FROM xz.temp;  DECLARE CONTINUE HANDLER FOR NOT FOUND SET nos = 1; #如果读取完，设置no为1    SET counter = 0; # 计数，循环了几次    OPEN rs; # 打开游标    WHILE nos != 1 DO # 循环读取游标数据      SET counter = counter + 1;    REPEAT      FETCH rs INTO idtmp, pidtemp, namev, typev;      IF nos != 1        THEN        IF ISNULL(midss) || LENGTH(TRIM(midss)) < 1          THEN          SET midss = idtmp;        ELSE          SET midss = CONCAT(midss, ',', idtmp);        END IF;      END IF;    UNTIL nos    END REPEAT;  END WHILE;  CLOSE rs;  */  CALL xz.procedure1(ids, @pids, @mids);  SELECT    @pids INTO pidss;  SET pids = pidss;    SELECT        @mids INTO midss;      SET mids =CONCAT(mids,',',midss) ;  WHILE flag = 0    DO    IF ISNULL(pids)      THEN      SET flag = 1;    ELSE      CALL xz.procedure1(pids, @pids, @mids);      SELECT        @pids INTO pidss;      SET pids = pidss;      SELECT        @mids INTO midss;      SET mids =midss;      IF ISNULL(rmids)        THEN        SET rmids =mids;      else           SET rmids=CONCAT(rmids,',',mids) ;        end IF ;       END IF; END WHILE;SET midss=rmids;END

在mybatis中调用存储过程。

//XML文件的写法，将参数以及结果作为一个map

<select id="getChildIds" parameterType="java.util.Map"statementType="CALLABLE" resultType="String">{call findMajor1(#{ids,jdbcType=VARCHAR,mode=IN},#{midss,jdbcType=VARCHAR,mode=OUT})}</select>

//在service实现层，将参数传入，返回结果

@Overridepublic String getChildIds(String ids) {// TODO Auto-generated method stubMap<String, Object> paramMap = new HashMap<String, Object>();paramMap.put("ids", ids);hisMapper.getChildIds(paramMap);return (String) paramMap.get("midss");}