1、当需要把一个字符串按某一分隔符分隔后,变为数据列,即把字符串行变为列,可以使用level关键字
with t as (select 'a;b;c;d;e' as str from dual)
select level, t.str, substr(t.str, 2 * (level - 1) + 1, 1) as str_signle from t connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;
2、上面的写法只是适用于一般有规律的字符串行,当遇到不规则字符串行时,可以使用oracle的正则表达式函数,请看下面的例子:
with t as
(select 'i;am;a;test;hahahhah' as str from dual)
select level, str, regexp_substr(t.str, '[^;]+', 1, level) str_single from t connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;
3、插入测试数据
insert into tb_test(val) select level from dual connect by level < 100;
4、99乘法表
select ltrim(sys_connect_by_path
(rownum - rn1+1||'*'||rownum || '=' || rpad(rownum * (rownum - rn1+1), 2) ,' '))
from
(select rownum rn1 from dual connect by rownum <=9)
where rn1 = 1
connect by rn1+1 = prior rn1;