Oracle字符串行拆分成列的三种方式

--muphy

开发过程中经常会遇到将前台多个值用逗号连接一同传递到后台查询，这个用逗号连接的字符串分隔的每个字符串分别对应Oracle数据库表的不同行。

如下一个表table_test的内容如下：

name       value

pa           5

pb           6

pc           8

需要查询分别与pa和pb相同的行，参数字符串为：

pi_names=”pa,pb”

如何查询呢，有以下三种方式（根据执行计划分析，效率由低到高排列）：

1.       使用Oracle Database 11g Release 2及更高版本时，可以使用递归with子查询：

with a(name, i)

as

(select regexp_substr(pi_names, '[^,]+') name,

substr(pi_names || ',', instr(pi_names, ',') + 1) i

from dual

union all

select regexp_substr(i, '[^,]+'), substr(i, instr(i, ',') + 1)

from a

where instr(i, ',') <> 0)

select t.name, t.value from table_test t inner join a on a.name = t.name

2.       使用connect by语句实现之方式一：

with a as

(select distinct regexp_substr(pi_names, '[^,]+', 1, level) name

from dual

connect by regexp_substr(pi_names, '[^,]+', 1, level) is not null

)

select t.name, t.value from table_test t inner join a on a.name = t.name

3.       使用connect by语句实现之方式二：

with a as

(select regexp_substr(pi_names, '[^,]+', 1, rownum) name

from dual

connect by rownum <= length(pi_names) -

length(replace(pi_names, ',')) + 1)

select t.name, t.value from table_test t inner join a on a.name = t.name