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"));
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)