写一个分割并遍历的存储过程

Question

需要一个过程，传入的参数是“1,2,35,4,6,2,3,21……”这样的一组字符串，而我需要把每个“，”号之间的数字取出来，然后insert into A（）插到一张表中，该怎么很好的分割呢。
（传入的字符串很长）

Answer 1

--写了个语句
with tb as(
select '1,2,35,4,6,2,3,21,56,567,3432,78,3,67,5,23,43,43,4,5,5,45' id from dual)
select
substr(','||id||',',instr(','||id||',',',',1,rownum)+1,
        instr(','||id||',',',',1,rownum+1)-instr(','||id||',',',',1,rownum)-1) newid
/*
instr(','||id||',',',',1,rownum)col1,  --','rownum次出现的位置
instr(','||id||',',',',1,rownum+1)col2, --','rownum+1次出现的位置
instr(','||id||',',',',1,rownum+1)-instr(','||id||',',',',1,rownum) col3 --要截取的长度
*/
from tb
connect by rownum <= length(','||id||',')
     - length(replace(','||id||',', ',', ''))-1NEWID
---------------
1
2
35
4
6
2
3
21
56
567
3432NEWID
---------------
78
3
67
5
23
43
43
4
5
5
45已选择22行。

Answer 2

2楼写的东西，怎么用到过程中，而且完成插入呢。execute immediate =‘insert 。。’

Answer 3

execute immediate
'insert into tablename
select substr('',''||id||'','',instr('',''||id||'','','','',1,rownum)+1,
        instr('',''||id||'','','','',1,rownum+1)-instr('',''||id||'','','','',1,rownum)-1) newid
from (select '''||入参||''' id from dual) tb
connect by rownum <= length('',''||id||'','') - length(replace('',''||id||'','', '','', ''''))-1';

Answer 4

execute immediate
'insert into tablename
select substr('',''||id||'','',instr('',''||id||'','','','',1,rownum)+1,
        instr('',''||id||'','','','',1,rownum+1)-instr('',''||id||'','','','',1,rownum)-1) newid
from (select '''||入参||''' id from dual) tb
connect by rownum <= length('',''||id||'','') - length(replace('',''||id||'','', '','', ''''))-1';

Answer 5

--拼接sql的时候比较繁琐
create or replace procedure testpro(v_str varchar2)
as
sqlstr varchar2(4000);
v_sqlstr varchar2(4000);
begin
v_sqlstr:=','||v_str||',';
sqlstr:='insert into a
select
substr('''||v_sqlstr||''',instr('''||v_sqlstr||''','','',1,rownum)+1,
        instr('''||v_sqlstr||''','','',1,rownum+1)-
        instr('''||v_sqlstr||''','','',1,rownum)-1) newid
from dual
connect by rownum <= length('''||v_sqlstr||''')
     - length(replace('''||v_sqlstr||''', '','', ''''))-1';
execute immediate sqlstr;
--dbms_output.put_line(sqlstr);
end;
SQL> exec testpro('12,123,213,2321,3');PL/SQL 过程已成功完成。SQL> select * from a;        ID
----------
        12
       123
       213
      2321
         3

Answer 6

--好像Aspen 有个小小的问题，循环connect by 多了一个，多插入了一个空格
--其实这样就可以了：
select substr(id,instr(id||',',',',1,level)+1,
          instr(id||',',',',1,level+1)-instr(id||',',',',1,level)-1) newid
from tab
connect by 
level <= length(id) - length(replace(id, ',', ''))  

Answer 7

在Java中调用的时候：
java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
传入了一个 很长的字符串，而且我把你写的过程的数据类型也改成了long,但是还不行

Answer 8

CREATE OR REPLACE Function xxxxxx(param in Nvarchar2) 
return Number
Is
--创建一个数组，用来存储逗号隔开的多个值
TYPE StrArray    IS TABLE OF Varchar2(12) INDEX BY BINARY_INTEGER;
saveParam  StrArray;--你能传多少个，不会有9位数个吧，那还不的撑死鸟～
wLength            Number(9);    
wMoji              Nvarchar2(1);
wCnt               Number(3);Begin
...
wLength = LENGTH(param); --取得参数文字长度
wCnt := 1;
--循环参数文字
for widx in 1..wLength Loop
   --将每一个逗号切开的部分保存到数组中，逗号消除
   wMoji := SUBSTR(param,widx,1);
   If wMoji=',' Then
     wCnt := wCnt + 1;
     saveParam(wCnt) := null;
   Else
     saveParam(wCnt) := saveParam(wCnt) || wMoji;
   End If;
End Loop;for widx in 1..wCnt Loop
   tempParam = saveParam(wCnt)；   If tempParam <> null Then
     insert into ...
   End If;
End Loop;

Answer 9

--字符不会超过4000吧
SQL> create table tt_id(id varchar2(10))
  2  /表已创建。SQL> ed
已写入 file afiedt.buf  1  create or replace procedure p_ins_tb(str varchar2)
  2  as
  3  str2 varchar2(1000);
  4  num number;
  5  begin
  6  str2:=','||str;
  7  num:=length(str)-length(replace(str,',',''))+1;
  8  for i in 1..num loop
  9  insert into tt_id(id)
 10  select
 11  substr(str2,instr(str2,',',1,i)+1,abs(instr(str2,',',1,i+1)-instr(str2,',',1,i)-1))
 12  from dual ;
 13  end loop;
 14  exception
 15  when others then
 16  dbms_output.put_line(sqlcode||'  '||sqlerrm);
 17* end;
SQL> /过程已创建。SQL> exec p_ins_tb('1,2,35,4,6,2,3,21')PL/SQL 过程已成功完成。SQL> commit
  2  /提交完成。SQL> select * from tt_id
  2  /ID
----------
1
2
35
4
6
2
3
21已选择8行。

Answer 10

给成兄指出一个小小错误，呵呵
对最后一个逗号截取
需要判断是否是最后一个逗号，不然可能最后一个截取的字串不是对的
比如  ‘12，123456576789’
--稍作改善：
create or replace procedure p_ins_tb(str varchar2)
/*
*需求:
*     传入的参数是',1,2,35,4,6,2,3,21....'这样的一组字符串，需要把每个','号之间的数字取出来，
*     然后insert into A()插到一张表中.
*/
as
  str2 varchar2(4000);
  num number;
  tmp number;  --判断是否是最后一个逗号
begin
     str2:=','||str;
     num:=length(str2)-length(replace(str,',',''));
     for i in 1..num loop 
         select instr(str2,',',1,i+1) into tmp from dual ;
         if tmp!=0 then
            insert into a(id)
            select
                  substr(str2,instr(str2,',',1,i)+1,abs(instr(str2,',',1,i+1)-instr(str2,',',1,i)-1))
            from dual ;
        else
            --截取最后一个逗号后全部字串
            insert into a(id)
            select substr(str2,instr(str2,',',1,i)+1) from dual ;
        end if;
     end loop;
exception when others then
     dbms_output.put_line(sqlcode||'  '||sqlerrm);
end;
 

Answer 11

--改正 上面的错误SQL> ed
已写入 file afiedt.buf  1  create or replace procedure p_ins_tb(str varchar2)
  2  as
  3  str2 varchar2(1000);
  4  num number;
  5  begin
  6  str2:=','||trim(str)||',';
  7  num:=length(str)-length(replace(str,',',''))+1;
  8  for i in 1..num loop
  9  insert into tt_id(id)
 10  select
 11  substr(str2,instr(str2,',',1,i)+1,abs(instr(str2,',',1,i+1)-instr(str2,',',1,i)-1))
 12  from dual ;
 13  end loop;
 14  exception
 15  when others then
 16  dbms_output.put_line(sqlcode||'  '||sqlerrm);
 17* end;
SQL> /过程已创建。SQL> truncate table tt_id
  2  /表被截断。SQL> commit
  2  /提交完成。SQL> exec p_ins_tb('1,2,35,4,6,2,3,21')PL/SQL 过程已成功完成。SQL> alter table tt_id modify id varchar2(30)
  2  /表已更改。SQL> exec p_ins_tb('12,123456576789')PL/SQL 过程已成功完成。SQL> commit
  2  /提交完成。SQL> select * from tt_id
  2  /ID
------------------------------
1
2
35
4
6
2
3
21
12
12
123456576789已选择11行。

Answer 12

SQL> select * from tt_id
  2  /ID
------------------------------
1
2
35
4
6
2
3
21
12 ---这里先前测试 id 为varchar2(10) 过短限制问题 下面的重新调用了一次 重复
12  
123456576789

Answer 13

insert into tt_id(id)
 10  select
 11  substr(str2,instr(str2,',',1,i)+1,abs(instr(str2,',',1,i+1)-instr(str2,',',1,i)-1))
 12  from dual ;
   commit;--加个 提交 释放资源
--运行之前把表改为
alter table tt_id nologging执行完毕改回alter table tt_id logging

Answer 14

--采用批量提交试试，每100笔提交一次create or replace procedure p_ins_tb(str varchar2)
/*
*需求:
*     传入的参数是',1,2,35,4,6,2,3,21....'这样的一组字符串，需要把每个','号之间的数字取出来，
*     然后insert into A()插到一张表中.
*/
as
  str2 varchar2(4000);
  num number;
begin
     str2:=','||trim(str)||',';
     num:=length(str2)-length(replace(str,',',''))-1;  --纠正成哥，这里次数要-1
     for i in 1..num loop
            insert into a(id)
            select
                  substr(str2,instr(str2,',',1,i)+1,abs(instr(str2,',',1,i+1)-instr(str2,',',1,i)-1))
            from dual ;
            if mod(i,100)=0 then  --批量提交
               commit;
            end if ;
     end loop;
exception when others then
     dbms_output.put_line(sqlcode||'  '||sqlerrm);
end;