调用存诸过程将返回值打印出来

111111111111

 

declare 
Result varchar2(4000):='';
begin
  Result:= cfun_cusellratedetail_prefix(1009388);
  dbms_output.put_line(Result);
end;

 

 

 

create or replace
function cfun_cusellratedetail_prefix(p_numcusellratedmsguid in number) return varchar2 is
  Result varchar2(4000):='';
  l_lastprefix varchar2(100) :='0';
  l_sign varchar2(10);
  l_begin number;
  l_end number;

begin

  for cur_rec in (  select trim(vc2areacode) vc2areacode from ct_cusellratedetail_prefix where numcusellratedmsguid = p_numcusellratedmsguid
  AND NUMVALIDFLAG=1 order by vc2areacode  ) loop
        if length(Result) > 3950 then
         --dbms_output.put_line(p_numdmsguid||'aaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
        -- dbms_output.put_line(Resultc);
         exit ;
      end if;

      if  to_char(to_number(l_lastprefix) +1) = cur_rec.vc2areacode  then
          if l_sign is null then
             l_sign := '-';
             Result :=substr(result,1,length(result)-1 )||l_sign;

          end if;
      else
          if l_sign = '-' then

              Result :=Result|| l_lastprefix||';'||cur_rec.vc2areacode||';';
          else
              Result :=Result||cur_rec.vc2areacode||';';
          end if;
           l_sign := '';
      end if;
      l_lastprefix := cur_rec.vc2areacode;
  end loop;
  if l_sign = '-' then
     Result :=Result|| l_lastprefix;
     return  Result;

  else
      return(case when length( Result) > 0 then substr(result,1,length(result)-1 ) else '' end );
  end if;

end cfun_cusellratedetail_prefix;