/**PageBeginHtml Block Begin **/ /***自定义返回顶部小火箭***/ /*生成博客目录的JS 开始*/ /*生成博客目录的JS 结束*/

Oracle 行转列 WM_CONCAT 函数超详细讲解


Oracle 行转列 最实用、最常用的 3 种方案（静态固定列、动态自动列、多行拼接单列），全部带可直接运行的测试数据，兼容所有业务场景，一看就会！

准备测试表（学生成绩表）

先建测试数据，所有示例都用这个表，直接复制执行：
sql
-- 学生成绩表：行数据（1个学生+1个科目=1行）
CREATE TABLE score(
    sid NUMBER,         -- 学生ID
    sname VARCHAR2(20), -- 姓名
    subject VARCHAR2(20),-- 科目
    score NUMBER        -- 分数
);

-- 插入测试数据
INSERT INTO score VALUES(1,'张三','语文',90);
INSERT INTO score VALUES(1,'张三','数学',95);
INSERT INTO score VALUES(1,'张三','英语',88);
INSERT INTO score VALUES(2,'李四','语文',85);
INSERT INTO score VALUES(2,'李四','数学',92);
INSERT INTO score VALUES(2,'李四','英语',90);
COMMIT;

方案 1：CASE WHEN 行转列（万能通用 ✅）

兼容所有 Oracle 版本（9i/10g/11g/12c/19c），最稳妥、最常用！
sql
SELECT
    sid,
    sname,
    -- 行转列核心：CASE WHEN 匹配科目 + 聚合函数
    MAX(CASE WHEN subject = '语文' THEN score END) AS 语文,
    MAX(CASE WHEN subject = '数学' THEN score END) AS 数学,
    MAX(CASE WHEN subject = '英语' THEN score END) AS 英语
FROM score
-- 按学生分组
GROUP BY sid, sname
ORDER BY sid;

效果

1 行 = 1 个学生，列 = 语文、数学、英语

方案 2：PIVOT 行转列（Oracle 11g+ 推荐 ✨）

语法更简洁，Oracle 11g 及以上版本专用，官方原生行转列语法。
sql
SELECT * FROM (
    -- 基础数据
    SELECT sid, sname, subject, score FROM score
)
-- 行转列核心语法
PIVOT(
    MAX(score)          -- 聚合函数（必须写）
    FOR subject IN (     -- 要转列的字段
        '语文' AS 语文,
        '数学' AS 数学,
        '英语' AS 英语
    )
)
ORDER BY sid;

方案 3：动态行转列（列不固定时用 ）

实际开发最常用：科目不固定（无法写死在 SQL 里），自动生成列。
sql
-- 动态行转列（PL/SQL 执行）
DECLARE
    v_sql VARCHAR2(4000);
BEGIN
    -- 拼接动态列
    SELECT LISTAGG('MAX(CASE WHEN subject='''||subject||''' THEN score END) AS '||subject, ',') 
           WITHIN GROUP (ORDER BY subject)
    INTO v_sql
    FROM (SELECT DISTINCT subject FROM score);

    -- 拼接完整SQL
    v_sql := 'SELECT sid,sname,'||v_sql||' FROM score GROUP BY sid,sname ORDER BY sid';

    -- 执行并输出
    DBMS_OUTPUT.PUT_LINE('执行SQL：'||v_sql);
    EXECUTE IMMEDIATE v_sql;
END;
/

方案 4：多行转单列（字符串拼接 ）

把多行数据拼接成一个字符串（用逗号 / 符号分隔）

1. LISTAGG（11gR2+ 推荐）

sql
SELECT
    sid,
    sname,
    -- 拼接格式：科目:分数,科目:分数
    LISTAGG(subject || ':' || score, ',') 
    WITHIN GROUP (ORDER BY subject) AS 成绩详情
FROM score
GROUP BY sid, sname;

2. WM_CONCAT（老版本兼容）

sql
SELECT
    sid,
    sname,
    WM_CONCAT(subject || ':' || score) AS 成绩详情
FROM score
GROUP BY sid, sname;

核心知识点（必看）

  1. 为什么要用 MAX ()/MIN ()？
    行转列本质是分组聚合，必须用聚合函数（单行单值用 MAX/MIN 都可以）。
  2. 适用场景
    • 固定列 → CASE WHEN / PIVOT
    • 动态列 → 动态 SQL
    • 拼接字符串 → LISTAGG
  3. 版本限制
    • PIVOT / LISTAGG → Oracle 11g+
    • CASE WHEN → 全版本兼容

最终效果对比

表格
原始行数据 行转列后
张三 语文 90 张三 90 95 88
张三 数学 95
张三 英语 88
 







Oracle WM_CONCAT 函数超详细讲解

WM_CONCAT 是 Oracle 非官方内置函数，核心作用：将多行数据拼接成一行，用逗号分隔，是老版本 Oracle（11g 及以前）最常用的行转列拼接方案。

一、核心特性

  1. 功能：多行字段 → 单行字符串（默认分隔符：,）
  2. 适用版本：Oracle 9i / 10g / 11gR1
  3. 废弃说明：12c、18c、19c 及以上版本已移除，直接使用会报错：无效的标识符 WM_CONCAT
  4. 返回类型：CLOB（需手动转 VARCHAR2）
  5. 缺点：无法指定排序、无法修改分隔符

二、基础语法 & 示例

沿用学生成绩表演示：

1. 基础用法（分组拼接多行字段）

sql
SELECT
    sid,
    sname,
    -- 拼接科目（多行变一行）
    WM_CONCAT(subject) AS 科目列表,
    -- 拼接分数
    WM_CONCAT(score) AS 分数列表
FROM score
GROUP BY sid, sname;

2. 拼接自定义内容（字段 + 符号）

sql
SELECT
    sid,
    sname,
    WM_CONCAT(subject || ':' || score) AS 成绩拼接
FROM score
GROUP BY sid, sname;

3. 转换分隔符（默认逗号 → 自定义）

WM_CONCAT 只能用逗号，想改分隔符套 REPLACE：
sql
SELECT
    sid,
    sname,
    REPLACE(WM_CONCAT(subject), ',', '、') AS 科目列表
FROM score
GROUP BY sid, sname;

三、致命缺陷（生产环境慎用 ❌）

  1. 非官方函数：Oracle 不维护，不保证兼容性
  2. 高版本报错：12c+ 直接删除了该函数
  3. 无法排序：拼接结果顺序随机，不能控制
  4. 长度限制：拼接字符串超过 4000 字节 直接报错
  5. 返回 CLOB：部分场景需要 TO_CHAR 转换

四、✅ 官方完美替代方案（必学）

Oracle 11gR2 开始提供 官方标准函数 LISTAGG，完全替代 WM_CONCAT，支持排序、自定义分隔符、官方维护。

LISTAGG 语法

sql
LISTAGG(拼接字段, '分隔符') 
WITHIN GROUP (ORDER BY 排序字段) 
AS 别名

示例（替代 WM_CONCAT）

sql
SELECT
    sid,
    sname,
    -- 自定义分隔符 + 排序（官方推荐）
    LISTAGG(subject, '、') 
    WITHIN GROUP (ORDER BY subject) 
    AS 科目列表,
    
    LISTAGG(subject || ':' || score, ',') 
    WITHIN GROUP (ORDER BY subject) 
    AS 成绩详情
FROM score
GROUP BY sid, sname;

五、老版本无 LISTAGG 怎么办？

如果你的 Oracle 是 9i/10g，必须用 WM_CONCAT，超长拼接用这个自定义函数：
sql
CREATE OR REPLACE FUNCTION STR_CAT(p_str VARCHAR2)
RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    RETURN REPLACE(WM_CONCAT(p_str), ',', '、');
END;
/

六、总结

表格
函数 支持版本 排序 分隔符 官方支持 推荐度
WM_CONCAT ≤11gR1 ❌ 不支持 ❌ 仅逗号 ❌ 非官方 ⭐
LISTAGG ≥11gR2 ✅ 支持 ✅ 自定义 ✅ 官方 ⭐⭐⭐⭐⭐

最终建议

  1. Oracle 12c+：直接用 LISTAGG，彻底抛弃 WM_CONCAT
  2. 老版本：可以临时用 WM_CONCAT，建议尽快升级 / 替换
  3. 生产环境：严禁使用 WM_CONCAT，兼容性风险极高




Oracle WM_CONCAT 函数完整源 SQL

WM_CONCAT 是 Oracle WMSYS 模式下的非官方聚合函数，由对象类型 + 函数两部分组成，以下是完整的源 SQL 实现（兼容 11g 及以下版本）。

一、官方原始实现（WMSYS.WM_CONCAT）

Oracle 官方通过 owmctab.plb、owmaggrs.plb、owmaggrb.plb 三个脚本创建，核心代码如下：

1. 定义聚合类型（WM_CONCAT_IMPL）

sql
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT (
  CURR_STR VARCHAR2(32767),  -- 存储拼接结果（10g版本）
  -- 11g版本会改为 CLOB 类型：CURR_STR CLOB,
  
  -- 初始化函数
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) 
  RETURN NUMBER,
  
  -- 迭代处理函数（核心：拼接字符串）
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, 
                                      P1 IN VARCHAR2) 
  RETURN NUMBER,
  
  -- 合并函数（并行执行时用）
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, 
                                     SCTX2 IN WM_CONCAT_IMPL) 
  RETURN NUMBER,
  
  -- 终止函数（返回最终结果）
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL, 
                                         RETURNVALUE OUT VARCHAR2,  -- 11g改为 CLOB
                                         FLAGS IN NUMBER) 
  RETURN NUMBER
);
/

2. 实现类型体（核心逻辑）

sql
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL AS
  -- 初始化：清空字符串
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) 
  RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL(NULL);
    RETURN ODCICONST.SUCCESS;
  END;

  -- 迭代：核心拼接逻辑（逗号分隔）
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, 
                                      P1 IN VARCHAR2) 
  RETURN NUMBER IS
  BEGIN
    IF CURR_STR IS NOT NULL THEN
      -- 已有内容：拼接逗号+新值
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      -- 空值：直接赋值
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  -- 合并：并行执行时合并两个上下文
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, 
                                     SCTX2 IN WM_CONCAT_IMPL) 
  RETURN NUMBER IS
  BEGIN
    IF SCTX2.CURR_STR IS NOT NULL THEN
      IF SELF.CURR_STR IS NOT NULL THEN
        SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
      ELSE
        SELF.CURR_STR := SCTX2.CURR_STR;
      END IF;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  -- 终止：返回最终拼接结果
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL, 
                                         RETURNVALUE OUT VARCHAR2, 
                                         FLAGS IN NUMBER) 
  RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/

3. 创建聚合函数（WM_CONCAT）

sql
CREATE OR REPLACE FUNCTION WM_CONCAT(P1 VARCHAR2) 
RETURN VARCHAR2 
AGGREGATE USING WM_CONCAT_IMPL;  -- 11g版本返回类型改为 CLOB
/

二、高版本兼容实现（12c+ 可用）

Oracle 12c+ 已移除 WM_CONCAT，以下是自定义兼容版（完美复刻官方功能）：

完整创建脚本（含 CLOB 支持）

sql
-- 1. 创建类型（支持CLOB）
CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB AS OBJECT (
  CURR_STR CLOB,
  
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB) 
  RETURN NUMBER,
  
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB, 
                                      P1 IN VARCHAR2) 
  RETURN NUMBER,
  
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_CLOB, 
                                     SCTX2 IN WM_CONCAT_IMPL_CLOB) 
  RETURN NUMBER,
  
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_CLOB, 
                                         RETURNVALUE OUT CLOB, 
                                         FLAGS IN NUMBER) 
  RETURN NUMBER
);
/

-- 2. 实现类型体
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB AS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB) 
  RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL_CLOB(EMPTY_CLOB());
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB, 
                                      P1 IN VARCHAR2) 
  RETURN NUMBER IS
  BEGIN
    IF CURR_STR IS NOT NULL THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL_CLOB, 
                                     SCTX2 IN WM_CONCAT_IMPL_CLOB) 
  RETURN NUMBER IS
  BEGIN
    IF SCTX2.CURR_STR IS NOT NULL THEN
      IF SELF.CURR_STR IS NOT NULL THEN
        SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
      ELSE
        SELF.CURR_STR := SCTX2.CURR_STR;
      END IF;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL_CLOB, 
                                         RETURNVALUE OUT CLOB, 
                                         FLAGS IN NUMBER) 
  RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/

-- 3. 创建兼容函数
CREATE OR REPLACE FUNCTION WM_CONCAT(P1 VARCHAR2) 
RETURN CLOB 
AGGREGATE USING WM_CONCAT_IMPL_CLOB;
/

三、核心原理与关键说明

  1. 聚合函数本质：WM_CONCAT 是基于 ODCI 聚合接口实现的自定义聚合函数，需通过 AGGREGATE USING 关联类型
  2. 版本差异：
    • 10g：返回 VARCHAR2(32767)，有长度限制
    • 11g：返回 CLOB，支持超长字符串
    • 12c+：官方移除，需手动创建兼容版
  3. 官方创建方式：通过 $ORACLE_HOME/rdbms/admin/owmctab.plb 等脚本创建
    sql
    -- 官方重建命令（SYS用户执行）
@?/rdbms/admin/owmctab.plb
@?/rdbms/admin/owmaggrs.plb
@?/rdbms/admin/owmaggrb.plb
  4. 致命缺陷：非官方函数、无排序控制、分隔符固定为逗号、12c+ 不支持

四、替代方案（官方推荐）

Oracle 11gR2+ 提供 LISTAGG 官方函数，完全替代 WM_CONCAT：
sql
SELECT
  sid,
  sname,
  LISTAGG(subject, ',') WITHIN GROUP (ORDER BY subject) AS 科目列表
FROM score
GROUP BY sid, sname;
表格
特性 WM_CONCAT LISTAGG
官方支持 ❌ 非官方 ✅ 官方
排序控制 ❌ 不支持 ✅ 支持
分隔符 ❌ 固定逗号 ✅ 自定义
返回类型 10g VARCHAR2 / 11g CLOB VARCHAR2（默认）/ CLOB
版本支持 ≤11g ≥11gR2
 



区分三大场景：Oracle 10g、Oracle 11g、Oracle 12c/18c/19c/21c（高版本官方已移除，做兼容复刻）。
前置说明：
  1. 优先使用 SYS 管理员账号 执行脚本；
  2. 原 WM_CONCAT 属于 WMSYS 系统方案，低版本严格对齐原厂结构；
  3. 所有脚本自带先删后建逻辑，重复执行不会报错。

一、通用：清理旧对象（所有版本通用，优先执行）

如果数据库已存在 WM_CONCAT 相关对象，先执行以下语句删除，避免创建冲突：
sql
-- 1. 删除聚合函数
DROP FUNCTION WMSYS.WM_CONCAT;
-- 2. 删除类型体
DROP TYPE BODY WMSYS.WM_CONCAT_IMPL;
-- 3. 删除对象类型
DROP TYPE WMSYS.WM_CONCAT_IMPL;

-- 若你是在普通用户下创建（非WMSYS），执行下面这组：
-- DROP FUNCTION WM_CONCAT;
-- DROP TYPE BODY WM_CONCAT_IMPL;
-- DROP TYPE WM_CONCAT_IMPL;
/

二、场景 1：Oracle 10G 版本（原生 VARCHAR2 版）

Oracle 10g 中 WM_CONCAT 返回 VARCHAR2(32767)，固定逗号分隔，原厂逻辑复刻。
使用 SYS / WMSYS 账号执行：
sql
-- 切换到 WMSYS 方案（原厂默认归属）
ALTER SESSION SET CURRENT_SCHEMA = WMSYS;

-- 1. 创建聚合对象类型
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT (
  CURR_STR VARCHAR2(32767),

  -- 聚合初始化
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
  -- 单行迭代拼接
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 IN VARCHAR2) RETURN NUMBER,
  -- 并行查询合并上下文
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER,
  -- 聚合结束，返回结果
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER
);
/

-- 2. 实现类型体（核心拼接逻辑）
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL AS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL(NULL);
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF CURR_STR IS NOT NULL THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    IF SCTX2.CURR_STR IS NOT NULL THEN
      IF SELF.CURR_STR IS NOT NULL THEN
        SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
      ELSE
        SELF.CURR_STR := SCTX2.CURR_STR;
      END IF;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/

-- 3. 创建聚合函数 WM_CONCAT
CREATE OR REPLACE FUNCTION WM_CONCAT(P1 VARCHAR2)
RETURN VARCHAR2
AGGREGATE USING WM_CONCAT_IMPL;
/

-- 切回原用户（可选）
ALTER SESSION SET CURRENT_SCHEMA = SYS;

三、场景 2：Oracle 11G 版本（原生 CLOB 版，推荐）

Oracle 11g 升级为 CLOB 类型，支持超长字符串（突破 32767 限制），生产环境主流版本：
sql
-- 切换到 WMSYS 方案
ALTER SESSION SET CURRENT_SCHEMA = WMSYS;

-- 1. 创建聚合对象类型（字段改为 CLOB）
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT (
  CURR_STR CLOB,

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER
);
/

-- 2. 实现类型体
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL AS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL(EMPTY_CLOB());
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF CURR_STR IS NOT NULL THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := TO_CLOB(P1);
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    IF SCTX2.CURR_STR IS NOT NULL THEN
      IF SELF.CURR_STR IS NOT NULL THEN
        SELF.CURR_STR := CURR_STR || ',' || SCTX2.CURR_STR;
      ELSE
        SELF.CURR_STR := SCTX2.CURR_STR;
      END IF;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/

-- 3. 创建聚合函数 WM_CONCAT（返回 CLOB）
CREATE OR REPLACE FUNCTION WM_CONCAT(P1 VARCHAR2)
RETURN CLOB
AGGREGATE USING WM_CONCAT_IMPL;
/

-- 切回原用户
ALTER SESSION SET CURRENT_SCHEMA = SYS;

四、场景 3：Oracle 12C / 18C / 19C / 21C（高版本兼容版）

Oracle 12c+ 官方彻底移除 WMSYS.WM_CONCAT，无法使用原厂脚本。
提供独立兼容版（无需依赖 WMSYS，任意用户可创建，行为和原版完全一致）：

4.1 完整版（CLOB 超长支持，推荐）

sql
-- 1. 创建聚合类型
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT (
  CURR_STR CLOB,

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER
);
/

-- 2. 类型体实现
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL AS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL(EMPTY_CLOB());
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF CURR_STR IS NOT NULL THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := TO_CLOB(P1);
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    IF SCTX2.CURR_STR IS NOT NULL THEN
      IF SELF.CURR_STR IS NOT NULL THEN
        SELF.CURR_STR := CURR_STR || ',' || SCTX2.CURR_STR;
      ELSE
        SELF.CURR_STR := SCTX2.CURR_STR;
      END IF;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/

-- 3. 创建兼容函数 WM_CONCAT
CREATE OR REPLACE FUNCTION WM_CONCAT(P1 VARCHAR2)
RETURN CLOB
AGGREGATE USING WM_CONCAT_IMPL;
/

4.2 精简版（VARCHAR2，短字符串场景）

仅适用于拼接内容较短（≤4000 字符），性能更高：
sql
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT (
  CURR_STR VARCHAR2(32767),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL AS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL(NULL);
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    CURR_STR := CASE WHEN CURR_STR IS NULL THEN P1 ELSE CURR_STR || ',' || P1 END;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    IF SCTX2.CURR_STR IS NOT NULL THEN
      CURR_STR := CASE WHEN CURR_STR IS NULL THEN SCTX2.CURR_STR ELSE CURR_STR || ',' || SCTX2.CURR_STR END;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/

CREATE OR REPLACE FUNCTION WM_CONCAT(P1 VARCHAR2)
RETURN VARCHAR2
AGGREGATE USING WM_CONCAT_IMPL;
/

五、权限授权（普通用户使用必备）

如果非创建用户需要调用 WM_CONCAT，用管理员账号执行授权：
sql
-- 1. 授予执行函数权限（通用）
GRANT EXECUTE ON WMSYS.WM_CONCAT TO 你的用户名;
GRANT EXECUTE ON WMSYS.WM_CONCAT_IMPL TO 你的用户名;

-- 若为高版本自定义函数（不在WMSYS下），执行：
GRANT EXECUTE ON WM_CONCAT TO 你的用户名;
GRANT EXECUTE ON WM_CONCAT_IMPL TO 你的用户名;

-- 2. 公有权限（所有用户可用，开发环境用）
GRANT PUBLIC EXECUTE ON WMSYS.WM_CONCAT;
GRANT PUBLIC EXECUTE ON WMSYS.WM_CONCAT_IMPL;

六、测试验证脚本（所有版本通用）

执行完创建脚本后，运行下面 SQL 验证函数是否生效：
sql
-- 造测试数据
CREATE TABLE test_wm_concat(
  id NUMBER,
  name VARCHAR2(20)
);
INSERT INTO test_wm_concat VALUES(1,'苹果');
INSERT INTO test_wm_concat VALUES(1,'香蕉');
INSERT INTO test_wm_concat VALUES(1,'橘子');
INSERT INTO test_wm_concat VALUES(2,'西瓜');
COMMIT;

-- 测试 WM_CONCAT 拼接
SELECT 
  id,
  WM_CONCAT(name) AS 拼接结果
FROM test_wm_concat
GROUP BY id;

-- 结果示例：
-- ID  拼接结果
-- 1   苹果,香蕉,橘子
-- 2   西瓜

七、重要补充说明

  1. 分隔符
    复刻版本和原厂一致，固定逗号 ,，如需改分隔符，外层套 REPLACE：
    sql
    SELECT id, REPLACE(WM_CONCAT(name),',','|') FROM test_wm_concat GROUP BY id;
  2. 排序问题
    WM_CONCAT 不支持内部排序，拼接顺序随机；Oracle 11gR2+ 优先使用官方 LISTAGG（支持排序 + 自定义分隔符）。
  3. 生产环境建议
    • 11gR2 及以上：放弃 WM_CONCAT，直接使用 LISTAGG（官方标准、功能更强）；
    • 10g / 11gR1 老旧系统：使用上面的复刻脚本临时兼容。
  4. 常见报错
    • ODCICONST 不存在：Oracle 系统内置包，正常环境不会缺失，无需处理；
    • 权限不足：执行第五节的授权语句。
posted @ 2026-06-01 16:50  一品堂.技术学习笔记  阅读(13)  评论(0)    收藏  举报