CREATE OR REPLACE FUNCTION FUNC_GETOPTCABLEBYLINSEG(v_linkSeg in clob)
RETURN clob IS clbResult clob; -- 返回clob
vsql varchar2(4000);
Type mycur is ref cursor;
curTube mycur;
vStag varchar2(128);
vSId varchar2(128);
vLinkId varchar2(128);
vflag varchar2(1);
BEGIN
--在SplitClob2Array函数里,我把入参clob里的数据按照‘#’进行了切改后存入了临时表TLD_TMP_DATA
vflag := SplitClob2Array(v_linkSeg, '#');
vsql :='select v.FLDTAG, v.FLDID, v.FLDPARENTID from view_relquery_optcablelink v, TLD_TMP_DATA d
where v.fldparentid = d.fldid';
Open curTube For
vsql;
Loop
fetch curTube into vStag,vSId,vLinkId;
if curTube%notfound then
exit;
End If;
if clbResult='' or clbResult is null then
clbResult:=to_clob(vStag || ',' || vSId || ',' || vLinkId);
else
--clbResult:=clbResult || '#' || vStag || ',' || vSId || ',' || vLinkId;
dbms_lob.append(clbResult, '#' || vStag || ',' || vSId || ',' || vLinkId);
end if;
end loop;
close curTube;
RETURN clbResult;
EXCEPTION
WHEN OTHERS THEN
NULL;
RETURN('error');
END FUNC_GETOPTCABLEBYLINSEG;
为了方便断点调试查看,我写了个存储过程
create or replace procedure sp_yrd_test
is
inclobvar clob;
outclobvar clob;
begin
inclobvar := to_clob('001004014003,12065011131774278#001004014003,12065011131986331#001004014003,12065011132293053');
outclobvar:=FUNC_GETOPTCABLEBYLINSEG(inclobvar);
dbms_output.put_line(to_char(dbms_lob.GETLENGTH(outclobvar)));
end sp_yrd_test;可以看到函数能够得到值。但是,如果如果直接查询
select FUNC_GETOPTCABLEBYLINSEG(to_clob('001004014003,12065011131774278#001004014003,12065011131986331#001004014003,12065011132293053')) result from dual出来的clob确是空,参如的参数都一样,这事什么原因呢?如何才能得到函数的返回值呢求解释
RETURN clob IS clbResult clob; -- 返回clob
vsql varchar2(4000);
Type mycur is ref cursor;
curTube mycur;
vStag varchar2(128);
vSId varchar2(128);
vLinkId varchar2(128);
vflag varchar2(1);
BEGIN
--在SplitClob2Array函数里,我把入参clob里的数据按照‘#’进行了切改后存入了临时表TLD_TMP_DATA
vflag := SplitClob2Array(v_linkSeg, '#');
vsql :='select v.FLDTAG, v.FLDID, v.FLDPARENTID from view_relquery_optcablelink v, TLD_TMP_DATA d
where v.fldparentid = d.fldid';
Open curTube For
vsql;
Loop
fetch curTube into vStag,vSId,vLinkId;
if curTube%notfound then
exit;
End If;
if clbResult='' or clbResult is null then
clbResult:=to_clob(vStag || ',' || vSId || ',' || vLinkId);
else
--clbResult:=clbResult || '#' || vStag || ',' || vSId || ',' || vLinkId;
dbms_lob.append(clbResult, '#' || vStag || ',' || vSId || ',' || vLinkId);
end if;
end loop;
close curTube;
RETURN clbResult;
EXCEPTION
WHEN OTHERS THEN
NULL;
RETURN('error');
END FUNC_GETOPTCABLEBYLINSEG;
为了方便断点调试查看,我写了个存储过程
create or replace procedure sp_yrd_test
is
inclobvar clob;
outclobvar clob;
begin
inclobvar := to_clob('001004014003,12065011131774278#001004014003,12065011131986331#001004014003,12065011132293053');
outclobvar:=FUNC_GETOPTCABLEBYLINSEG(inclobvar);
dbms_output.put_line(to_char(dbms_lob.GETLENGTH(outclobvar)));
end sp_yrd_test;可以看到函数能够得到值。但是,如果如果直接查询
select FUNC_GETOPTCABLEBYLINSEG(to_clob('001004014003,12065011131774278#001004014003,12065011131986331#001004014003,12065011132293053')) result from dual出来的clob确是空,参如的参数都一样,这事什么原因呢?如何才能得到函数的返回值呢求解释
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货