Workarounds for IP Address sequencing

Recently encountered in the development of the IP address sorting problems, the ordinary sort by string can not meet the requirements, online access to data, most of the IP address segmentation, and then sort, the method is more complex. Consulting company Daniel, with the definition of type and type body in Oracle, the basic idea is to convert the string type to integer and then compare.

The code is as follows:

CREATE OR REPLACETYPE "Ip_type" asObject (/*------------------------------------------------------------------------------Feature requirements: strings that handle IP formats (such as: 10.67.21.201)  。 Parameter definition: Data Source: Call Method: Call in other languages, special exception in PL/sql:------------------------------------------------------------------------- ---------*/Ipstrvarchar2( the),--IP AddressIpnumint,--the corresponding valueConstructorfunctionIp_type (p_strvarchar2)returnSelf asresult, constructorfunctionIp_type (P_numint)returnSelf asresult, MemberfunctionIncrease (P_numint)returnIp_type, memberfunctionTostringreturn varchar2, memberfunctionTonumberreturn int,   OrderMemberfunctionOrder_fun (P_obj ip_type)return int )

Create or Replacetype body Ip_type is /*------------------------------------------------------------------------------Functional Requirements: The functions of the Ip_type object are implemented. Parameter definition: Data Source: Call Method: Call in other languages, special exception in PL/sql:------------------------------------------------------------------------- ---------*/   /*------------------------------------------------------------------------------Functional Requirements: the implementation of the IP address into the corresponding value--------- -------------------------------------------------------------------------*/ConstructorfunctionIp_type (p_strvarchar2)returnSelf asresult istype T_tab is Table  ofBinary_integerIndex  byBinary_integer;       L_tab T_tab; functionSplit (P_strvarchar2, P_tokenChar:='.')returnT_tab asl_n Pls_integer:= 0; L_idx Pls_integer:= 0; L_argsvarchar2( +) :=P_str;       tab T_tab; begin         ifSUBSTR (L_args,-1,1)<>P_token ThenL_args:=L_args||P_token; End if; Loop L_n:=InStr (L_args, P_token); Exit  when(NVL (L_n,0)= 0); L_IDX:=L_idx+ 1; tab (L_IDX):= LTrim(RTrim(Substr (L_args,1, L_n- 1))); L_args:=substr (L_args, L_n+ 1); EndLoop; returntab; End; beginL_tab:=split (P_STR); ifL_tab.Count=4  ThenSelf . IPSTR:=P_str;  forIinch 1.. L_tab.CountLoop self. Ipnum:=NVL (self. Ipnum,0)+L_tab (i)*Power( the,4-i); EndLoop; End if; return; End; /*------------------------------------------------------------------------------Functional Requirements: The implementation converts the numeric value to the corresponding IP address--------- -------------------------------------------------------------------------*/ConstructorfunctionIp_type (P_numint)returnSelf asresult isL_firstint:= Floor(P_num/Power( the,3)); begin     ifL_first<255  ThenSelf.ipnum:=P_num; Self. IPSTR:=L_first||'.'||                 Floor(MoD (P_num,Power( the,3))/Power( the,2))||'.'||                 Floor(MoD (P_num,Power( the,2))/Power( the,1))||'.'||                 Floor(MoD (P_num,Power( the,1))); ElseSelf.ipnum:=0; Self. IPSTR:='0.0.0.0'; End if; return; End; /*------------------------------------------------------------------------------     ---------------------------- ------------------------------------------------------*/memberfunctionIncrease (P_numint)returnIp_type is   begin      returnIp_type (Self.ipnum+p_num); End; MemberfunctionTostringreturn varchar2    is   begin    returnIpstr; End; /*------------------------------------------------------------------------------Functional Requirements: Get the value of the current IP address----------- -----------------------------------------------------------------------*/memberfunctionTonumberreturn int    is   begin    returnIpnum; End; /*------------------------------------------------------------------------------     ----------------------------- -----------------------------------------------------*/   OrderMemberfunctionOrder_fun (P_obj ip_type)return int    is   begin      return  Sign(self.) Ipnum-P_obj.   Ipnum); End; End;

Specific Use method:
Select Ip_type (' 10.67.2.101 '). Ipnum,ip_type (' 10.67.2.101 '). Ipstr from dual

return Result: 172163685,10.67.2.101

Workarounds for IP Address sequencing