MySQL存储过程

两个相同功能存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS `SP_FL_UPD_LEVELS_xxx`$$
CREATE DEFINER=`root`@`%` PROCEDURE `SP_FL_UPD_LEVELS_xxx`()
BEGIN
 
  DECLARE tbCatalogID BIGINT(20) DEFAULT 0;
  DECLARE A BIGINT(20);
  DECLARE B BIGINT(20);
  DECLARE C BIGINT(20);
  DECLARE A_LEVEL_NAME VARCHAR(200);
  DECLARE B_LEVEL_NAME VARCHAR(200);
  DECLARE C_LEVEL_NAME VARCHAR(200);
  DECLARE no_more_departments INT(1);
   
   DECLARE ordernumbers CURSOR
   FOR
   SELECT CATALOG_ID FROM `t_d_catalog_info` ORDER BY CATALOG_ID DESC;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments = 1;
   SET no_more_departments=0;
   
   -- 清空栏目层级表
    TRUNCATE TABLE T_D_COLUMN_LEVELS;
    
   OPEN ordernumbers;
 # REPEAT
 
   WHILE no_more_departments = 0 AND tbCatalogID != 1 DO
      FETCH ordernumbers INTO tbCatalogID;
      
      CALL SP_GET_CALTALOG_LEVELS(tbCatalogID,A,B,C,A_LEVEL_NAME,B_LEVEL_NAME,C_LEVEL_NAME);
        -- 插入栏目层级表
        INSERT INTO T_D_COLUMN_LEVELS
        VALUES (tbCatalogID, A, A_LEVEL_NAME, B, B_LEVEL_NAME, C, C_LEVEL_NAME);
     END WHILE;
   #UNTIL no_more_departments
    
   # END REPEAT;
   
   CLOSE ordernumbers;
END$$

DELIMITER ;
#-----------------------------------------------------------------------------------

DELIMITER $$
DROP PROCEDURE IF EXISTS `SP_GET_CALTALOG_LEVELS`$$

CREATE DEFINER=`root`@`%` PROCEDURE `SP_GET_CALTALOG_LEVELS`(IN  baseID    BIGINT(20),
                                                    OUT A_LEVEL   BIGINT(20),
                                                    OUT B_LEVEL   BIGINT(20),
                                                    OUT C_LEVEL   BIGINT(20),
                                                    OUT A_LEVEL_NAME   VARCHAR(200),
                                                    OUT B_LEVEL_NAME   VARCHAR(200),
                                                    OUT C_LEVEL_NAME   VARCHAR(200)
                                                    )
BEGIN
      DECLARE tbROWID    INT(3);
      DECLARE tbID       BIGINT(20);
      DECLARE tbNAME     VARCHAR(200);
      DECLARE sTemp      VARCHAR(1000);
      DECLARE sTempChd   VARCHAR(1000);
      DECLARE LEVELS     INT(3);
      SET A_LEVEL = 0;
      SET B_LEVEL = 0;
      SET C_LEVEL = 0;
      SET A_LEVEL_NAME = '0';
      SET B_LEVEL_NAME = '0';
      SET C_LEVEL_NAME = '0';
      SET LEVELS = 0;
      
      DROP TABLE IF EXISTS `heapTable`;
      CREATE TEMPORARY TABLE `heapTable`
      (
         `ROWID`   INT(3) NOT NULL,
         `ID`      BIGINT(20) NULL,
         `NAME`    VARCHAR(200) NULL,
         `LEVEL`   INT(3) NOT NULL DEFAULT 0,
         PRIMARY KEY(`ROWID`)
      )
      ENGINE = HEAP;
      SET tbROWID = 0;
      SET tbNAME = '';
      SET sTemp = '$';
      SET sTempChd = CAST(baseID AS CHAR);
      WHILE sTempChd IS NOT NULL
      DO
         SET sTemp = CONCAT(sTemp, ',', sTempChd);
         SET tbROWID = tbROWID + 1;
         SET tbID = 0;
         SELECT GROUP_CONCAT(PARENT_ID), CATALOG_ID, CATALOG_ALIAS
           INTO sTempChd, tbID, tbNAME
           FROM T_D_CATALOG_INFO
          WHERE FIND_IN_SET(CATALOG_ID, sTempChd) > 0;
         INSERT INTO heapTable(ROWID, ID, NAME)
         VALUES (tbROWID, tbID, tbNAME);
      END WHILE;
      
      SELECT COUNT(1) INTO LEVELS FROM heapTable;
      UPDATE heapTable
         SET LEVEL = ABS(ROWID - LEVELS);
      SELECT ID,NAME
        INTO A_LEVEL,A_LEVEL_NAME
        FROM heapTable
       WHERE LEVEL = 1;
      SELECT ID,NAME
        INTO B_LEVEL,B_LEVEL_NAME
        FROM heapTable
       WHERE LEVEL = 2;
      SELECT ID,NAME
        INTO C_LEVEL,C_LEVEL_NAME
        FROM heapTable
       WHERE LEVEL = 3;
       
       
       DROP TEMPORARY TABLE IF EXISTS `heapTable`;
   END$$

DELIMITER ;

CALL SP_FL_UPD_LEVELS_xxx;  1 MIN




#----------------------------------------------------------------------------------------------
DELIMITER $$
DROP PROCEDURE IF EXISTS `SP_FL_COLUMN_LEVELS`$$
CREATE DEFINER=`root`@`%` PROCEDURE `SP_FL_COLUMN_LEVELS`()
BEGIN
    -- 栏目层级表字段
    DECLARE COLUMN_ID    BIGINT(20) DEFAULT 0;
    DECLARE A_LEVEL_ID   BIGINT(20) DEFAULT 0;
    DECLARE A_LEVEL_NAME VARCHAR(256) DEFAULT '0';
    DECLARE B_LEVEL_ID   BIGINT(20) DEFAULT 0;
    DECLARE B_LEVEL_NAME VARCHAR(256) DEFAULT '0';    
    DECLARE C_LEVEL_ID   BIGINT(20) DEFAULT 0;
    DECLARE C_LEVEL_NAME VARCHAR(256) DEFAULT '0';
    -- 每个栏目的所有父亲栏目信息临时表    
    DECLARE tmpID        BIGINT(20);
    DECLARE tmpNAME      VARCHAR(256);
    DECLARE LEVELS       INT(3) DEFAULT 0;    
    
    DECLARE TEMP_COLUMN_ID BIGINT(20) DEFAULT 0;
    DECLARE do_flag INT(1) DEFAULT 0;
    -- 变量必须都放在最前面
    DECLARE cur1 CURSOR FOR SELECT CATALOG_ID FROM t_d_catalog_info;   
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET do_flag = 1;
    
    -- 清空栏目层级表
    TRUNCATE TABLE T_D_COLUMN_LEVELS;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS t_d_column_levels_temp
    (
    LEVELS INT(3),
    COLUMN_ID   BIGINT(20),
    COLUMN_NAME VARCHAR(256)
    );    
    
    -- 使用游标，查询所有栏目    
    OPEN cur1;
    FETCH cur1 INTO TEMP_COLUMN_ID;   
    WHILE do_flag=0 DO
        SET COLUMN_ID = TEMP_COLUMN_ID;    
    
    -- 初始化临时表，一级、二级、三级层级
    TRUNCATE TABLE t_d_column_levels_temp;
    INSERT INTO t_d_column_levels_temp VALUES
    (1, 0, '0'), (2, 0, '0'), (3, 0, '0');
    SET LEVELS = 4; -- 从第4层开始
    -- 循环栏目树
    WHILE TEMP_COLUMN_ID IS NOT NULL AND LEVELS < 10 DO            
       SELECT GROUP_CONCAT(PARENT_ID), CATALOG_ID, CATALOG_ALIAS
           INTO TEMP_COLUMN_ID, tmpID, tmpNAME
           FROM T_D_CATALOG_INFO WHERE FIND_IN_SET(CATALOG_ID, TEMP_COLUMN_ID) > 0;
       IF tmpID IS NOT NULL THEN
          INSERT INTO t_d_column_levels_temp VALUES (LEVELS, tmpID, tmpNAME);
    -- INSERT INTO t_sp_auto_dispartch_exe_msg(`procedure_name`,`execute_date`)
    -- VALUES (CONCAT(LEVELS, '|', tmpID, '|', tmpNAME), NOW());            
       END IF;
           SET LEVELS = LEVELS+1;
        END WHILE;
        
        -- 初始化一级(A)栏目层级
        SELECT t.COLUMN_ID, t.COLUMN_NAME INTO A_LEVEL_ID, A_LEVEL_NAME
        FROM t_d_column_levels_temp t ORDER BY t.LEVELS DESC LIMIT 0,1;
 
         -- 初始化二级(B)栏目层级
        SELECT t.COLUMN_ID, t.COLUMN_NAME INTO B_LEVEL_ID, B_LEVEL_NAME
        FROM t_d_column_levels_temp t ORDER BY t.LEVELS DESC LIMIT 1,1;        
        
        -- 初始化三级(C)栏目层级
        SELECT t.COLUMN_ID, t.COLUMN_NAME INTO C_LEVEL_ID, C_LEVEL_NAME
        FROM t_d_column_levels_temp t ORDER BY t.LEVELS DESC LIMIT 2,1;
        
        -- 插入栏目层级表
        INSERT INTO T_D_COLUMN_LEVELS
        VALUES (COLUMN_ID, A_LEVEL_ID, A_LEVEL_NAME, B_LEVEL_ID, B_LEVEL_NAME, C_LEVEL_ID, C_LEVEL_NAME);
        
        -- 游标下移一位         
        FETCH cur1 INTO TEMP_COLUMN_ID;   
    END WHILE;
    CLOSE cur1;   
    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS t_d_column_levels_temp;    
END$$

DELIMITER ;

CALL SP_FL_COLUMN_LEVELS;   1 MIN 26 sec



DELIMITER $$

USE `report_test`$$

DROP PROCEDURE IF EXISTS `SP_FL_COLUMN_LEVELS`$$

CREATE DEFINER=`root`@`%` PROCEDURE `SP_FL_COLUMN_LEVELS`()
BEGIN
    -- 栏目层级表字段
    DECLARE COLUMN_ID    BIGINT(20) DEFAULT 0;
    DECLARE A_LEVEL_ID   BIGINT(20) DEFAULT 0;
    DECLARE A_LEVEL_NAME VARCHAR(256) DEFAULT '0';
    DECLARE B_LEVEL_ID   BIGINT(20) DEFAULT 0;
    DECLARE B_LEVEL_NAME VARCHAR(256) DEFAULT '0';    
    DECLARE C_LEVEL_ID   BIGINT(20) DEFAULT 0;
    DECLARE C_LEVEL_NAME VARCHAR(256) DEFAULT '0';
    -- 每个栏目的所有父亲栏目信息临时表    
    DECLARE tmpID        BIGINT(20);
    DECLARE tmpNAME      VARCHAR(256);    
    
    DECLARE TEMP_COLUMN_ID BIGINT(20) DEFAULT 0;
    DECLARE do_flag INT(1) DEFAULT 0;    
    
    -- 变量必须都放在最前面
    DECLARE cur1 CURSOR FOR SELECT CATALOG_ID FROM t_d_catalog_info;   
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET do_flag = 1;
    
    -- 清空栏目层级表
    TRUNCATE TABLE T_D_COLUMN_LEVELS;   
    
    -- 使用游标，遍历所有栏目    
    OPEN cur1;
   FETCH cur1 INTO TEMP_COLUMN_ID;   
    WHILE do_flag=0 DO        
        SET COLUMN_ID = TEMP_COLUMN_ID;
        SET C_LEVEL_ID=0;
    SET C_LEVEL_NAME='0';
    SET B_LEVEL_ID=0;
    SET B_LEVEL_NAME='0';
    SET A_LEVEL_ID=0;
    SET A_LEVEL_NAME='0';    
    
    -- 向上循环栏目树
    WHILE TEMP_COLUMN_ID IS NOT NULL DO            
       SELECT GROUP_CONCAT(PARENT_ID), CATALOG_ID, CATALOG_ALIAS
           INTO TEMP_COLUMN_ID, tmpID, tmpNAME
           FROM T_D_CATALOG_INFO WHERE FIND_IN_SET(CATALOG_ID, TEMP_COLUMN_ID) > 0;
       IF tmpID IS NOT NULL THEN
          SET C_LEVEL_ID=B_LEVEL_ID;
          SET C_LEVEL_NAME=B_LEVEL_NAME;
          SET B_LEVEL_ID=A_LEVEL_ID;
          SET B_LEVEL_NAME=A_LEVEL_NAME;
          SET A_LEVEL_ID=tmpID;
          SET A_LEVEL_NAME=tmpNAME;      
       END IF;           
        END WHILE;       
        
        -- 插入栏目层级表: 根栏目、一级栏目、二级栏目
        INSERT INTO T_D_COLUMN_LEVELS
        VALUES (COLUMN_ID, A_LEVEL_ID, A_LEVEL_NAME, B_LEVEL_ID, B_LEVEL_NAME, C_LEVEL_ID, C_LEVEL_NAME);
    -- 游标下移一位         
        FETCH cur1 INTO TEMP_COLUMN_ID;   
    END WHILE;
    CLOSE cur1;         
END$$

DELIMITER ;