【SQL Tricks】DBMS_RANDOM.VALUE在复制与展开行、字符串拆分中的妙用

在使用递归操作的时候，经常会遇到递归条件报错：ORA-01436: 用户数据中的 CONNECT BY 循环。特别是在做一些技巧性操作的时候，比如常见的复制和展开行，字符串拆分。这时候经常会使用层次查询CONNECT BY。但是稍加不慎，就会报递归循环错误，为了避免这种错误，有个技巧，那就是增加prior dbms_random.value is not null。
例1：复制与展开行
比如对1 ID,5 times按5次展开5行。那么这很简单，如下：

SQL>  WITH t AS
  2   (
  3    SELECT 1 ID,5 times FROM dual
  4   )
  5   SELECT ID FROM t
  6   CONNECT BY LEVEL<=times;
 
        ID
----------
         1
         1
         1
         1
         1

  但是，如果有多行数据分别按times展开，该如何做呢？比如有
  ID                  TIMES
   1                     5
   2                     3
  如果还按照上面的做法，那肯定是不行的，因为递归是先深度搜索再广度搜索。怎么办，当然办法有多种，如下：

1）根据TIMES构造序列，然后关联判断
 WITH t AS
 (
  SELECT 1 ID,5 times FROM dual UNION ALL
  SELECT 2,3 FROM dual
 )
 SELECT ID FROM t,
 (SELECT LEVEL mlevel FROM dual CONNECT BY LEVEL<=( SELECT MAX(times)  FROM t )) tmp
 WHERE t.times>=tmp.mlevel
 ORDER BY ID;

2）使用CONNECT BY,但是需要自身与自身递归
  因为自身与自身递归，CONNECT BY PRIOR ID=ID会报循环错误，因此为了欺骗ORACLE,我每次递归的条件是没有循环的，增加PRIOR DBMS_RANDOM.VALUE IS NOT NULL,如下：

 WITH t AS (
  SELECT 1 ID,5 times FROM dual UNION ALL
  SELECT 2,3 FROM dual
 )
 SELECT ID FROM t
 CONNECT BY PRIOR ID=ID
 AND LEVEL<=times
 AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
 
结果都是：
1
1
1
1
1
2
2
2

2.有具体分隔符的多行字符串拆分
  单行字符串拆分很简单，有多种方法，比如CONNECT BY+正则或INSTR,SUBSTR：

SELECT REGEXP_SUBSTR('&str', '[^,]+', 1, LEVEL) AS value_str
       FROM DUAL
       CONNECT BY LEVEL <=
       LENGTH('&str')-LENGTH(REPLACE('&str',',','')) + 1;

SELECT
     SUBSTR (inlist,
           INSTR (inlist, ',', 1, LEVEL  ) + 1,
           INSTR (inlist, ',', 1, LEVEL+1)
           - INSTR (inlist, ',', 1, LEVEL) -1 )
      AS value_str
      FROM (SELECT ','||'&str'||',' AS inlist
              FROM DUAL)
      CONNECT BY LEVEL <=
      LENGTH('&str')-LENGTH(REPLACE('&str',',','')) + 1;

输入ab,cd,efg,mm，结果为：
ab
cd
efg
mm

   如果多行字符串拆分，必然遇到与展开行同样的问题，方法也是可以用构造数据然后关联和DBMS_RANDOM.VALUE。如下：

1)使用传统数据构造方法
 SELECT ID,rn,list_str,REGEXP_SUBSTR(list_str,'[^,]+',1,rn) str
  FROM t,(SELECT LEVEL rn FROM DUAL
          CONNECT BY LEVEL<=(SELECT MAX(length(trim(translate(list_str,replace(list_str,','),' '))))+1 FROM t))
WHERE REGEXP_SUBSTR(list_str,'[^,]+',1,rn) IS NOT NULL
ORDER BY ID,rn;

2)使用DBMS_RANDOM递归技巧
SELECT id,level lv,list_str,
       rtrim(regexp_substr(list_str || ',', '.*?' || ',', 1, LEVEL), ',') AS str
FROM t
CONNECT BY id = PRIOR id
       AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
       AND LEVEL <= length(regexp_replace(list_str, '[^,]'))+1
ORDER BY ID,lv ;

结果都是：
  ID         LV LIST_STR             STR
---- ---------- -------------------- ----------
   1          1 xyy,m,ab             xyy
   1          2 xyy,m,ab             m
   1          3 xyy,m,ab             ab
   2          1 o,pn,nnnn,bb         o
   2          2 o,pn,nnnn,bb         pn
   2          3 o,pn,nnnn,bb         nnnn
   2          4 o,pn,nnnn,bb         bb
   3          1 M                    M

     上面多行拆分也可以使用INSTR+SUBSTR方式来实现，类似于正则表达式的实现，这里不做例子。充分运用SQL技巧，可以使编程更加简单，甚至更高效。复制与展开行，特别是字符串拆分，经常使用，比如前台传入一个有连接符号的字符串，然后作为SQL语句拼凑的WHERE里的字段值。这时候必须将字符串拆分后再进行处理。当然，以上需求用SQL实现，还有其他的技巧，这里主要是学习下DBMS_RANDOM.VALUE的SQL技巧，所以不再说明其他技巧。