SQL 陳述式(四)-自訂函數

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;
------------------------------------
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;