高分求教一个存储过程式的写法

Question

如果没有具体规律，用
if...elsif...then...end if;
把你的情况都列上去，应该没有什么问题啊!

Answer 1

SQL> create or replace function f_demo(v_str varchar2,v_num number)
  2  return number as  
  3  v_in varchar2(200):=v_str||',';
  4  v_num1 number;
  5  v_num2 number;
  6  v_num3 number;
  7  v_temp number:=v_num;
  8  begin
  9  while instr(v_in,',')>0 loop
 10    if instr(v_in,'-')>instr(v_in,'|') then
 11       v_num1:=substr(v_in,1,instr(v_in,'|')-1);
 12       v_num2:=v_num1;
 13    else
 14    v_num1:=substr(v_in,1,instr(v_in,'-')-1);
 15    v_num2:=substr(v_in,instr(v_in,'-')+1,instr(v_in,'|')-instr(v_in,'-')-1);
 16    end if;
 17    v_num3:=substr(v_in,instr(v_in,'|')+1,instr(v_in,',')-instr(v_in,'|')-1);
 18    if v_temp>=v_num1 and v_temp<=v_num2 then
 19       return v_num3;
 20       exit;
 21    end if;  
 22    v_in:=substr(v_in,instr(v_in,',')+1);
 23  end loop;
 24  end f_demo;
 25  /函数已创建。已用时间:  00: 00: 01.61
SQL> select f_demo('1-4|3,5-8|26,9-10|27,13-14|27,15-17|28',7) from dual;F_DEMO('1-4|3,5-8|26,9-10|27,13-14|27,15-17|28',7)
--------------------------------------------------
                                                26已用时间:  00: 00: 00.20
SQL> select f_demo('1-4|3,5-8|26,9-10|27,13-14|27,15-17|28',13) from dual;F_DEMO('1-4|3,5-8|26,9-10|27,13-14|27,15-17|28',13)
---------------------------------------------------
                                                 27已用时间:  00: 00: 00.10
SQL>

Answer 2

都可以处理了。SQL> create or replace function f_demo(v_str varchar2,v_num number)
  2  return number as  
  3  v_in varchar2(200):=v_str||',';
  4  v_num1 number;
  5  v_num2 number;
  6  v_num3 number;
  7  v_temp number:=v_num;
  8  begin
  9  while instr(v_in,',')>0 loop
 10    if instr(v_in,'-')>instr(v_in,'|') then
 11       v_num1:=substr(v_in,1,instr(v_in,'|')-1);
 12       v_num2:=v_num1;
 13    else
 14       v_num1:=substr(v_in,1,instr(v_in,'-')-1);
 15       v_num2:=substr(v_in,instr(v_in,'-')+1,instr(v_in,'|')-instr(v_in,'-')-1);
 16    end if;
 17    if instr(v_in,'-',1,2)<instr(v_in,',') then
 18       v_num3:=substr(v_in,instr(v_in,'|')+1,instr(v_in,'-',1,2)-instr(v_in,'|')-1);
 19    else
 20       v_num3:=substr(v_in,instr(v_in,'|')+1,instr(v_in,',')-instr(v_in,'|')-1);
 21    end if;
 22    if v_temp>=v_num1 and v_temp<=v_num2 then
 23       if instr(v_in,'-',1,2)<instr(v_in,',') then
 24         v_num3:=v_num3+v_temp-v_num1;
 25       end if;
 26       return v_num3;
 27    end if;  
 28    v_in:=substr(v_in,instr(v_in,',')+1);
 29  end loop;
 30  end f_demo;
 31  /函数已创建。已用时间:  00: 00: 00.90
SQL> select f_demo('1-10|616-625,13-17|626-630',5) from dual;F_DEMO('1-10|616-625,13-17|626-630',5)
--------------------------------------
                                   620已用时间:  00: 00: 00.20
SQL> select f_demo('1-10|616-625,13-17|626-630',1) from dual;F_DEMO('1-10|616-625,13-17|626-630',1)
--------------------------------------
                                   616已用时间:  00: 00: 00.20
SQL> select f_demo('1-10|616-625,13-17|626-630',17) from dual;F_DEMO('1-10|616-625,13-17|626-630',17)
---------------------------------------
                                    630已用时间:  00: 00: 00.20
SQL> select f_demo('1-4|3,5-8|26,9-10|27,13-14|27,15-17|28',13) from dual;F_DEMO('1-4|3,5-8|26,9-10|27,13-14|27,15-17|28',13)
---------------------------------------------------
                                                 27已用时间:  00: 00: 00.20
SQL>

Answer 3

谢谢bzszp(SongZip)，虽然不是非常完美，但已经是...赐教了~：）