select c from( with test as (select '13589647512,13589647513,13589647514,13589647515' c from dual) select regexp_substr(t.ca,'[^,]+',1,lv) AS c from (select c AS ca,length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt FROM test) t, (select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt) 上面的代码可以把一行字符串转换成多行。 要想完整的实现,下面这句应该可以,具体参数自己修改: insert into tableName(Ö÷¼ü,userId,message,µç»°ºÅÂë) values(select sequence_tel.nextval,,userId,message,c from( with test as (select tel_str c from dual) select regexp_substr(t.ca,'[^,]+',1,lv) AS c from (select c AS ca,length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt FROM test) t, (select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt))
号码是不一样的啊,每次插入的数据都是不一样的。
你这样能够把所有的号码弄出来嘛?
我想,起码得需要一个循环吧,但是我oracle编程实在不行.....
你的想法很正确,不过select 语句本身就是一个循环
select c from(
with test as (select '13589647512,13589647513,13589647514,13589647515' c from dual)
select regexp_substr(t.ca,'[^,]+',1,lv) AS c
from (select c AS ca,length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt FROM test) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt)
上面的代码可以把一行字符串转换成多行。
要想完整的实现,下面这句应该可以,具体参数自己修改:
insert into tableName(Ö÷¼ü,userId,message,µç»°ºÅÂë)
values(select sequence_tel.nextval,,userId,message,c from(
with test as (select tel_str c from dual)
select regexp_substr(t.ca,'[^,]+',1,lv) AS c
from (select c AS ca,length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt FROM test) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt))