最近有个朋友说他要做的人员注册量的统计，有两张表，一张是用户注册表，一张是IP段对应城市表。需要根据用户注册时的IP查询到对应的城市，从而知道该城市有多少人注册。
其实没什么，关键是IP地址和IP段的匹配问题。
一开始，我使用的是BETWEEN，结果显然是不行的。
最后我想到用函数直接将192.168.0.1形式的IP按256进制转为数字，然后进行between(或者"<"AND">")。
其实想到方法，实现就简单的多了。下面把函数贴上来，有需要的朋友可以偷偷懒 ^_^
1、用户注册表
???? create table SINO_USER
(
? ID????????? NUMBER(19),
? USERNAME??? VARCHAR2(255),
? UPDATE_TIME TIMESTAMP(6),
? IP????????? VARCHAR2(255)
)
2、IP地址段表
create table SINO_IP
(
? ID?????? NUMBER(19),
? IP_BEGIN VARCHAR2(255),
? CITY???? VARCHAR2(255),
? IP_END?? VARCHAR2(255)
)
注：这两张表是我简化了，千万别以为是生产库的表.....
3、创建函数
create or replace function f_ip2number(ip in varchar2) return varchar2 as
v_ip_1 number;
v_ip_2 number;
v_ip_3 number;
v_ip_4 number;
v_result number;
begin
select to_number(substr(ip,1,instr(ip,'.',1,1)-1)),
?????? to_number(substr(ip,instr(ip,'.',1,1)+1,instr(ip,'.',1,2)-instr(ip,'.',1,1)-1)),
?????? to_number(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3)-instr(ip,'.',1,2)-1)),
?????? to_number(substr(ip,instr(ip,'.',1,3)+1,length(ip)-instr(ip,'.',1,3))) into v_ip_1,v_ip_2,v_ip_3,v_ip_4
? from dual;
v_result := v_ip_1 * 256*256*256 + v_ip_2 * 256*256 + v_ip_3 * 256 + v_ip_4;
return v_result;
end;
4、查询的SQL
select *
? from sino_user t, sino_ip t1
?where f_ip2number(t.ip) between f_ip2number(t1.ip_begin) and
?????? f_ip2number(t1.ip_end)
?? ?IP??????????????????????????IP_BEGIN?????????????????? IP_END????????????????? CITY
1?200.220.0.10??????? 200.220.0.1?????????? 255.255.255.255??????? 城市5
2?199.168.200.21??? 192.168.255.255???200.168.105.10????????? 城市4
3?199.168.200.20??? 192.168.255.255?? 200.168.105.10????????? 城市4
4?192.168.200.20??? 192.168.21.1???????? 192.168.225.10????????? 城市3
5?192.168.1.21??????? 192.168.1.1?????????? 192.168.1.35????????????? 城市2
6?192.168.1.20??????? 192.168.1.1?????????? 192.168.1.35????????????? 城市2
7?192.168.0.50??????? 192.168.0.1?????????? 192.168.0.255??????????? 城市1
相信大家都注意到问题在哪里了，对了，执行效率问题。
解决方法：
方法一、加冗余字段
在两个表都加上冗余字段，统计查询的时候直接根据冗余字段来查询；
方法二、加函数索引
如sino_user表的IP字段：CREATE INDEX IDX_SINO_USER_IP ON SINO_USER(F_IP2NUMBER(IP));
如果你按照我上面说的创建索引，ORACLE会报错的，具体什么错我忘了，大概是函数没有准备好之类的。
这是因为ORACLE对于自定义函数建索引是有规定的。
回到函数的创建：
create or replace function f_ip2number(ip in varchar2) return varchar2 as
这么来创建是不能用来加索引的，需要加DETERMINISTIC
create or replace function f_ip2number(ip in varchar2) return varchar2 DETERMINISTIC as
OK，现在再来创建索引就OK了。
哈哈，很简单吧。