Oracle自定义函数——f_henry_ConcatRowsByColumn

王朝oracle·作者佚名 2006-01-09

宽屏版字体: 小|中|大|超大

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;

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