DB2递归函数返回TABLE对象 和 Mysql递归函数返回Varchar类型

DB2数据库中

1. 使用TABLE对象构建临时表

values(("1","a"),("2",b"),("3","c"),("4","d"),("e","f"));

select * from Table(DIM_BRANCH_RECU('0706677A2'));

2. 递归函数的构建

在以上查询语句中DIM_BRANCH_RECU('0706677A2') 是一个递归函数，返回一个TABLE对象，该对象中包含某机构下面所有的分支机构

SET SCHEMA EDW;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","EDW";

CREATE FUNCTION "EDW"."DIM_BRANCH_RECU"
 ("P_BRNNBR" VARCHAR(20)
 ) 
  RETURNS TABLE
   ("BRNNBR" VARCHAR(20),
    "BRNNAME" VARCHAR(42),
    "UPPERBRN" VARCHAR(20),
    "ORDERNO" INTEGER
   )
  SPECIFIC "EDW"."DIM_BRANCH_RECU"
  LANGUAGE SQL
  DETERMINISTIC
  READS SQL DATA
  STATIC DISPATCH
  CALLED ON NULL INPUT
  NO EXTERNAL ACTION
  INHERIT SPECIAL REGISTERS
  RETURN
with banktmp(brnnbr,brnname,upperbrn,orderno) as (
  select t0.brnnbr,t0.brnname,t0.upperbrn,orderno
    from DIM_BRANCH t0
    where t0.brnnbr=P_BRNNBR
  union all
  select t2.brnnbr,t2.brnname,t2.upperbrn,t2.orderno
    from DIM_BRANCH t2,banktmp t1
    where t2.upperbrn=t1.brnnbr
      and t2.brnnbr<>t2.upperbrn )
  select t.brnnbr,t.brnname,t.upperbrn,t.orderno
    from banktmp t;

COMMENT ON FUNCTION "EDW"."DIM_BRANCH_RECU"
 (VARCHAR(20)
 ) 
  IS '/***
*** 机构递归查询，不要删除
*** created:XXXX
***time:2013-07-13
***/'';';

3. 在Mysql中建立相同表，导入数据

DELIMITER $$

USE `edwdb`$$

DROP FUNCTION IF EXISTS `DIM_BRANCH_RECU`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `DIM_BRANCH_RECU`( I_BRNNBR VARCHAR(20)) RETURNS VARCHAR(10000) CHARSET gbk
BEGIN
   DECLARE sTemp VARCHAR(1000); 
   DECLARE sTempChd VARCHAR(1000);     
   SET sTemp = '$'; 
   SET sTempChd =CAST(I_BRNNBR AS CHAR); 
   WHILE sTempChd IS NOT NULL DO
       SET sTemp = CONCAT(sTemp,',',sTempChd); 
       SELECT GROUP_CONCAT(`BRNNBR`) INTO sTempChd FROM `dim_branch` WHERE FIND_IN_SET(`UPPERBRN`,sTempChd)>0; 
   END WHILE;
   RETURN sTemp;
END$$

DELIMITER ;

以上查询返回一个varchar类型的结果集，该结果集只包括某机构及其下面的所以分支机构的id号，然后可以通过查询语句得到想要的结果

SELECT * FROM dim_branch WHERE FIND_IN_SET(`BRNNBR`,DIM_BRANCH_RECU("070667800"));