CREATE OR REPLACE Function getDetail(ID VARCHAR2) RETURN VARCHAR2 IS
VReturnValue VARCHAR2(1000) := '';
Vtemp varchar2(50) := '';
CURSOR Cur_ids IS SELECT pp FROM detail WHERE PID = ID;
BEGIN
open Cur_ids;
loop
fetch Cur_ids into Vtemp;
exit when Cur_ids%notfound;
if VReturnValue is null then
VReturnValue := Vtemp;
else
VReturnValue := VReturnValue||','||Vtemp;
end if;
end loop;
close Cur_ids;
RETURN VReturnValue;
EXCEPTION
WHEN OTHERS THEN
RETURN 'error';
END;
/select pname,getDetail(pid) from project
VReturnValue VARCHAR2(1000) := '';
Vtemp varchar2(50) := '';
CURSOR Cur_ids IS SELECT pp FROM detail WHERE PID = ID;
BEGIN
open Cur_ids;
loop
fetch Cur_ids into Vtemp;
exit when Cur_ids%notfound;
if VReturnValue is null then
VReturnValue := Vtemp;
else
VReturnValue := VReturnValue||','||Vtemp;
end if;
end loop;
close Cur_ids;
RETURN VReturnValue;
EXCEPTION
WHEN OTHERS THEN
RETURN 'error';
END;
/select pname,getDetail(pid) from project
Create table project(pid int,pname varchar(10))
insert into project select 123,'AAA'
union all select 456,'BBB'Create table detail(detailid int identity(1,1),pid int,pp varchar(10))
insert into detail select 123,'DEll'
union all select 123,'IBM'
union all select 456,'TCl'--建函数
CREATE function F_GETSTR(@pid int)
returns varchar(200)
as
begin
declare @return varchar(200)
set @return=''
select @return=@return+','+pp+'' from detail
where pid=@pid
set @return=stuff(@return,1,1,'')
return @return
end--查询
select 项目名称=(select pname from project where pid=A.pid),
对应的品牌=dbo.F_GETSTR(pid)
from detail A
group by pid
--结果
项目名称 对应的品牌
------- ---------
AAA DEll,IBM
BBB TCl(所影响的行数为 2 行)--删除测试环境
Drop table project,detail
Drop function F_GETSTR
returns varchar(200)
as
begin
declare @str varchar(200)
set @str=''
select @str=@str+','+pp from detail where pid=@pid
set @str=stuff(@str,1,1,'')
return @str
end
goselect distinct d.pid,dbo.getstr(d.pid) as pname
from detail d
left join project p on(p.pid=d.pid)
(pid int,
pname [varchar] (20) NULL ,
ppsum [varchar] (255) NULL
)INSERT INTO temp(pid,pname,ppsum)
SELECT a.pid as pid,a.pname AS pname, b.pp as ppsum
FROM project a,detail b
WHERE a.pid NOT IN (SELECT pid FROM temp)
and a.pid=b.pidupdate temp
set temp.ppsum=temp.ppsum+detail.pp,
from temp
join detail
on pid IN
(SELECT pid FROM temp)