create or replace function get_name(p_id in varchar2) return varchar2 is Result varchar2(100); cursor c1_sor is select STAFF_ID from cust_total_tmp where REGION_ID=p_id; cursor c2_sor(pid varchar2) is select STAFF_NAME from staff where STAFF_ID=pid; v_count Number; begin v_count:=0; Result:=''; for v1_sor in c1_sor loop for v2_sor in c2_sor(v1_sor.STAFF_ID) loop if v_count=0 then Result:=v2_sor.STAFF_NAME; v_count:=v_count+1; else Result:=Result||'/'||v2_sor.STAFF_NAME; end if; end loop; end loop; return(Result); end get_name; /select REGION_NAME,get_name(REGION_ID) STAFF_NAME from cust_total_tmp;
则可以这样写:
select REGION_NAME ,aa(REGION_ID) from cust_total_tmp;由于时间关系,我就不具体写函数的内容。祝你成功!
Result varchar2(100);
cursor c1_sor is
select STAFF_ID from cust_total_tmp where REGION_ID=p_id;
cursor c2_sor(pid varchar2) is
select STAFF_NAME from staff where STAFF_ID=pid;
v_count Number;
begin
v_count:=0;
Result:='';
for v1_sor in c1_sor loop
for v2_sor in c2_sor(v1_sor.STAFF_ID) loop
if v_count=0 then
Result:=v2_sor.STAFF_NAME;
v_count:=v_count+1;
else
Result:=Result||'/'||v2_sor.STAFF_NAME;
end if;
end loop;
end loop;
return(Result);
end get_name;
/select REGION_NAME,get_name(REGION_ID) STAFF_NAME from cust_total_tmp;