PL/SQL实现列转行：拆分斜杠分隔多值列转换为对应多行数据

实现方案 #

方案1：直接用SQL实现（Oracle 11g及以上支持） #

这是最简洁的实现方式，不需要额外封装PL/SQL对象，直接查询即可得到期望结果：

-- 假设你的源表名为src_table，包含col01、col02、col03三个字段
SELECT
  REGEXP_SUBSTR(col01, '[^/]+', 1, LEVEL) AS col01,
  REGEXP_SUBSTR(col02, '[^/]+', 1, LEVEL) AS col02,
  REGEXP_SUBSTR(col03, '[^/]+', 1, LEVEL) AS col03
FROM src_table
CONNECT BY LEVEL <= REGEXP_COUNT(col01, '/') + 1
-- 以下两个条件用于兼容源表存在多行待拆分数据的场景，避免出现笛卡尔积
AND PRIOR ROWID = ROWID
AND PRIOR SYS_GUID() IS NOT NULL;

方案2：封装为PL/SQL存储过程返回结果集 #

如果需要在PL/SQL逻辑中调用该能力，可以封装为存储过程：

CREATE OR REPLACE PROCEDURE get_split_result(p_out_cursor OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_out_cursor FOR
    SELECT
      REGEXP_SUBSTR(col01, '[^/]+', 1, LEVEL) AS col01,
      REGEXP_SUBSTR(col02, '[^/]+', 1, LEVEL) AS col02,
      REGEXP_SUBSTR(col03, '[^/]+', 1, LEVEL) AS col03
    FROM src_table
    CONNECT BY LEVEL <= REGEXP_COUNT(col01, '/') + 1
    AND PRIOR ROWID = ROWID
    AND PRIOR SYS_GUID() IS NOT NULL;
END;
/

调用示例：

DECLARE
  v_cursor SYS_REFCURSOR;
  v_col01 VARCHAR2(50);
  v_col02 VARCHAR2(50);
  v_col03 VARCHAR2(50);
BEGIN
  get_split_result(v_cursor);
  LOOP
    FETCH v_cursor INTO v_col01, v_col02, v_col03;
    EXIT WHEN v_cursor%NOTFOUND;
    -- 这里可以自定义处理逻辑，比如打印结果
    DBMS_OUTPUT.PUT_LINE(v_col01 || ' | ' || v_col02 || ' | ' || v_col03);
  END LOOP;
  CLOSE v_cursor;
END;
/

注意事项 #

• 请保证三个列拆分后的元素数量一致，否则数量更少的列对应位置会返回空值
• 如果使用的是Oracle 11g以下版本，没有REGEXP_COUNT函数，可以替换为LENGTH(col01) - LENGTH(REPLACE(col01, '/', '')) + 1计算拆分后的总条数
• 若源表永远只有一行待拆分数据，可以省略CONNECT BY子句后的PRIOR相关条件

内容的提问来源于stack exchange，提问作者10k