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