MySQL常用自定义函数

1、查询组织结构所有子级编码

    /*表结构*/
    CREATE TABLE `unit` (
      `unitcode` varchar(30) DEFAULT NULL COMMENT '组织编码',
      `unitname` varchar(80) DEFAULT NULL COMMENT '组织名称',
      `corporation` varchar(50) DEFAULT NULL,
      `address1` varchar(80) DEFAULT NULL,
      `office` varchar(80) DEFAULT NULL,
      `oname` varchar(50) DEFAULT NULL,
      `tel` varchar(50) DEFAULT NULL,
      `fax` varchar(50) DEFAULT NULL,
      `unitkind` varchar(10) DEFAULT NULL,
      `rank` varchar(2) DEFAULT NULL,
      `corpflag` smallint(6) DEFAULT NULL,
      `rsystem` varchar(80) DEFAULT NULL,
      `upunitname` varchar(80) DEFAULT NULL COMMENT '父级组织名称',
      `upunitcode` varchar(30) DEFAULT NULL COMMENT '父级组织编码',
      `postcode` varchar(20) DEFAULT NULL
    )
    
    /**
     * 函数 
     * demo1: select getChildList('xz001',1); 
     * demo2: select * from unit where FIND_IN_SET(unitcode,getChildList('xz001',1)); 
     * @param varcha(20) uid: 组织编码
     * @param tinyint(1) i_self: 是否要包含自身
     */
    CREATE DEFINER=`root`@`localhost` FUNCTION `getChildList`(`uid` VARCHAR(20), `i_self` TINYINT(1)) RETURNS varchar(1000) CHARSET utf8mb4
    BEGIN
        DECLARE sChildList    VARCHAR(1000);
        DECLARE sUnitcode VARCHAR(1000);
    
        SET sChildList = '';
        SET sUnitcode = uid;
    
        WHILE sUnitcode IS NOT NULL
        DO
    
          IF sChildList = '' THEN
            IF i_self THEN
    		    SET sChildList = sUnitcode;
    		ELSE
    		    SET sChildList = '$';
    	    END IF;
          ELSE
            SET sChildList = concat(sChildList, ',', sUnitcode);
          END IF;
    
          SELECT group_concat(unitcode)
          INTO
            sUnitcode
          FROM
            unit
          WHERE
            find_in_set(upunitcode, sUnitcode) > 0;
        END WHILE;
        
    RETURN sChildList;

  END
  

2、查询组织结构所有父级编码

    CREATE DEFINER=`root`@`localhost` FUNCTION `getParentList`( uid VARCHAR ( 20 ), `i_self` TINYINT ( 1 ) ) RETURNS varchar(1000) CHARSET utf8mb4
    BEGIN
        DECLARE
        	fid VARCHAR ( 100 ) DEFAULT '';
        DECLARE
        	s_parent_list VARCHAR ( 1000 );
        IF
        	i_self THEN
        	
        	SET s_parent_list = uid;
        ELSE 
        	SET s_parent_list = '$';
        
        END IF;
        WHILE
        		uid IS NOT NULL DO
        		
        		SET fid = ( SELECT upunitcode FROM unit WHERE unitcode = uid );
        	IF
        		fid IS NOT NULL THEN
        			
        			SET s_parent_list = concat( s_parent_list, ',', fid );
        		
        		SET uid = fid;
        		ELSE 
        			SET uid = fid;
        		
        	END IF;
        	
        END WHILE;
        RETURN s_parent_list;
    
    END

3、查询所有关联节点，含上下级

    CREATE DEFINER=`root`@`localhost` FUNCTION `getAllList`(`uid` VARCHAR(20)) RETURNS varchar(1000) CHARSET utf8mb4
    BEGIN
        DECLARE sChildList    VARCHAR(1000);
        DECLARE sUnitcode VARCHAR(1000);
    		DECLARE fid VARCHAR ( 100 ) DEFAULT '';
    		DECLARE s_parent_list VARCHAR ( 1000 ) DEFAULT uid;
    
        SET sChildList = '';
        SET sUnitcode = uid;
    
        WHILE sUnitcode IS NOT NULL
        DO
    
          IF sChildList = '' THEN
            SET sChildList = '$';
          ELSE
            SET sChildList = concat(sChildList, ',', sUnitcode);
          END IF;
    
          SELECT group_concat(unitcode)
          INTO
            sUnitcode
          FROM
            unit
          WHERE
            find_in_set(upunitcode, sUnitcode) > 0;
        END WHILE;
    		
    		WHILE uid IS NOT NULL DO
    				
    				SET fid = ( SELECT upunitcode FROM unit WHERE unitcode = uid );
    			IF
    				fid IS NOT NULL THEN
    					
    					SET s_parent_list = concat( s_parent_list, ',', fid );
    				
    				SET uid = fid;
    				ELSE 
    					SET uid = fid;
    				
    			END IF;
    			
    		END WHILE;
    
        RETURN CONCAT(s_parent_list,',',sChildList);
    
      END

4、获取完整组织架构路径

    select REPLACE(GROUP_CONCAT(unitname),',','/') from unit where FIND_IN_SET(unitcode,getParentList('10023006',1)) order by unitcode;