自定义函数-根据相同的key合并值

--删除测试表
--drop table test;
--创建测试表
create table test (fcode varchar(10),fname varchar(20));

--删除临时表
--drop table temp;
--创建临时表
create table temp(fkey varchar(10),fvalue varchar(100));


--删除函数
--DROP FUNCTION FUN_TEST;

--自定义函数中不能使用临时表和游标
--创建函数：将多行相同的编号合并成一行，名称用分隔符合并
CREATE FUNCTION FUN_TEST(
PARA_SPLIT VARCHAR(1) -- 分隔符
)
returns table(fcode varchar(10),fname varchar(100))
language sql

modifies sql data --函数中调用存储过程需要该配置
--reads sql data

begin atomic

--定义变量
declare num int;
declare key_ varchar(10); -- 比较的关键值
declare value_ varchar(100); -- 根据相同key的值通过分隔符合并

--初始化变量
set num = 0;
set key_ = '';
set value_ = '';

--判断是否存在TEST表，如果不存在就创建该表(测试用的表)
for tableName as SELECT NAME FROM SYSIBM.SYSTABLES WHERE CREATOR NOT IN ( 'SYSIBM ', 'SYSCAT ', 'SYSSTAT ') AND NAME = 'TEST'--表名要大些
do
if(tableName.name != '') then
delete from test;
insert into test(fcode, fname) values('1001', 'A1'),('1001', 'B1'),('1001', 'C1'),('1002', 'A2'),('1002', 'B2'),('1003', 'A3');
end if;
end for;

--删除临时表的数据
for tempName as SELECT NAME FROM SYSIBM.SYSTABLES WHERE CREATOR NOT IN ( 'SYSIBM ', 'SYSCAT ', 'SYSSTAT ') AND lower(NAME) = 'temp' -- lower转换为小写
do
if(tempName.name != '') then
delete from temp;
end if;
end for;

for test as select fcode, fname from test order by fcode --需要根据编号排序
do
if num = 0 then
set key_ = test.fcode;
set value_ = test.fname;
set num = 1;
else --这里不是 else if
if (key_ = test.fcode) then
set value_ = value_ || PARA_SPLIT || test.fname;
set num = num + 1;
else
insert into temp(fkey, fvalue) values(key_,value_);
set value_ = test.fname;
set key_ = test.fcode;
set num = num + 1;
end if;
end if;
end for;

if (num > 0) then
insert into temp(fkey, fvalue) values(key_, value_);
end if;

--可以调用存储过程,不能返回存储过程的返回值

--函数返回值
RETURN select fkey, fvalue from temp;

end;


--调用函数
--select * from table(fun_test('-')) as t