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;
/
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;
/