- SQL code
procedure buildSendReport( tel_str in clob , userId in long , telNums in long , message in varchar2, totalNums out number ) as begin end buildSendReport ;
上面是一个存储过程。
tel_str 是一个字符串,包含N个电话号码,每个号码以逗号‘,’隔开.
需求:解析字符串tel_str,把电话号码一个一个取出来,插入到一个表中,表内容包括 号码,userId,message.每个电话号码是一条记录,ID是根据序列生成,序列名字sequence_tel.
急急急.....急求~~
------解决方案--------------------
insert into 表 (主键,userId,message,电话号码)
select sequence_tel.nextval,
传进来userId,传进来的message,regexp_substr(tel_str,'[^,]+',1,level) from dual
connect by level <= telNums;
存储过程的参数名和表的字段名重复会出现问题。
------解决方案--------------------
- SQL code
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)