CREATE FUNCTION "ADMINISTRATOR"."DIGUI"
(
PARENTID BIGINT,
"TYPE" VARCHAR(10),
YEAR VARCHAR(10)
)
RETURNS BIGINT
BEGIN atomic
return
with rpl(id,name,parent) as (
select id,name,parent
from A_ORGAN
where id=PARENTID
union all
select t1.id,t1.name,t1.parent
from rpl t2,A_ORGAN t1
where t2.id=t1.parent )
select value(sum(1),0)
from (
select distinct id,name,parent
from rpl ) t5
INNER join (
select t4.XYDJ, t4.SSJGBS, t3.ND
from cmcr_sc_jbxx t4, cmcr_cj_cjb t3
where t3.SCBS = t4.BS
and t3.nd = YEAR
and t4.XYDJ=TYPE
and t4.YXBZ = 'Y'
and t3.YXBZ = 'Y'
and t3.ZT in('104','154')
) t2
on t2.SSJGBS = t5.id;
end
/******************************************************/
注: 参数
“ADMINISTRATOR”:db2登录名称,可要可不要,创建函数默认为db2登录名称;"DIGUI" :为函数名称,双引号可带可不带。
PARENTID :父级id
“TYPE” :类型 (双引号可要可不要)
YEAR :年份
atomic : 是将该部分程序块指定为一个整体,其中任何一个语句失败,则整个程序块都相当于没做,
包括包含在atomic块内的已经执行成功的语句也相当于没做,有点类似于transaction
/******************************************************/