CONNECT BY LEVEL

SQL> select ename from emp where ename='KING';

ENAME
----------
KING


---需求 将KING 转换为如下模式:
K
I
N
G

首选创建一个
CREATE TABLE T10 AS SELECT LEVEL AS ID FROM dual CONNECT BY LEVEL <= 10;

SQL> select * from t10;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

已选择10行。

SQL>  SELECT e.ename,
  2         iter.pos,
  3         substr(e.ename, iter.pos, 1) AS c,
  4         'substr(''' || e.ename || ''', ' || iter.pos || ', 1)' AS fun
  5    FROM (SELECT ename FROM emp WHERE ename = 'KING') e,
  6         (SELECT id AS pos FROM t10) iter
  7   WHERE iter.pos <= length(e.ename);

ENAME             POS C  FUN
---------- ---------- -- -----------------------------------------------------------------
KING                1 K  substr('KING', 1, 1)
KING                2 I  substr('KING', 2, 1)
KING                3 N  substr('KING', 3, 1)
KING                4 G  substr('KING', 4, 1)


或者利用CONNECT BY LEVEL 循环语句:
SQL> SELECT ename,
  2         LEVEL AS lv,
  3         substr(ename, LEVEL, 1) AS c,
  4         'substr(''' || ename || ''', ' || LEVEL || ', 1)' AS fun
  5    FROM (SELECT ename FROM emp WHERE ename = 'KING') e
  6  CONNECT BY LEVEL <= length(ename);

ENAME              LV C  FUN
---------- ---------- -- -----------------------------------------------------------------
KING                1 K  substr('KING', 1, 1)
KING                2 I  substr('KING', 2, 1)
KING                3 N  substr('KING', 3, 1)
KING                4 G  substr('KING', 4, 1)