oracle实现group_connect

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