自定义聚集函数代替wm_concat，WM_CONCAT字符超过4000的处理办法

-- 准备数据
create table test(id number,name varchar2(100));

-- 删除表，测试后不需要时调用
drop table test  

-- 测试数据
insert into test values(1,'a');  
insert into test values(1,'b');  
insert into test values(1,'c');  
insert into test values(2,'d');  
insert into test values(2,'e');  

-- 检查测试数据
select * from test

--测试系统自带wm_concat
select wm_concat(name) from test


-------------------------------------------------------------------------------------开始
-- 步骤1：自定义一个类型【str2tblType 】
create or replace type str2tblType as table of varchar2(4000)


-- 步骤2：自定义函数【my_wm_concat】
CREATE OR REPLACE FUNCTION my_wm_concat(
    p_str2tbltype str2tbltype,
    p_delim       IN VARCHAR2 DEFAULT ','
    ) 
RETURN CLOB IS
l_result CLOB;
BEGIN 
    FOR cc IN (SELECT column_value
              FROM TABLE(p_str2tbltype)
             ORDER BY column_value) LOOP
  l_result := l_result || p_delim || cc.column_value;
 END LOOP;
RETURN ltrim(l_result, p_delim);
END;

-- 步骤3-初始化测试数据
BEGIN   
  FOR idx IN 1 .. 10000 LOOP
  INSERT INTO test(id,name) VALUES (1,sys_guid());
  END LOOP;
END;

-- 步骤4-调用测试1
SELECT my_wm_concat(CAST(COLLECT(name) AS str2tbltype)) attributes
FROM test
WHERE rownum < 1000;

SELECT my_wm_concat(
  CAST(COLLECT(emp_name) AS str2tbltype))
FROM  dim_employee

SELECT my_wm_concat(
  CAST(COLLECT(emp_name) AS str2tbltype),
  'aa'-- 分割字符，若不传，默认','
  )
FROM  dim_employee