从IP数据库查找指定IP信息

 感谢纯真IP数据库作者及维护者提供的数据和支持工作

感谢IPLook作者开发的工具

本文word版及相关工具已包含在该网址，可免积分下载 
http://download.csdn.net/detail/china200_ok/9785544

1.    修改列名称，数据类型，如图1，保存时可能会有点慢，请耐心等待

（图1）

2.    由于根据字符串查找某一个指定的IP地址不方便，我这里准备把 strStart，strEnd转为bigint类型，新加的dwStart，dwEnd设置默认值0，如图2

（图2）

3.    写一个点分十进制IP转BIGINT的函数“GSF_GR_IpToBigint”

----------------------------------------------------------------------------------------------------
 
USE master
GO
 
IF EXISTS(SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[GSF_GR_IpToBigint]')and OBJECTPROPERTY(ID, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[GSF_GR_IpToBigint]
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_NULLS ON
GO
 
----------------------------------------------------------------------------------------------------
 --- 取整数值
CREATE FUNCTION [dbo].[GSF_GR_IpToBigint](
         @strIP AS NVARCHAR(20))
RETURNS  BIGINT
AS 
BEGIN
 
         DECLARE@rv BIGINT,
                            @o1 BIGINT,
                            @o2 BIGINT,
                            @o3 BIGINT,
                            @o4 BIGINT
          
                   -- @strIP=N'1.2.3.4'
                   -- select @o1,@o2,@o3,@o4
                   -- 输出1 2 3 4
                  
                   SELECT
                            @o1 = CONVERT(BIGINT, PARSENAME(@strIP, 4)),
                            @o2 = CONVERT(BIGINT, PARSENAME(@strIP, 3)),
                            @o3 = CONVERT(BIGINT, PARSENAME(@strIP, 2)),
                            @o4 = CONVERT(BIGINT, PARSENAME(@strIP, 1))
          
                   SET@rv=0
                   IF (@o1>=0 AND @o1<=255) AND(@o2>=0 AND @o2<=255) AND(@o3>=0 AND @o3<=255) AND(@o4>=0 AND @o4<=255)
                   BEGIN       
                            --SET @rv =(@o4*256*256*256) + (@o3*256*256) + (@o2*256) + (@o1)
                            SET @rv =(@o1*256*256*256) +(@o2*256*256) +(@o3*256) +(@o4)
                   END
                  
         return @rv
END

4.    然后使用update语句把strStart转为dwStart，把strEnd转为dwEnd，如图3

UPDATE[THExternalDB].[dbo].[IPData]
   SET [dwStart] = master.dbo.GSF_GR_IpToBigint(strStart)
      ,[dwEnd] = master.dbo.GSF_GR_IpToBigint(strEnd)
GO

（图3）

5.    上述步骤准备完毕，再写一个查询IP归属地的存储过程“GSP_GP_QueryIPLocation”

----------------------------------------------------------------------------------------------------
USE THExternalDB
GO
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID =OBJECT_ID(N'[dbo].[GSP_GP_QueryIPLocation]') and OBJECTPROPERTY(ID,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GSP_GP_QueryIPLocation]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 
----------------------------------------------------------------------------------------------------
 
-- 查询IP归属地
CREATE PROC GSP_GP_QueryIPLocation
         @strClientIPNVARCHAR(15)                                          --连接地址
WITH ENCRYPTION AS
 
-- 属性设置
SET NOCOUNT ON
 
-- 执行逻辑
BEGIN
 
         DECLARE@dwClientIP BIGINT
         SET@dwClientIP=master.dbo.GSF_GR_IpToBigint(@strClientIP)
 
         SELECT * FROM[THExternalDB].[dbo].[IPData] WHERE @dwClientIP>=dwStart AND@dwClientIP<=dwEnd
END
RETURN 0
GO
----------------------------------------------------------------------------------------------------

6.    执行测试，测试结果如图4

USE [THExternalDB]
GO
DECLARE     @return_valueint
EXEC @return_value =[dbo].[GSP_GP_QueryIPLocation]
                   @strClientIP= N'183.16.87.205'
SELECT       'ReturnValue' = @return_value
GO

（图4）