号码拆分提取功能

----如包含[1234]则可以拆分为1、2、3、4四种；

----  包含[0-5]则可以拆分为0至5六种；

----  包含X 则可以拆分为0至9十种；

---- 例如 403186001[1-3]X分割成40318600110,40318600111,40318600112....40318600139共30个电话号码

 

create or replace package pkg_romania is

  -- Public type declarations
  type varchar2array is table of varchar2(100);

  -- Public function and procedure declarations
  procedure p_parse_phone(ph_number  in varchar2,
                          phone_list out pkg_romania.varchar2array); --电话号码分离

  procedure p_execute;               --执行存储过程
end pkg_romania;
/

 

create or replace package body pkg_romania is

  procedure p_parse_phone(ph_number  in varchar2,
                          phone_list out pkg_romania.varchar2array)

    -- 例如 403186001[1-3]X分割成40318600110,40318600111,40318600112....40318600139共30个电话号码
   is

    v_ora_err varchar2(100);
    v_ora_msg varchar2(4000);

    t_temp_list pkg_romania.varchar2array := pkg_romania.varchar2array();

    v_left_str  varchar2(30);
    v_right_str varchar2(30);

    v_left_bracket  char(1) := '[';
    v_right_bracket char(1) := ']';
    v_wildcard      char(1) := 'X';

    i_phone_num number;
    i_wildcard  number;
    i_start_num number;
    i_end_num   number;
    i_idx_num   number;
    i_idx       number := 1;

  begin

    i_wildcard := instr(ph_number, v_wildcard);
    phone_list := pkg_romania.varchar2array();

    --  if ph_number like '403186001[012]X' or '403186001[0-2]6' then
    if instr(ph_number, v_left_bracket) > 0 then

      v_left_str  := substr(ph_number, 1, instr(ph_number, v_left_bracket) - 1);
      v_right_str := substr(ph_number, instr(ph_number, v_right_bracket) + 1);

      i_start_num := to_number(substr(ph_number,
                                      instr(ph_number, v_left_bracket) + 1,
                                      1));
      i_end_num   := to_number(substr(ph_number,
                                      instr(ph_number, v_right_bracket) - 1,
                                      1));

      for i_idx_num in i_start_num .. i_end_num loop

        t_temp_list.extend;
        t_temp_list(i_idx) := v_left_str || to_char(i_idx_num) || v_right_str;
        i_idx := i_idx + 1;

      end loop;

      -- if ph_number like '403186001[012]XX'
      if i_wildcard > 0 then

        for i in 1 .. t_temp_list.count loop

          v_left_str  := substr(t_temp_list(i),
                                1,
                                instr(t_temp_list(i), v_wildcard) - 1);
          v_right_str := substr(t_temp_list(i),
                                instr(t_temp_list(i), v_wildcard, -1) + 1);

          i_phone_num := to_number(translate(t_temp_list(i), 'X1234567890', '9'));

          for j in 0 .. i_phone_num loop

            phone_list.extend;
            phone_list((i - 1) * (i_phone_num + 1) + j + 1) := v_left_str ||
                                                               lpad(to_char(j),
                                                                    length(to_char(i_phone_num)),
                                                                    '0') ||
                                                               v_right_str;
          end loop;

        end loop;

        return;

      else
        -- if ph_number like 403186001[0-2]6
        phone_list := t_temp_list;
        return;

      end if;

    end if;

    -- if ph_number like '4031860012XX'
    if i_wildcard > 0 then

      v_left_str  := substr(ph_number, 1, instr(ph_number, v_wildcard) - 1);
      v_right_str := substr(ph_number, instr(ph_number, v_wildcard, -1) + 1);

      i_phone_num := to_number(translate(ph_number, 'X1234567890', '9'));

      for j in 0 .. i_phone_num loop
        phone_list.extend;
        phone_list(j + 1) := v_left_str ||
                             lpad(to_char(j), length(to_char(i_phone_num)), '0') ||
                             v_right_str;
      end loop;

      return;

    end if;

  exception
    when others then

      v_ora_err := to_char(sqlcode);
      v_ora_msg := trim(sqlerrm);

      dbms_output.put_line(v_ora_err || ': ' || v_ora_msg);

  end p_parse_phone;

 

procedure p_execute is

  begin
  end p_execute;

end pkg_romania;
/