不是吧?才5分.
不过还是给你一条路吧,我也遇到过这个问题把下面这个函数创建了,然后用
select 字段1,toCommaText(字段2) from 表1 group by 字段1;toCommaText是一个自己写的聚簇函数.create or replace type CommaText as object (
total varchar2(32767),
i number, -- 注两个变量在type body中用CommaText('',0)进行赋初值,不能在此处赋值
static function
ODCIAggregateInitialize (start_context IN OUT CommaText)
return number,
member function
ODCIAggregateIterate (self IN OUT CommaText , value IN varchar2)
return number,
member function
ODCIAggregateTerminate (self IN CommaText,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge (self IN OUT CommaText,
second_context IN CommaText)
return number
);
/ create or replace type body CommaText
is
static function ODCIAggregateInitialize (start_context IN OUT CommaText)
return number
is
begin
start_context := CommaText('',0); --此数调用的是上面type定义中随了那个参数之外的定义,将参数直接赋值,进行初始化.
return ODCIConst.Success;
end;
member function ODCIAggregateIterate (self IN OUT CommaText,
value IN varchar2)
return number
is
begin
if (self.i > 0) then self.total := self.total || ','; end if;
self.total := self.total || value;
self.i := self.i + 1;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate (self IN CommaText,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := self.total;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge (self IN OUT CommaText,
second_context IN CommaText)
return number
is
begin
self.total := self.total || ',' || second_context.total;
return ODCIConst.Success;
end;
end;
/ CREATE or REPLACE FUNCTION toCommaText(input varchar2)
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING CommaText;
/ 例子:
select userid,toCommaText(course_wareid) from tbl_studycourse where userid between 2500 and 2502 group by userid;
返回:
USERID TOCOMMATEXT(COURSE_WAREID)
---------- ---------------------------------------
2500 135,134,136,203,199,197,198,200,131
2501 135,136,203,199,197,198,200,131,134
2502 134,200,198,135,197,199,203,136,131当然toCommaText也像普通的聚簇函数一样,可与分析函数一起使用。
select userid,toCommaText(course_wareid) over (partition by userid order by course_wareid) from tbl_studycourse where userid between 2500 and 2502;
select toCommaText(course_wareid) over (partition by userid order by course_wareid) from tbl_studycourse where userid between 2500 and 2502;
不过还是给你一条路吧,我也遇到过这个问题把下面这个函数创建了,然后用
select 字段1,toCommaText(字段2) from 表1 group by 字段1;toCommaText是一个自己写的聚簇函数.create or replace type CommaText as object (
total varchar2(32767),
i number, -- 注两个变量在type body中用CommaText('',0)进行赋初值,不能在此处赋值
static function
ODCIAggregateInitialize (start_context IN OUT CommaText)
return number,
member function
ODCIAggregateIterate (self IN OUT CommaText , value IN varchar2)
return number,
member function
ODCIAggregateTerminate (self IN CommaText,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge (self IN OUT CommaText,
second_context IN CommaText)
return number
);
/ create or replace type body CommaText
is
static function ODCIAggregateInitialize (start_context IN OUT CommaText)
return number
is
begin
start_context := CommaText('',0); --此数调用的是上面type定义中随了那个参数之外的定义,将参数直接赋值,进行初始化.
return ODCIConst.Success;
end;
member function ODCIAggregateIterate (self IN OUT CommaText,
value IN varchar2)
return number
is
begin
if (self.i > 0) then self.total := self.total || ','; end if;
self.total := self.total || value;
self.i := self.i + 1;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate (self IN CommaText,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := self.total;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge (self IN OUT CommaText,
second_context IN CommaText)
return number
is
begin
self.total := self.total || ',' || second_context.total;
return ODCIConst.Success;
end;
end;
/ CREATE or REPLACE FUNCTION toCommaText(input varchar2)
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING CommaText;
/ 例子:
select userid,toCommaText(course_wareid) from tbl_studycourse where userid between 2500 and 2502 group by userid;
返回:
USERID TOCOMMATEXT(COURSE_WAREID)
---------- ---------------------------------------
2500 135,134,136,203,199,197,198,200,131
2501 135,136,203,199,197,198,200,131,134
2502 134,200,198,135,197,199,203,136,131当然toCommaText也像普通的聚簇函数一样,可与分析函数一起使用。
select userid,toCommaText(course_wareid) over (partition by userid order by course_wareid) from tbl_studycourse where userid between 2500 and 2502;
select toCommaText(course_wareid) over (partition by userid order by course_wareid) from tbl_studycourse where userid between 2500 and 2502;
create or replace function getvalue(varchar2 pv_f1) return varchar2 as
declare
cursor c is select f2 from tab1
where f1 = pv_f1;
res varchar(2000);
v_f2 varchar(20);
begin
res = ' ';
open c;
fetch c into v_f2;
do while c%notfound
res := res || v_f2;
fetch c into v_f2;
loop
res = trim(res);
return res;
end function
2 v_temp varchar2(100):='';
3 v_out varchar2(500):='';
4 cursor c is select a.subject_name from test_table a where a.grade_id=p_grade;
5 begin
6 open c ; --打开游标
7 loop
8 fetch c into v_temp;
9 exit when c%notfound;
10 v_out:=v_out||' '||v_temp;
11 end loop;
12 close c; --关闭游标
13 return v_out;
14 exception
15 when others then
16 return 'An error occured';
17 end ;
18 /
Function created.
p_grade是你输入的变量一,参考着改一下.