--查看过程、函数视图
dba_source user_source all_source
--删除存储过程
drop procedure SP_TMP;
drop function fun_tmp;
---函数
to_char:转换数据这字符串,也可以把10进制转换为16进制(注意xx的个数)。
SQL> select to_char(100,'xx') from dual; TO_ --- 64
to_number:字符串转为数字,也可以16进制转为10进制。
SQL> select to_number('1f','xx') from dual; TO_NUMBER('1F','XX') -------------------- 31
substr:从字符串截取字符。可以从0或1开始,结果一样。
SQL> select substr('abcdefg',1,2) from dual; SU -- ab SQL> select substr('abcdefg',0,2) from dual; SU -- ab
-------------------------------------------------------------------- --ip转换为数字函数 -------------------------------------------------------------------- create or replace function fun_ipconvernum(v_ip in varchar2) return number as v_tmp varchar2(2); v_all varchar2(8); v_tow varchar2(3); v_chkn number; v_chkt number; begin v_tmp:='';v_chkn:=0;v_chkt:=1;v_all:=''; for i in 1..3 loop v_chkn:=instr(v_ip,'.',v_chkn+1); v_tow:=substr(v_ip,v_chkt,v_chkn-v_chkt); v_tmp:=trim(to_char(v_tow,'xx')); if length(v_tmp)=1 then v_all:=v_all || lpad(v_tmp,2,'0'); else v_all:=v_all || v_tmp; end if; v_chkt:=v_chkn+1; if i=3 then v_tow:=substr(v_ip,v_chkt,length(v_ip)-v_chkn); v_tmp:=trim(to_char(v_tow,'xx')); if length(v_tmp)=1 then v_all:=v_all || lpad(v_tmp,2,'0'); else v_all:=v_all || v_tmp; end if; end if; end loop; --dbms_output.put_line(v_all); return to_number(v_all,'xxxxxxxxxx'); end; ------------------------- set serveroutput on exec sp_ipconvernum('192.168.8.4') select fun_ipconvernum('255.255.255.255') from dual; -------------------------------------------------------------------- --数字转换为ip函数 -------------------------------------------------------------------- create or replace function fun_numconverip(v_nip number) return varchar2 as v_ip varchar2(32); v_iphex varchar2(8); begin v_ip:=''; v_iphex:=trim(to_char(v_nip,'xxxxxxxxxx')); for i in 1..4 loop if i=4 then v_ip:=v_ip||to_number(substr(v_iphex,(i-1)*2+1,2),'xxx'); else v_ip:=v_ip||to_number(substr(v_iphex,(i-1)*2+1,2),'xxx')||'.'; end if; end loop; return v_ip; end; ----------------------------------------------------------------- select fun_numconverip(3232237572) from dual; -----------------------------------------------------------------