Oracle 18c中无CREATE TYPE权限时从多段线坐标字符串生成顶点明细行的查询方案

针对你在Oracle 18c中遇到的多段线坐标字符串拆分需求——把嵌套格式的坐标串拆分成带分段号、顶点号和X/Y/Z坐标的单行数据，且不能创建自定义类型，只能用查询和内置函数实现，我整理了一套可行的方案，完全通过纯查询完成，不需要插入数据到表中。

核心实现思路 #

我们可以分三步拆分：先拆分出每个分段，再拆分每个分段里的顶点，最后拆分每个顶点的X/Y/Z坐标。这里用到了Oracle的正则表达式函数、分层查询（CONNECT BY）以及递归CTE，同时利用Oracle自带的集合类型避免创建自定义类型的需求。

完整查询代码 #

WITH multipart_data AS (
    -- 替换成你的实际表名和列名，这里是模拟数据
    SELECT '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' AS multipart_lines FROM dual
),
-- 第一步：拆分出所有分段，生成分段编号
split_parts AS (
    SELECT
        ROWNUM AS part_num,
        TRIM(BOTH '() ' FROM part) AS part_coords
    FROM (
        SELECT
            REGEXP_SUBSTR(
                TRIM(BOTH '() ' FROM multipart_lines),
                '[^()]+',
                1,
                LEVEL
            ) AS part
        FROM multipart_data
        CONNECT BY LEVEL <= REGEXP_COUNT(TRIM(BOTH '() ' FROM multipart_lines), '[^()]+')
    )
    WHERE part IS NOT NULL
),
-- 第二步：拆分每个分段的顶点，生成顶点编号（用系统自带集合类型转成行）
split_vertices AS (
    SELECT
        sp.part_num,
        ROWNUM AS vertex_num,
        TRIM(v.vertex) AS vertex_coords
    FROM split_parts sp,
         TABLE(
             CAST(
                 MULTISET(
                     SELECT REGEXP_SUBSTR(sp.part_coords, '[^,]+', 1, LEVEL)
                     FROM dual
                     CONNECT BY LEVEL <= REGEXP_COUNT(sp.part_coords, '[^,]+')
                 ) AS SYS.ODCIVARCHAR2LIST
             )
         ) v
),
-- 第三步：拆分顶点的X/Y/Z坐标，转换为数值类型
split_coords AS (
    SELECT
        part_num,
        vertex_num,
        TO_NUMBER(REGEXP_SUBSTR(vertex_coords, '[^ ]+', 1, 1)) AS X,
        TO_NUMBER(REGEXP_SUBSTR(vertex_coords, '[^ ]+', 1, 2)) AS Y,
        TO_NUMBER(REGEXP_SUBSTR(vertex_coords, '[^ ]+', 1, 3)) AS Z
    FROM split_vertices
)
SELECT * FROM split_coords ORDER BY part_num, vertex_num;

各部分解释 #

1. multipart_data：这里是模拟你的表数据，实际使用时直接替换成你的真实表名和对应的坐标列名即可。
2. split_parts：先去掉整个坐标字符串外层的括号和多余空格，然后用REGEXP_SUBSTR结合CONNECT BY拆分出每个独立的分段，同时用ROWNUM生成唯一的分段编号part_num。
3. split_vertices：针对每个分段，再次用正则表达式拆分出每个顶点。这里用到了Oracle自带的SYS.ODCIVARCHAR2LIST集合类型（无需你创建，系统默认存在），配合TABLE()函数将集合转换为行数据，同时生成顶点编号vertex_num。
4. split_coords：最后把每个顶点的坐标字符串按空格拆分，提取X、Y、Z三个数值，用TO_NUMBER转换为数字类型，确保坐标值是可计算的数值格式。

无集合类型替代方案（兼容更严格的权限场景） #

如果你的环境中SYS.ODCIVARCHAR2LIST不可用（虽然18c肯定支持），可以用递归CTE替代集合转换的部分，修改split_vertices为：

split_vertices AS (
    SELECT
        part_num,
        1 AS vertex_num,
        REGEXP_SUBSTR(part_coords, '[^,]+', 1, 1) AS vertex_coords,
        REGEXP_COUNT(part_coords, '[^,]+') AS total_vertices
    FROM split_parts
    UNION ALL
    SELECT
        part_num,
        vertex_num + 1,
        REGEXP_SUBSTR(part_coords, '[^,]+', 1, vertex_num + 1),
        total_vertices
    FROM split_vertices
    WHERE vertex_num < total_vertices
)

这个版本完全用递归CTE实现顶点拆分，不依赖任何集合类型，权限要求更低。

输出结果 #

执行上述查询后，会得到你需要的格式：

PART_NUM VERTEX_NUM          X          Y          Z
---------- ---------- ---------- ---------- ----------
         1          1          0          5          0
         1          2         10         10      11.18
         1          3         30          0      33.54
         2          1         50         10      33.54
         2          2         60         10      43.54

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