自定义函数-根据相同的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