如何获取某些字符之间出现的所有子字符串?

您可以使用与第一次匹配的递归查询,然后在递归查询的下一次迭代中将其从字符串中删除。

假设您的表和列被调用 tbl 和 txt :

with cte(match, txt) as (
    select regexp_substr(txt,'\$\$(.*?)\$\$', 1, 1, null, 1),
           regexp_replace(txt,'\$\$(.*?)\$\$', '', 1, 1)
    from   tbl
    where  regexp_like(txt,'\$\$(.*?)\$\$') 
    union all
    select regexp_substr(txt,'\$\$(.*?)\$\$', 1, 1, null, 1),
           regexp_replace(txt,'\$\$(.*?)\$\$', '', 1, 1)
    from   cte
    where  regexp_like(txt,'\$\$(.*?)\$\$') 
)
select match from cte

SQL> with tbl(id, txt) as (
     select 1, '$$xxx$$' from dual union all
     select 2, '$$xxx$$ ... $$yyy$$' from dual union all
     select 3, '' from dual union all
     select 4, '$$xxx$$abc$$yyy$$' from dual union all
     select 5, '$$xxx$$ ... $$yyy$$ ... $$www$$ ... $$zzz$$' from dual union all
     select 6, '$$aaa$$$$bbb$$$$ccc$$$$ddd$$' from dual union all
     select 7, '$$aaa$$$$$$$$ccc$$$$ddd$$' from dual
   )
   select id, level, regexp_substr(txt,'(\$\$(.*?)\$\$)',1,level,null,2) element
   from tbl
   connect by regexp_substr(txt,'(\$\$(.*?)\$\$)',1,level) is not null
     and prior txt = txt
     and prior sys_guid() is not null
   order by id, level;

        ID      LEVEL ELEMENT
---------- ---------- -------------------------------------------
         1          1 xxx
         2          1 xxx
         2          2 yyy
         3          1
         4          1 xxx
         4          2 yyy
         5          1 xxx
         5          2 yyy
         5          3 www
         5          4 zzz
         6          1 aaa
         6          2 bbb
         6          3 ccc
         6          4 ddd
         7          1 aaa
         7          2
         7          3 ccc
         7          4 ddd

18 rows selected.

SQL>