oracle实现group_connect

a7ss3aataa

1.10g及以后版本使用wm_concat

2.

wm_concat出现在oracle10g版本中，不仅是加密的，而且是在一个单独的用户中，不方便使   并且10g以前的版本也用不上。

注：因为wm_connect只能存4000字符 超过4000报错 猜测可能返回值是varchar2   因为加密 没有去



F_LINK_LOB 是返回的clob类型


--方法  F_LINK_LOB

CREATE OR REPLACE FUNCTION  F_LINK_LOB (P_STR VARCHAR2)
   RETURN CLOB
AGGREGATE USING T_LINK_LOB;
/
--类型T_LINK_LOB

CREATE OR REPLACE TYPE "T_LINK_LOB"
AS
   OBJECT
   (
      V_LOB CLOB,
      STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT NOCOPY T_LINK_LOB)
      RETURN NUMBER,
      MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT NOCOPY T_LINK_LOB, VALUE IN VARCHAR2)
         RETURN NUMBER,
      MEMBER FUNCTION ODCIAGGREGATETERMINATE
      (
         SELF          IN            T_LINK_LOB,
         RETURNVALUE      OUT NOCOPY CLOB,
         FLAGS         IN            NUMBER
      )
         RETURN NUMBER,
      MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT NOCOPY T_LINK_LOB, CTX2 IN T_LINK_LOB)
         RETURN NUMBER
   );
/
CREATE OR REPLACE TYPE BODY T_LINK_LOB
IS
   STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT NOCOPY T_LINK_LOB)
      RETURN NUMBER
   IS
   BEGIN
      SCTX := T_LINK_LOB (NULL);
      DBMS_LOB.CREATETEMPORARY (SCTX.V_LOB, TRUE, DBMS_LOB.SESSION);
      DBMS_LOB.OPEN (SCTX.V_LOB, DBMS_LOB.LOB_READWRITE);
      RETURN ODCICONST.SUCCESS;
   END;

   MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT NOCOPY T_LINK_LOB, VALUE IN VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      DBMS_LOB.WRITEAPPEND (SELF.V_LOB, LENGTH (VALUE) + 1, VALUE || ',');
      RETURN ODCICONST.SUCCESS;
   END;

   MEMBER FUNCTION ODCIAGGREGATETERMINATE
   (
      SELF          IN            T_LINK_LOB,
      RETURNVALUE      OUT NOCOPY CLOB,
      FLAGS         IN            NUMBER
   )
      RETURN NUMBER
   IS
   BEGIN
      DBMS_LOB.CREATETEMPORARY (RETURNVALUE, TRUE, DBMS_LOB.CALL);
      DBMS_LOB.COPY (RETURNVALUE, SELF.V_LOB, DBMS_LOB.GETLENGTH (SELF.V_LOB) - 1);
      RETURN ODCICONST.SUCCESS;
   END;

   MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT NOCOPY T_LINK_LOB, CTX2 IN T_LINK_LOB)
      RETURN NUMBER
   IS
   BEGIN
      NULL;
      RETURN ODCICONST.SUCCESS;
   END;
END;
/

使用：

sql = "select t.*, tt.userName from "
     + TABLE_NAME
     + " t,(select t.groupid, F_LINK_LOB(u.username) as userName from rg_groupanduser t, users u where t.userid = u.id "
     + "group by t.groupid) tt where  t.id = tt.groupid(+) and t.fast = 0  and t.rggrouptype = "
     + type;

3.通过sql实现

select devicesid,ltrim(max(sys_connect_by_path(channelnum,',')),',') row2col
from (select devicesid,channelnum,
devicesid+(row_number() over(order by devicesid)) node_id,
row_number() over(partition by devicesid order by devicesid) rn
from channels ORDER BY devicesid asc, channelnum asc)
start with rn = 1
connect by node_id-1 = prior node_id
group by devicesid
order by devicesid;


如果含有特殊字符替换掉特殊字符

select b.devicesid,my_concat(b.channelnum) from devices a,channels b where a.deviceid = b.devicesid   
group by b.devicesid,b.channelnum     
ORDER BY b.devicesid asc, b.channelnum asc


  select 9001, 1, a.devicesid, a.rights
    from (
    select devicesid,
                 ltrim(max(sys_connect_by_path(channelnum, ',')), ',') rights
            from (select devicesid,
                         channelnum,
                         REPLACE(devicesid,'@','') + (row_number() over(order by devicesid)) node_id,
                         row_number() over(partition by devicesid order by devicesid) rn
                    from channels
                   ORDER BY devicesid asc, channelnum asc)
           start with rn = 1
          connect by node_id - 1 = prior node_id
           group by devicesid
           order by devicesid
           ) a,
         devices b
   where b.coding like '001%'
     and a.devicesid = b.deviceid