阿里巴巴项目用到：
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(ou_code, ';')), ';') AS RESULT
FROM (SELECT category_id, ou_code, RN, LEAD(RN) OVER(ORDER BY RN) RN1
FROM (SELECT category_id,
ou_code,
ROW_NUMBER() OVER(ORDER BY category_id, ou_code DESC) RN
FROM (SELECT t.category_id, t.ou_code
FROM bpm_dcs_category_ou t
WHERE t.category_id=1000085)))
START WITH RN1 IS NULL
AND category_id = 1000085
CONNECT BY RN1 = PRIOR RN;
函数定义
CREATE OR REPLACE FUNCTION GET_OU_LIST(I_CATRGORY_ID IN NUMBER) RETURN VARCHAR2 IS
OU_LIST VARCHAR2(4000) := '';
BEGIN
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(OU_CODE, ';')), ';') INTO OU_LIST
FROM (SELECT CATEGORY_ID, OU_CODE, RN, LEAD(RN) OVER(ORDER BY RN) RN1
FROM (SELECT CATEGORY_ID,
OU_CODE,
ROW_NUMBER() OVER(ORDER BY CATEGORY_ID, OU_CODE DESC) RN
FROM (SELECT T.CATEGORY_ID, T.OU_CODE
FROM BPM_DCS_CATEGORY_OU T
WHERE T.CATEGORY_ID=I_CATRGORY_ID)))
START WITH RN1 IS NULL
AND CATEGORY_ID = I_CATRGORY_ID
CONNECT BY RN1 = PRIOR RN;
RETURN OU_LIST;
END GET_OU_LIST;
