Question

oracle存储过程 遍历字符串

oracle存储过程传入一个字符串参数'1,2,3,4,5,6,7,8'。（就是以逗号隔开的一系列数字组成的字符串）。如何分割这个字符串并转为数字来循环使用。比如第一次得到number数字1，第二次得到number2.。。。以此类推。急，重赏

Answer 1

不用循环不行么，一个sql就搞定啦

 select  c from
　　(with test as (select '21,32,43' c from dual)
　　select substr(t.ca,instr(t.ca, ',', 1, c.lv) + 1,instr(t.ca, ',', 1, c.lv + 1) - (instr(t.ca, ',', 1, c.lv) + 1)) 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 )

 

'21,32,43'  --这个你换成你要查的字符串，数字字母什么都可以，只要逗号分隔就好

Answer 2

CREATE OR REPLACE
PROCEDURE pro_getnumber(
    p_str IN VARCHAR2)
AS
  v_str VARCHAR2(4000);
BEGIN
  v_str:=','||p_str||',';
  FOR i IN 1..length(v_str)
  LOOP
    IF(instr(v_str,',',1,i)!=0) THEN
      dbms_output.put_line( SUBSTR( v_str, instr(v_str,',',1,i)+1, 
      instr(v_str,',',1,i+1)-instr(v_str,',',1,i)-1 ) );
    END IF;
  END LOOP;
END;
/

追问

你这个回答是最简单易懂的，但是FOR i IN 1..length(v_str)这样最后会多跑几次无谓的循环，有办法优化吗？

追答

后面再加个跳出的判断
IF(instr(v_str,',',1,i)=0) THEN
return;
end if;

Answer 3

declare
   L_str varchar2(4000) := '1,2,3,4,5,6,7,8';
begin
   for rec_num in
      (
      select regexp_substr(COLUMN1,'[^,]+',1,rownum) COLUMN1
         from
            (
            select L_str COLUMN1
               from dual
            )
         connect by rownum<=length(COLUMN1)-length(replace(COLUMN1,','))+1
      )
   loop
      DBMS_OUTPUT.put_line(rec_num.COLUMN1);
   end loop;
end;

Answer 4

substr(string,i,j),这个是截取字符串的函数，作用为截取字符串的第i个位置开始的长度为j的字符串，估计你会用到这个，判断如果是逗号则i自增1，不是逗号就转换成数字存储到数组。至于转换成数字，‘1’和1对应的ASCII码应该不一样吧，做一个增量使一一对应应该可以。

追问

那字符串要是‘1，22，44，44，’，岂不是要认为1,2,2,4,4,4,4？
要是能给个类似的存储过程的话，我给你追加100分

追答

可以设置一个变量保存上次截取的位置，先判断下一个是不是逗号，不是逗号j自增1，直到是逗号为止，然后进行截取，开始位置是你保存的截取的位置，截取长度是j-1。。。
delare
i number(20):=1;
j number(20):=1;
max_len number(20):=1;
k number(20);
begin
max_len:=length(str);
for i in 1..max_len loop
while substr(str,i,j)!=',' loop
j:=j+1;
end loop;
dbms_output.put_line(substr(str,i,j-1));
i:=i+j;
end loop
end;
每次会截取逗号之间的数字，当然是字符串的形式，存储过程不会写

Answer 5

create or replace type type_split as table of varchar2(50); --创建一个　　type　　，如果为了使split函数具有通用性，请将其size 设大些。

--创建function
create or replace function split
(
p_list varchar2,
p_sep varchar2 := ','
) return type_split pipelined
is
l_idx pls_integer;
v_list varchar2(50) := p_list;
begin
loop
l_idx := instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list,1,l_idx-1));
v_list := substr(v_list,l_idx+length(p_sep));
else
pipe row(v_list);
exit;
end if;
end loop;
return;
end split;

使用：
select * from table(split('1,2,3,4,5,6,7,8'
,','));
然后就可以通过“，”来分割数字了