oracle函数 rows,Oracle自定义函数——f_henry_ConcatRowsByColumn

Create Table henry_test (a varchar2(10),b int);

Insert Into henry_test values ('aa',1);

Insert Into henry_test values ('bb',1);

Insert Into henry_test values ('cc',1);

Insert Into henry_test values ('dd',2);

Insert Into henry_test values ('ee',2);

Insert Into henry_test values ('ff',3);

Insert Into henry_test values ('gg',3);

Insert Into henry_test values ('hh',3);

Commit;

/*

SQL> select * from henry_test;

A                                                B

---------- ---------------------------------------

aa                                               1

bb                                               1

cc                                               1

dd                                               2

ee                                               2

ff                                               3

gg                                               3

hh                                               3

8 rows selected

*/

create or replace function f_henry_ConcatRowsByColumn(

Column2Value in Varchar2,  --分组该列的值

ColumnName1 in Varchar2,  --要连接的列名

ColumnName2 in Varchar2,  --用来做分组依据的列名

TableName  in Varchar2  --表名

)

return varchar2 is

v_Result varchar2(32767);

type cur_type is ref cursor;

myCur cur_type;

v_Column1Value varchar2(4000);

begin

Open myCur for 'Select '||ColumnName1||' From '||TableName||' Where '||ColumnName2||' = '||Column2Value;

Loop

Fetch myCur Into v_Column1Value;

Exit When myCur%notfound;

v_Result:=v_Result||v_Column1Value||',';

End Loop;

Close myCur;

return(v_Result);

end f_henry_ConcatRowsByColumn;

/*

SQL> select B,f_henry_ConcatRowsByColumn(B,'A','B','henry_test') from henry_test group by B;

B F_HENRY_CONCATROWSBYCOLUMN(B,'

--------------------------------------- --------------------------------------------------

1 aa,bb,cc,

2 dd,ee,

3 ff,gg,hh,*/

Drop Table henry_test;

/*上面的操作仅限于一张表内。*/