最近,工作中需要查看某些字段的二进制格式,索性写了一些进制相互转换的函数,以方便调用。
记录下这些代码,以备使用。
create or replace package pkg_digit_conv as --将字符串转换为其在数据库中的二进制存储格式 function fun_str2bin(par_str in varchar2) return varchar2; --将二进制转换为10进制 function fun_bin2dec(par_bin in varchar2) return varchar2; --将二进制转换为8进制 function fun_bin2oct(par_bin in varchar2) return varchar2; --将二进制转换为16进制 function fun_bin2hex(par_bin in varchar2) return varchar2; --将10进制转换为二进制 function fun_dec2bin(par_dec in varchar2) return varchar2; --将8进制转换为二进制 function fun_oct2bin(par_oct in varchar2) return varchar2; --将16进制转换为二进制 function fun_hex2bin(par_hex in varchar2) return varchar2; end; create or replace package body pkg_digit_conv as --将字符串转换为其在数据库中的二进制存储格式 function fun_str2bin(par_str in varchar2) return varchar2 as v_strlen pls_integer; v_detemp number; v_bintemp varchar2(20); v_retval varchar2(1000); begin select length(par_str) into v_strlen from dual; for i in 1 .. v_strlen loop select to_number(ascii(substr(par_str,i,1))) into v_detemp from dual; select fun_dec2bin(v_detemp) into v_bintemp from dual; v_retval :=nvl(v_retval,'')|| v_bintemp; end loop; return v_retval; end; --将二进制转换为10进制 function fun_bin2dec(par_bin in varchar2) return varchar2 as v_retval varchar2(100); begin select sum(d) into v_retval from ( select substr(par_bin,rownum,1)*power(2,length(par_bin)-rownum) d from dual connect by rownum <= length(par_bin) ); return v_retval; end; --将二进制转换为8进制 function fun_bin2oct(par_bin in varchar2) return varchar2 as v_octlen pls_integer; v_octtemp varchar2(3); v_retemp varchar2(1); v_retval varchar2(1000); begin select ceil(length(par_bin)/3) into v_octlen from dual; for i in 1..v_octlen loop if(3*i<=length(par_bin)) then select substr(par_bin,0-3*i,3) into v_octtemp from dual; else select substr(par_bin,0-length(par_bin),length(par_bin)-3*i+3) into v_octtemp from dual; end if; select fun_bin2dec(v_octtemp) into v_retemp from dual; v_retval := v_retemp||nvl(v_retval,''); end loop; return v_retval; end; --将二进制转换为16进制 function fun_bin2hex(par_bin in varchar2) return varchar2 as v_hexlen pls_integer; v_hextemp varchar2(4); v_retemp varchar2(1); v_retval varchar2(1000); begin select ceil(length(par_bin)/4) into v_hexlen from dual; for i in 1..v_hexlen loop if(4*i<=length(par_bin)) then select substr(par_bin,0-4*i,4) into v_hextemp from dual; else select substr(par_bin,0-length(par_bin),length(par_bin)-4*i+4) into v_hextemp from dual; select substr('000'||v_hextemp,-4,4) into v_hextemp from dual; end if; case v_hextemp WHEN '0000' THEN v_retemp :='0' ; WHEN '0001' THEN v_retemp :='1' ; WHEN '0010' THEN v_retemp :='2' ; WHEN '0011' THEN v_retemp :='3' ; WHEN '0100' THEN v_retemp :='4' ; WHEN '0101' THEN v_retemp :='5' ; WHEN '0110' THEN v_retemp :='6' ; WHEN '0111' THEN v_retemp :='7' ; WHEN '1000' THEN v_retemp :='8' ; WHEN '1001' THEN v_retemp :='9' ; WHEN '1010' THEN v_retemp :='A' ; WHEN '1011' THEN v_retemp :='B' ; WHEN '1100' THEN v_retemp :='C' ; WHEN '1101' THEN v_retemp :='D' ; WHEN '1110' THEN v_retemp :='E' ; else v_retemp :='F' ; end case; v_retval := v_retemp||nvl(v_retval,''); end loop; return v_retval; end; --将10进制转换为二进制 function fun_dec2bin(par_dec in varchar2) return varchar2 as yushu number; retemp varchar2(1); retval varchar2(1000); begin select to_number(par_dec) into yushu from dual; while yushu >0 loop select mod(yushu,2) into retemp from dual; retval := retemp || nvl(retval,''); select trunc(yushu/2) into yushu from dual; end loop; return retval; end; --将8进制转换为二进制 function fun_oct2bin(par_oct in varchar2) return varchar2 as v_octlen pls_integer; v_octchar varchar2(1); v_dectemp number := 0; v_retval varchar2(1000); begin select length(par_oct) into v_octlen from dual; for i in 1..v_octlen loop v_dectemp := v_dectemp + to_number(substr(par_oct,i,1))*power(8,v_octlen-i); end loop; select fun_dec2bin(to_char(v_dectemp)) into v_retval from dual; return v_retval; end; --将16进制转换为二进制 function fun_hex2bin(par_hex in varchar2) return varchar2 as v_hexlen pls_integer; v_dectemp number; begin select length(par_hex) into v_hexlen from dual; select to_number(par_hex,lpad('x',v_hexlen,'x')) into v_dectemp from dual; return fun_dec2bin(v_dectemp); end; end;
查看本栏目更多精彩内容:http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/
以上是小编为您精心准备的的内容,在的博客、问答、公众号、人物、课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索二进制
, 进制
, return
, function
, varchar2
varchar
二进制八进制十六进制、二进制转八进制、二进制转八进制算法、二进制转换八进制、二进制转换为八进制,以便于您获取更多的相关知识。
时间: 2024-08-30 11:05:22