create or replace procedure get_Area_Phone(i_phoneNum   in varchar2,--传入号码

o_phoneFlag  out varchar2,--返回手机号码1，固话2，其他0

o_resultNum  out varchar2)

is

iv_province varchar2(50);

iv_phoneNum varchar2(50);

iv_count number;

iv_zipCode2 varchar2(50);

iv_zipCode varchar2(50);

v_strNum   VARCHAR2(30);

v_400_phoneNum varchar2(50);

begin

iv_count := 0;

o_phoneFlag := '0';

--判断手机号码还是固话

if REGEXP_LIKE(i_phoneNum,'^1[3458]\d{9}$') then --判断手机号码

v_strNum  := substr(i_phoneNum,0,7);

o_phoneFlag := '1';

select dpn.PROVINCE_NAME into iv_province from DIC_PHONE_NUM dpn WHERE dpn.MOBILE_NUMBER = v_strNum;

elsif REGEXP_LIKE(i_phoneNum,'^(010|02\d|0[3-9]\d{2})?\d{6,8}$') then --判断固话

iv_zipCode2 := substr(i_phoneNum,0,2);

o_phoneFlag := '2';

if (iv_zipCode2 = '01' or iv_zipCode2 = '02' or iv_zipCode2 = '85') then

if REGEXP_LIKE(substr(i_phoneNum,0,3),'^(010|852|853|02\d)$') then

iv_zipCode := substr(i_phoneNum,0,3);

end if;

else

if REGEXP_LIKE(substr(i_phoneNum,0,4),'^(0[3-9]\d{2})$') then

iv_zipCode := substr(i_phoneNum,0,4);

end if;

end if;

if iv_zipCode is not null then

select dpn.PROVINCE_NAME into iv_province from DIC_PHONE_NUM dpn WHERE dpn.area_code = iv_zipCode  and rownum=1;

end if;

end if;

if iv_province is not null then       select dpp.phone_num into o_resultNum from dic_phone_province dpp where dpp.province=iv_province;   end if;   exception    --如果查不到对应的归属地返回 公用号码   when no_data_found then   o_resultNum := '020'; end;