使用 XMLTable 和 FLWOR 表示式拆分字串

此解決方案使用 Oracle 11 中提供的 ora:tokenize XQuery 函式 。

樣本資料 ：

CREATE TABLE table_name ( id, list ) AS
SELECT 1, 'a,b,c,d' FROM DUAL UNION ALL -- Multiple items in the list
SELECT 2, 'e'       FROM DUAL UNION ALL -- Single item in the list
SELECT 3, NULL      FROM DUAL UNION ALL -- NULL list
SELECT 4, 'f,,g'    FROM DUAL;          -- NULL item in the list

查詢 ：

SELECT t.id,
       x.item,
       x.lvl
FROM   table_name t,
       XMLTABLE(
         'let $list := ora:tokenize(.,","),
              $cnt := count($list)
          for $val at $r in $list 
          where $r < $cnt
          return $val'
       PASSING list||',' 
       COLUMNS
         item VARCHAR2(100) PATH '.',
         lvl FOR ORDINALITY
       ) (+) x;

輸出 ：

        ID ITEM           LVL
---------- ------- ----------
         1 a                1 
         1 b                2 
         1 c                3 
         1 d                4 
         2 e                1 
         3 (NULL)      (NULL) 
         4 f                1 
         4 (NULL)           2 
         4 g                3