CREATE FUNCTION "YNTJ"."GETHEALTHCOMMA"
(
mainId varchar(40),
subTableName varchar(40),
subId varchar(40),
subString varchar(2000),
dd VARCHAR(100),
ddid VARCHAR(100)
)
returns varchar(2000)
LANGUAGE SQL
SPECIFIC SQL110511145990134
begin
DECLARE v_at_end INTEGER DEFAULT 0;--标识是否有数据
declare Result varchar(2000);
declare exesql varchar(500);
declare v_name varchar(30);
declare v_errmsg varchar(300);
DECLARE refcursor STATEMENT;
DECLARE c_result CURSOR FOR refcursor;
set exesql= 'select distinct(d.name) from ' || subTableName ||' s,'||dd||' d where s.'||subId||' = ' || mainId ||' and s.'||subString||'=d.'||ddid;
PREPARE refcursor FROM exesql;
declare continue handler for not found--这行出错, set v_at_end=1;赋值失败,跳不出循环
begin
set v_at_end=1;
end;
OPEN c_result;
loop1: loop
fetch c_result into v_name;
if v_at_end=1 then
leave loop1;
end if;
set Result=v_name || ',' || Result;
end loop ;
close c_result;
set Result = trim(Result);
set Result = substr(Result,1,length(Result)-1);
Return trim(Result);
end
收起