处理字符串的oracle 代码

求一个处理字符串的oracle 代码

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) 

 

查看更多 下一篇