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;
/* 上面的操作仅限于一张表内。 */
[ORACLE]Oracle自定义函数——f_henry_GetStringLength
|