Oracle自定义函数——f_henry_ConcatRowsByColumn

来源：互联网发布：java迭代器使用编辑：程序博客网时间：2024/04/26 18:57

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;

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