ip字符串与整型相互转换的函数

    目前网上很多ip字符串与整型相互转换都是用编程语言实现的,下面我试着在数据库中实现这样的功能.

1.字符串ip转换成整型表示

CREATE OR REPLACE Function Ip_Str2long(Strip In String) Return Integer As

  Ip1 Varchar2(10);

  Ip2 Varchar2(10);

  Ip3 Varchar2(10);

  Ip4 Varchar2(10);

  Temp Varchar2(20);

  Result Integer;

Begin

  Temp := Strip;

  Ip1 := Substr(Temp, 1, Instr(Temp, '.', 1, 1) - 1);

  Ip2 := Substr(Temp,
               
                Instr(Temp, '.', 1, 1) + 1,
               
                Instr(Temp, '.', 1, 2) - Length(Ip1) - 2);

  Ip3 := Substr(Temp,
               
                Instr(Temp, '.', 1, 2) + 1,
               
                Instr(Temp, '.', 1, 3) - Length(Ip1) - Length(Ip2) - 3);

  Ip4 := Substr(Temp,
               
                Instr(Temp, '.', 1, 3) + 1,
               
                Length(Temp) - Length(Ip1) - Length(Ip2) - 4);

  Result := To_Number(Ip1) * 16777216 + To_Number(Ip2) * 65536 +
          
            To_Number(Ip3) * 256 + To_Number(Ip4);

  Return(Result);

End Ip_Str2long;

 

Select Ip_Str2long('192.168.0.1') From dual
-----------------------------
3232235521

2.整型ip转换成字符串表示

CREATE OR REPLACE Function Ip_Long2str(Pi_Ip_Address Number)

 Return Varchar2 Is

  l_P1 Number;

  l_P2 Number;

  l_P3 Number;

  l_P4 Number;

Begin

  If (Pi_Ip_Address / Power(2, 24) = 1) Or
   
     (Pi_Ip_Address / Power(2, 24) = 0) Then
 
    l_P1 := Pi_Ip_Address / Power(2, 24);
 
  Else
 
    l_P1 := Substr(To_Char(Pi_Ip_Address / Power(2, 24), 'FM999990.09999999999'),
                  
                   1,
                  
                   Instr((To_Char(Pi_Ip_Address / Power(2, 24),
                                  'FM999990.09999999999')),
                         '.') - 1);
 
  End If;

  If (Bitand(Pi_Ip_Address, 16777215) / Power(2, 16) = 1) Or
   
     (Bitand(Pi_Ip_Address, 16777215) / Power(2, 16) = 0)
 
   Then
 
    l_P2 := Bitand(Pi_Ip_Address, 16777215) / Power(2, 16);
 
  Else
 
    l_P2 := Substr(To_Char(Bitand(Pi_Ip_Address, 16777215) / Power(2, 16),
                          
                           'FM999990.09999999999'),
                  
                   1,
                  
                   Instr(To_Char(Bitand(Pi_Ip_Address, 16777215) /
                                 Power(2, 16),
                                 'FM999990.09999999999'),
                         '.') - 1);
 
  End If;

  If (Bitand(Pi_Ip_Address, 65535) / Power(2, 8) = 1) Or
   
     (Bitand(Pi_Ip_Address, 65535) / Power(2, 8) = 0)
 
   Then
 
    l_P3 := Bitand(Pi_Ip_Address, 65535) / Power(2, 8);
 
  Else
 
    l_P3 := Substr(To_Char(Bitand(Pi_Ip_Address, 65535) / Power(2, 8),
                          
                           'FM999990.09999999999'),
                  
                   1,
                  
                   Instr(To_Char(Bitand(Pi_Ip_Address, 65535) / Power(2, 8),
                                 'FM999990.09999999999'),
                         '.'));
 
  End If;

  l_P4 := Bitand(Pi_Ip_Address, 255);

  Return l_P1 || '.' || l_P2 || '.' || l_P3 || '.' || l_P4;

End;

 

Select Ip_Long2str(3232235521) From dual
----------------------------
192.168.0.1


-- The  End --