在9i里面想自己写一个wm_contanct函数，来实现在10g里面的列合并的功能！

Question

create table t2(col1 varchar2(10),col2 varchar2(10)); 
insert into t2 values('001','vl1'); 
insert into t2 values('001','vl2'); 
insert into t2 values('001','vl3'); 
insert into t2 values('002','vl1'); 
insert into t2 values('002','vl2'); SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2 
FROM (SELECT COL1, 
               COL2, 
               MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN, 
               (ROW_NUMBER() OVER(ORDER BY COL1, COL2)) + 
               (DENSE_RANK() OVER(ORDER BY COL1)) NUMID 
          FROM T2) 
START WITH COL2 = COL2_MIN 
CONNECT BY NUMID - 1 = PRIOR NUMID 
GROUP BY COL1 ;这是样例子，在9i里面如何写成跟10g里面wm_contact一模一样功能的函数呢？

Answer 1

利用sys_connect_by_path来实现wm_concat

Answer 2

--一个自定义分且函数例子
Create Table wbq_test (a varchar2(10),b int);
Insert Into wbq_test values ('aa',1);
Insert Into wbq_test values ('bb',1);
Insert Into wbq_test values ('cc',1);
Insert Into wbq_test values ('dd',2);
Insert Into wbq_test values ('ee',2);
Insert Into wbq_test values ('ff',3);
Insert Into wbq_test values ('gg',3);
Insert Into wbq_test values ('hh',3);select * from wbq_test;CREATE OR REPLACE FUNCTION f_GroupByColumn(GroupColumnValue IN VARCHAR2)
  RETURN VARCHAR2 IS
  TYPE cur_type IS REF CURSOR;
  myCur          cur_type;
  v_Column1Value VARCHAR2(20);
  v_Result       VARCHAR2(2000);
BEGIN
  OPEN myCur FOR
    SELECT a FROM wbq_test WHERE b = GroupColumnValue;
  LOOP
    FETCH myCur
      INTO v_Column1Value;
    EXIT WHEN myCur%NOTFOUND;
    v_Result := v_Result || v_Column1Value || ',';
  END LOOP;
  CLOSE myCur;
  RETURN(v_Result);
END;select b,f_GroupByColumn(b) from wbq_test group by b;

Answer 3

--改成通用的
CREATE OR REPLACE FUNCTION my_concat(TableName       IN VARCHAR2,
                                     ColCatName      IN VARCHAR2,
                                     ColGroupByName  IN VARCHAR2,
                                     ColGroupByValue IN VARCHAR2) RETURN VARCHAR2 IS
  TYPE cur_type IS REF CURSOR;
  myCur          cur_type;
  v_Column1Value VARCHAR2(20);
  v_Result       VARCHAR2(2000);
BEGIN
  OPEN myCur FOR 'SELECT ' || ColCatName || ' FROM ' || TableName || ' WHERE ' 
                 || ColGroupByName || '=' || ColGroupByValue;
  LOOP
    FETCH myCur
      INTO v_Column1Value;
    EXIT WHEN myCur%NOTFOUND;
    v_Result := v_Result || v_Column1Value || ',';
  END LOOP;
  CLOSE myCur;
  RETURN(v_Result);
END;
--调用
select b,my_concat('wbq_test','a','b',b) from wbq_test group by b;

Answer 4

http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html

Answer 5

create or replace type tmp 
authid current_user as object
(
  tmp_strings varchar2(32767),
  static function odciaggregateinitialize(p in out tmp) return number,
  member function odciaggregateiterate(self in out tmp,p in varchar2) return number,
  member function odciaggregateterminate(self in tmp,returnvalue out varchar2,flags in number)return number,
  member function odciaggregatemerge(self in out tmp,p in tmp) return number
);
/create or replace type body tmp
is
  static function odciaggregateinitialize(p in out tmp) return number 
  is
  begin
    p := tmp(null) ;
    return odciconst.success;
  end;
  member function odciaggregateiterate(self in out tmp,p in varchar2) return number
  is
  begin
    if(tmp_strings is not null) then 
      tmp_strings := tmp_strings || ',' || p;
    else
      tmp_strings := p;
    end if;
    return odciconst.success;
  end;
  member function odciaggregateterminate(self in tmp,returnvalue out varchar2,flags in number)return number
  is
  begin
    returnvalue := tmp_strings ;
    return odciconst.success;
  end;
  member function odciaggregatemerge(self in out tmp,p in  tmp) return number
  is
  begin
    if(p.tmp_strings is not null) then 
      self.tmp_strings := self.tmp_strings || ',' || p.tmp_strings ;
    end if;
    return odciconst.success;
  end;
end;
/create or replace function my_wm_concat(p varchar2) return varchar2 aggregate using tmp ;
/[TEST@ora10gr1] SQL>with tt as (select rownum as a from dual connect by rownum< 5)
  2  select  my_wm_concat(a) from tt;my_wm_concat(A)
--------------------------------------------------------------------------------
1,2,3,4,5

Answer 6

颜色没有了。
create or replace type tmp 
authid current_user as object 
( 
  tmp_strings varchar2(32767), 
  static function odciaggregateinitialize(p in out tmp) return number, 
  member function odciaggregateiterate(self in out tmp,p in varchar2) return number, 
  member function odciaggregateterminate(self in tmp,returnvalue out varchar2,flags in number)return number, 
  member function odciaggregatemerge(self in out tmp,p in tmp) return number 
); 
/ create or replace type body tmp 
is 
  static function odciaggregateinitialize(p in out tmp) return number 
  is 
  begin 
    p := tmp(null) ; 
    return odciconst.success; 
  end; 
  member function odciaggregateiterate(self in out tmp,p in varchar2) return number 
  is 
  begin 
    if(tmp_strings is not null) then 
      tmp_strings := tmp_strings || ',' || p; 
    else 
      tmp_strings := p; 
    end if; 
    return odciconst.success; 
  end; 
  member function odciaggregateterminate(self in tmp,returnvalue out varchar2,flags in number)return number 
  is 
  begin 
    returnvalue := tmp_strings ; 
    return odciconst.success; 
  end; 
  member function odciaggregatemerge(self in out tmp,p in  tmp) return number 
  is 
  begin 
    if(p.tmp_strings is not null) then 
      self.tmp_strings := self.tmp_strings || ',' || p.tmp_strings ; 
    end if; 
    return odciconst.success; 
  end; 
end; 
/ create or replace function my_wm_concat(p varchar2) return varchar2 aggregate using tmp ; 
/ [TEST@ora10gr1] SQL>with tt as (select rownum as a from dual connect by rownum < 5) 
  2  select  my_wm_concat(a) from tt; my_wm_concat(A) 
-------------------------------------------------------------------------------- 
1,2,3,4,5