CREATE OR REPLACE FUNCTION inet_aton(ips in varchar) RETURN number AS
rtn number;
r varchar;
arg1 varchar;
arg2 varchar;
arg3 varchar;
arg4 varchar;
BEGIN
r := '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})';
arg1 := '\1';
arg2 := '\2';
arg3 := '\3';
arg4 := '\4';
execute immediate 'select to_number(regexp_replace(ip, '''||r||''', '''||arg1||''')) * 16777216 + to_number(regexp_replace(ip, '''||r||''', '''||arg2||''')) * 65536 + to_number(regexp_replace(ip, '''||r||''', '''||arg3||''')) * 256 + to_number(regexp_replace(ip, '''||r||''', '''||arg4||''')) as ip_number from (select '''||ips||''' as ip from dual)'
into rtn;
return rtn;
END;
/
测试语句:select inet_aton('192.168.133.254') from dual;,结果如下:
[dmdba@192 bin]$ ./disql
disql V8
用户名:SYSDBA
密码:
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间: 9.080(毫秒)
SQL> select inet_aton('192.168.133.254') from dual;
行号 INET_ATON('192.168.133.254')
---------- ----------------------------
1 3232269822
已用时间: 7.416(毫秒). 执行号:3157.
网友评论