Oracle xmlagg() 函数与listagg()函数的区别-sql一个字段多条查询结果拼接为一个字符串

序：日常开发中，有时候需要我们将多条查询结果拼接成一个字符串输出,这时候我们就需要使用这个拼接函数进行操作。

业务场景：需要返回未审核的订单的订单号输出，并且子查询拼接到主查询上面。一个字段多条查询结果拼接

listagg()函数

函数返回结果为varchar2格式的数据，即拼接后的字符串最大可以保存4000字节的数据，所以大于这个数据的字符串就会报

ORA-01489 字符串连接的结果过长  的错误

拼接符号可以自己定义。

select listagg(t.a, ',') within group(order by t.a) as a
       from table t;--基础写法

实际业务应用 

select listagg(v.exp_number, ',') within group(order by v.exp_number)--函数写法
          from (select h.requisition_number as exp_number
                  from acp_acp_requisition_hds    h,
                       cux_pur_order_acp_req_refs c,
                       pur_order_lines            l,
                       prj_project_detial         t1
                 where h.acp_requisition_header_id =
                       c.acp_requisition_header_id
                   and c.pur_order_line_id = l.pur_order_line_id
                   and t1.dimension5_id = l.dimension5_id
                   and t1.prj_project_id = ?
                   and (h.audit_flag = 'N' or h.audit_flag is null)
                union all
                select h.exp_report_number as exp_number, t1.prj_project_id
                  from prj_project_detial t1,
                       exp_report_lines   l,
                       exp_report_headers h
                 where l.dimension5_id = t1.dimension5_id
                   and l.exp_report_header_id = h.exp_report_header_id
                   and t1.prj_project_id = ?
                   and (l.audit_flag = 'N' or l.audit_flag is null)) v
--查询出来的结果v含有多条数据：一个字段多条数据
--拼接效果：X20190920001,X20190918002,...

 xmlagg()函数

当查询结果过长，拼接的字符串长度过长大于4000字节，我们可以使用这个函数，函数返回结果为CLOB类型，大对象数据类型

最大可以存储4GB的数据长度。拼接符号可以自己定义

select xmlagg(xmlparse(content t.a||',' wellformed) order by t.at.a).getclobval()
       from table t;--基础写法

实际业务应用

select xmlagg(xmlparse(content v.exp_number||',' wellformed) order by v.exp_number).getclobval()--函数写法
          from (select h.requisition_number as exp_number
                  from acp_acp_requisition_hds    h,
                       cux_pur_order_acp_req_refs c,
                       pur_order_lines            l,
                       prj_project_detial         t1
                 where h.acp_requisition_header_id =
                       c.acp_requisition_header_id
                   and c.pur_order_line_id = l.pur_order_line_id
                   and t1.dimension5_id = l.dimension5_id
                   and t1.prj_project_id = ?
                   and (h.audit_flag = 'N' or h.audit_flag is null)
                union all
                select h.exp_report_number as exp_number, t1.prj_project_id
                  from prj_project_detial t1,
                       exp_report_lines   l,
                       exp_report_headers h
                 where l.dimension5_id = t1.dimension5_id
                   and l.exp_report_header_id = h.exp_report_header_id
                   and t1.prj_project_id = ?
                   and (l.audit_flag = 'N' or l.audit_flag is null)) v
--查询出来的结果v含有多条数据：一个字段多条数据
--拼接效果：X20190920001,X20190918002,...
 

WMSYS.WM_CONCAT()函数

返回对象也是大对象数据类型，可以多个字段同时拼接（使用'||'符号拼接）。中间连接符为逗号。

select WMSYS.WM_CONCAT(t.a) from table t;
--拼接效果
--1,2,2,3,4,...

多字段多条数据拼接

可以使用 '||' 符号进行拼接，也可以使用上述函数再结合 '||' 进行拼接。

select t.a || t.b || t.c as abc from table t;

怎么说呢，做了这么久的业务开发，面向数据库编程，突然间意识到一直学习的都是怎么去操作数据库，对于算法，数据结构这些感觉都忘记的差不多了，而这些才是程序员的基础，技术素养，决定了自己能走多远，走的多舒服！