PL/SQL-将字符串拆分为关联数组

此类需求没有内置功能。
但您可以轻松构建如下查询来解析这些字符串:

SELECT y.* 
FROM (
    select trim(regexp_substr(str,'[^,]+', 1, level)) as str1
    from ( 
       SELECT 'test1:First string, test2: Second string, test3: Third string' as Str 
       FROM dual 
    )
    connect by regexp_substr(str, '[^,]+', 1, level) is not null
) x
CROSS APPLY(
    select trim(regexp_substr(str1,'[^:]+', 1, 1)) as key,
           trim(regexp_substr(str1,'[^:]+', 1, 2)) as value
    from dual
) y

KEY    VALUE         
------ --------------
test1  First string  
test2  Second string 
test3  Third string  

然后可以在函数中使用此查询,并将其结果传递给数组。
我把这个练习留给你,我相信你能做到(提示:使用Oracle的 bulk collect 功能)

例如,如果仍需要显示元素2为NULL,则此方法处理NULL列表元素。注意,第二个元素为NULL:

-- Original data with multiple delimiters and a NULL element for testing.
with orig_data(str) as (
  select 'test1:First string,, test3: Third string' from dual 
),
--Split on first delimiter (comma)
Parsed_data(rec) as (
  select regexp_substr(str, '(.*?)(,|$)', 1, LEVEL, NULL, 1)
  from orig_data
  where str is not null
  CONNECT BY LEVEL <= REGEXP_COUNT(str, ',') + 1 
)
-- For testing-shows records based on 1st level delimiter
--select rec from parsed_data;

-- Split the record into columns
select trim(regexp_replace(rec, '^(.*):.*', '\1')) key,
       trim(regexp_replace(rec, '^.*:(.*)', '\1')) value
from Parsed_data;

注意的正则表达式形式 [^,]+ 对于解析分隔字符串,它在NULL元素上失败。 More Information