判断IP是否在一个范围内.docxVIP

createorreplacefunction IP_TEST(IP invarchar2) returnNUMBERisResultNUMBER :=0; KSIP varchar2(100); JSIP varchar2(100);type cursor_t isrefcursor;emp_cur cursor_t;beginopen emp_cur forselect KSDZ,JSDZ from XXPT_IPXX_JCXX;loopfetch emp_cur into KSIP,JSIP; exitwhen emp_cur%notfound;if IP_JUDGE(IP,KSIP,JSIP)=1thenResult :=1;endif;endloop;close emp_cur;returnResult;end IP_TEST;CREATEORREPLACEFUNCTION BIT_TO_NUMBER(P_BIN INVARCHAR2) RETURNNUMBERAS V_SQL VARCHAR2(30000) := SELECT BIN_TO_NUM(; V_RETURN NUMBER;BEGINIF LENGTH(P_BIN) = 256THEN RAISE_APPLICATION_ERROR(-20001, INPUT BIN TOO LONG!);ENDIF;IF LTRIM(P_BIN, 01) ISNOTNULLTHEN RAISE_APPLICATION_ERROR(-20002, INPUT STR IS NOT VALID BIN VALUE!);ENDIF;FOR I IN1 .. LENGTH(P_BIN) LOOP V_SQL := V_SQL || SUBSTR(P_BIN, I, 1) || ,;ENDLOOP; V_SQL := RTRIM(V_SQL, ,) || ) FROM DUAL;EXECUTEIMMEDIATE V_SQLINTO V_RETURN;RETURN V_RETURN;END;createorreplacefunction f_dec_to_bin(v_num number) returnvarcharis v_rtn varchar(2000); v_n1 number; v_n2 number;begin v_n1 := v_num;loop v_n2 := mod(v_n1, 2); v_n1 := trunc(v_n1 / 2); v_rtn := to_char(v_n2) || v_rtn;exitwhen v_n1 = 0;endloop;case length(v_rtn)when1then v_rtn :=0000000||v_rtn;when2then v_rtn :=000000||v_rtn;when3then v_rtn :=00000||v_rtn;when4then v_rtn :=0000||v_rtn;when5then v_rtn :=000||v_rtn;when6then v_rtn :=00||v_rtn;when7then v_rtn :=0||v_rtn;when8then v_rtn :=v_rtn;endcase;return v_rtn;end f_dec_to_bin;createorreplacefunction IP_JUDGE(IP invarchar2,KSIP invarchar2,JSIP invarchar2)returnNumberis return1 Number :=0; i INT := 1; str VARCHAR2 (4000);--定义字符串数组类型typearrayisvarray(4) ofvarchar2(20); --一维数组，字符串类型 IP_array array:=array(00,00,00,00); KSIP_array array:=array(00,00,00,00); JSIP_array array:=array(00,00,00,00); --定义整数数组类型type array2 isvarray(4) ofINT; --一维数组，整数类型 IP_array2 array2:=array2(0,0,0,0); KSIP_array2 array2:=a