Oracle中函数表在交叉连接的工作原理及列表展开实现

首先先回顾下你用来拆分逗号分隔列表的完整SQL：

with tbl as ( 
    select 1 id, 'a,b' lst from dual 
    union all 
    select 2 id, 'c' lst from dual 
    union all 
    select 3 id, 'e,f,g' lst from dual
)
select tbl.ID 
, regexp_substr(tbl.lst, '[^,]+', 1, lvl.column_value) elem 
, lvl.column_value lvl 
from tbl 
, table(cast(multiset( 
    select level from dual connect by level <= regexp_count(tbl.lst, ',')+1
) as sys.odcinumberlist)) lvl;

接下来一步步拆解交叉连接里的函数表逻辑：

1. 先搞懂multiset + connect by的作用 #

对于tbl中的每一行数据，这个子查询会生成一个数字序列：

• regexp_count(tbl.lst, ',')+1：计算当前行lst列里逗号分隔元素的总数（比如ID=1的行有2个元素，ID=3的行有3个）。
• select level from dual connect by level <= 上述总数：生成从1到元素总数的连续数字（比如ID=1生成1、2；ID=3生成1、2、3）。
• multiset(...)：把这个数字序列转换成一个集合对象，相当于把多行数字打包成一个集合变量。

2. cast(...) as sys.odcinumberlist的作用 #

Oracle内置的sys.odcinumberlist是一个预定义的集合类型（本质是可变长度的数字数组）。cast操作是把multiset生成的集合转换成这个Oracle能识别的标准集合类型，这样后续的table()函数才能处理它。

3. table()函数：把集合转成临时行集 #

table()函数的核心功能是将集合对象转换成一张临时表（行集）。比如上面生成的集合[1,2]会变成两行数据，每行的column_value字段分别是1和2；集合[1,2,3]变成三行，以此类推。

4. 交叉连接的作用 #

这里的逗号（,)就是Oracle中交叉连接（CROSS JOIN）的简写形式。它会把tbl中的每一行，和table()生成的临时表中的所有行进行笛卡尔关联：

• 当tbl行是ID=1（lst='a,b'）时，临时表有2行，所以关联后得到2条结果；
• 当tbl行是ID=2（lst='c'）时，临时表只有1行，所以关联后得到1条结果；
• 当tbl行是ID=3（lst='e,f,g'）时，临时表有3行，所以关联后得到3条结果。

最后regexp_substr(tbl.lst, '[^,]+', 1, lvl.column_value)就是根据lvl.column_value这个位置参数，从lst中提取对应位置的元素，最终得到你看到的拆分结果。

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