create procedure get_value(p_jxxmbh in varchar2)
return varchar2
as
cursor t_sor is
select sjsbmc from tsjsb where jxxmbh=p_jxxmbh;
num number:=0;
str varchar2(100);
begin
for v_sor in t_sor loop
if num=0 then
str:=v_sor.sjsbmc;
else
str:=str||','||v_sor.sjsbmc;
end if;
end loop;
return str;
end;
/
select b.cbm,b.jxxmbh,b.jxxmmc,(select get_value(jxxmbh) from tsjsb a where a.jxxmbh=b.jxxmbh) sjsbmc from tjxxm b;
return varchar2
as
cursor t_sor is
select sjsbmc from tsjsb where jxxmbh=p_jxxmbh;
num number:=0;
str varchar2(100);
begin
for v_sor in t_sor loop
if num=0 then
str:=v_sor.sjsbmc;
else
str:=str||','||v_sor.sjsbmc;
end if;
end loop;
return str;
end;
/
select b.cbm,b.jxxmbh,b.jxxmmc,(select get_value(jxxmbh) from tsjsb a where a.jxxmbh=b.jxxmbh) sjsbmc from tjxxm b;
create procedure get_value(p_jxxmbh in varchar2)
return varchar2
as
cursor t_sor is
select sjsbmc from tsjsb where jxxmbh=p_jxxmbh;
num number:=0;
str varchar2(100);
begin
for v_sor in t_sor loop
if num=0 then
str:=v_sor.sjsbmc;
else
num:=num+1;
str:=str||','||v_sor.sjsbmc;
end if;
end loop;
return str;
end;
/
return varchar2
is
cursor t_sor is select sjsbmc from tsjsb where cbm=cbm1 and jxxmbh=jxxmbh1;
str varchar2(4000);
begin
for v_sor in t_sor loop
str:=str||v_sor.sjsbmc||',';
end loop;
return substr(str,1,length(str)-1);
end ;
/
select cbm,jxxmbh,get_value(cbm,jxxmbh) sjsbmc from tjxxm;
非常感谢!
但是,这个get_Value函数运行时,出错:function get_value处于无效状态;
在sql\pl 中的objects->functions显示的小图标上有一个红色的错误标记!
请先生们再修改一下!