Oracle自定义一个把 IP 地址转成整数的函数

CREATE OR REPLACE FUNCTION fn_ipaddr_to_num (p_ipaddr VARCHAR2)
    RETURN NUMBER
IS
    v_ret NUMBER := 0;
    k INT := 3;
    j INT := 0;
    i INT := 1;
    len INT := 0;
    len1 INT := 0;
    str VARCHAR2 (4000);
    str_split ty_str_split := ty_str_split ();
BEGIN
    len := LENGTH (p_ipaddr);

    IF p_ipaddr IS NULL OR len = 0
    THEN
        RETURN 0;
    ELSE
        WHILE j < len
        LOOP
            j := INSTR (p_ipaddr, '.', i);

            IF j = 0
            THEN
                j := len;
                str := SUBSTR (p_ipaddr, i);
                v_ret := v_ret + (TO_NUMBER (str) * POWER (256, k));

                IF i >= len
                THEN
                    EXIT;
                END IF;
            ELSE
                str := SUBSTR (p_ipaddr, i, j - i);
                i := j + 1;
                v_ret := v_ret + (TO_NUMBER (str) * POWER (256, k));
            END IF;

            k := k - 1;
        END LOOP;
    END IF;

    RETURN v_ret;
EXCEPTION
WHEN OTHERS
THEN
    RAISE;
END fn_ipaddr_to_num;
/


©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页