Oracle中的二进制八进制十进制十六进制相互转换函数.docx
- 文档编号:3362436
- 上传时间:2022-11-22
- 格式:DOCX
- 页数:10
- 大小:16.38KB
Oracle中的二进制八进制十进制十六进制相互转换函数.docx
《Oracle中的二进制八进制十进制十六进制相互转换函数.docx》由会员分享,可在线阅读,更多相关《Oracle中的二进制八进制十进制十六进制相互转换函数.docx(10页珍藏版)》请在冰豆网上搜索。
Oracle中的二进制八进制十进制十六进制相互转换函数
今天在网上看到一篇关于在oracle中对各种进制数进行转换的帖子,觉得不错,也比较全面,几乎涵盖了经常用到的所有转换。
转过来学习一下,也方便以后查询。
==================================================================================
大家经常遇到进制转换的问题,网上搜到的转换只是部分十进制与其它进制的转换,所以我把自己写的转换函数提供给大家,4种进制共12个转换函数,虽然有的转换直接使用to_char()和to_number()就可以实现,但我还是把它们整理到一起,使用和查找都方便。
部分函数需要先创建type_str_agg类型和f_stragg函数才能使用,这两个对象的代码也附在之后。
CREATEORREPLACEPACKAGEpkg_number_transIS
FUNCTIONf_bin_to_oct(p_strINVARCHAR2)RETURNVARCHAR2;
FUNCTIONf_bin_to_dec(p_strINVARCHAR2)RETURNVARCHAR2;
FUNCTIONf_bin_to_hex(p_strINVARCHAR2)RETURNVARCHAR2;
FUNCTIONf_oct_to_bin(p_strINVARCHAR2)RETURNVARCHAR2;
FUNCTIONf_oct_to_dec(p_strINVARCHAR2)RETURNVARCHAR2;
FUNCTIONf_oct_to_hex(p_strINVARCHAR2)RETURNVARCHAR2;
FUNCTIONf_hex_to_bin(p_strINVARCHAR2)RETURNVARCHAR2;
FUNCTIONf_hex_to_oct(p_strINVARCHAR2)RETURNVARCHAR2;
FUNCTIONf_hex_to_dec(p_strINVARCHAR2)RETURNVARCHAR2;
FUNCTIONf_dec_to_bin(p_intINVARCHAR2)RETURNVARCHAR2;
FUNCTIONf_dec_to_oct(p_intINVARCHAR2)RETURNVARCHAR2;
FUNCTIONf_dec_to_hex(p_intINVARCHAR2)RETURNVARCHAR2;
ENDpkg_number_trans;
/
CREATEORREPLACEPACKAGEBODYpkg_number_transIS
FUNCTIONf_bin_to_oct(p_strINVARCHAR2)RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_bin_to_oct
--对象描述:
二进制转换八进制
--输入参数:
p_str二进制字符串
--返回结果:
八进制字符串
--测试用例:
SELECTpkg_number_trans.f_bin_to_oct('11110001010')FROMdual;
--备 注:
需要定义f_stragg函数和type_str_agg类型
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
v_bin:
=substr('00'||p_str,-3*ceil(length(p_str)/3));
SELECTf_stragg(data1)INTOv_return
FROM(SELECT(CASEupper(substr(v_bin,(rownum-1)*3+1,3))
WHEN'000'THEN'0'
WHEN'001'THEN'1'
WHEN'010'THEN'2'
WHEN'011'THEN'3'
WHEN'100'THEN'4'
WHEN'101'THEN'5'
WHEN'110'THEN'6'
WHEN'111'THEN'7'
END)data1
FROMdual
CONNECTBYrownum<=length(v_bin)/3);
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_bin_to_oct;
FUNCTIONf_bin_to_dec(p_strINVARCHAR2)RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_bin_to_dec
--对象描述:
二进制转换十进制
--输入参数:
p_str二进制字符串
--返回结果:
十进制字符串
--测试用例:
SELECTpkg_number_trans.f_bin_to_dec('11110001010')FROMdual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECTSUM(data1)INTOv_return
FROM(SELECTsubstr(p_str,rownum,1)*power(2,length(p_str)-rownum)data1
FROMdual
CONNECTBYrownum<=length(p_str));
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_bin_to_dec;
FUNCTIONf_bin_to_hex(p_strINVARCHAR2)RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_bin_to_hex
--对象描述:
二进制转换十六进制
--输入参数:
p_str二进制字符串
--返回结果:
十六进制字符串
--测试用例:
SELECTpkg_number_trans.f_bin_to_oct('11110001010')FROMdual;
--备 注:
需要定义f_stragg函数和type_str_agg类型
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
v_bin:
=substr('000'||p_str,-4*ceil(length(p_str)/4));
SELECTf_stragg(data1)INTOv_return
FROM(SELECT(CASEupper(substr(v_bin,(rownum-1)*4+1,4))
WHEN'0000'THEN'0'
WHEN'0001'THEN'1'
WHEN'0010'THEN'2'
WHEN'0011'THEN'3'
WHEN'0100'THEN'4'
WHEN'0101'THEN'5'
WHEN'0110'THEN'6'
WHEN'0111'THEN'7'
WHEN'1000'THEN'8'
WHEN'1001'THEN'9'
WHEN'1010'THEN'A'
WHEN'1011'THEN'B'
WHEN'1100'THEN'C'
WHEN'1101'THEN'D'
WHEN'1110'THEN'E'
WHEN'1111'THEN'F'
END)data1
FROMdual
CONNECTBYrownum<=length(v_bin)/4);
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_bin_to_hex;
FUNCTIONf_oct_to_bin(p_strINVARCHAR2)RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_oct_to_bin
--对象描述:
八进制转换二进制
--输入参数:
p_str八进制字符串
--返回结果:
二进制字符串
--测试用例:
SELECTpkg_number_trans.f_oct_to_bin('3612')FROMdual;
--备 注:
需要定义f_stragg函数和type_str_agg类型
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2(4000);
BEGIN
SELECTto_char(to_number(f_stragg(data1)))INTOv_return
FROM(SELECT(CASEupper(substr(p_str,rownum,1))
WHEN'0'THEN'000'
WHEN'1'THEN'001'
WHEN'2'THEN'010'
WHEN'3'THEN'011'
WHEN'4'THEN'100'
WHEN'5'THEN'101'
WHEN'6'THEN'110'
WHEN'7'THEN'111'
END)data1
FROMdual
CONNECTBYrownum<=length(p_str));
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_oct_to_bin;
FUNCTIONf_oct_to_dec(p_strINVARCHAR2)RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_oct_to_dec
--对象描述:
八进制转换十进制
--输入参数:
p_str八进制字符串
--返回结果:
十进制字符串
--测试用例:
SELECTpkg_number_trans.f_oct_to_dec('3612')FROMdual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECTSUM(data1)INTOv_return
FROM(SELECTsubstr(p_str,rownum,1)*power(8,length(p_str)-rownum)data1
FROMdual
CONNECTBYrownum<=length(p_str));
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_oct_to_dec;
FUNCTIONf_oct_to_hex(p_strINVARCHAR2)RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_oct_to_bin
--对象描述:
八进制转换十六进制
--输入参数:
p_str八进制字符串
--返回结果:
十六进制字符串
--测试用例:
SELECTpkg_number_trans.f_oct_to_hex('3612')FROMdual;
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
SELECTpkg_number_trans.f_oct_to_bin(p_str)INTOv_binFROMdual;
SELECTpkg_number_trans.f_bin_to_hex(v_bin)INTOv_returnFROMdual;
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_oct_to_hex;
FUNCTIONf_dec_to_bin(p_intINVARCHAR2)RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_dec_to_bin
--对象描述:
十进制转换二进制
--输入参数:
p_str十进制字符串
--返回结果:
二进制字符串
--测试用例:
SELECTpkg_number_trans.f_dec_to_bin('1930')FROMdual;
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2(4000);
v_hex VARCHAR2(4000);
BEGIN
SELECTpkg_number_trans.f_dec_to_hex(p_int)INTOv_hexFROMdual;
SELECTpkg_number_trans.f_hex_to_bin(v_hex)INTOv_returnFROMdual;
RETURNv_return;
EXCEPTION
WHENOTHERSTHEN
RETURNNULL;
ENDf_dec_to_bin;
FUNCTIONf_dec_to_oct(p_intINVARCHAR2)RETURNVARCHAR2IS
----------------------------------------------------------------------------------------------------------------------
--对象名称:
f_dec_to_oct
--对象描述:
十进制转换八进制
--输入参数:
p_str十进制字符串
--返回结果:
八进制字符串
--测试用例:
SELECTpkg_number_trans.f_dec_to_oct('1930')FROMdual;
----------------------------------------------------------------------------------------------------------------------
v_returnVARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
SELECTpkg_number_trans.f_dec_to_bin(p_int)INTOv_binFROMdual;
v_bin:
=substr('00'||v_bin,-3*ceil(length(v_bin)/3));
SELECTf_stragg(data1)INTOv_return
FROM(SELECT(CASEupper(substr(v_bin,(rownum-1)*3+1,3))
WHEN'000'THEN'0'
WHEN'001'THEN'1'
WHEN'010'THEN'2'
WHEN'011'THEN'3'
WHEN'100'THEN'4'
WHEN'101'THEN'5'
WHEN'110'THEN'6'
WHEN'111'THEN'7'
END)data1
FROMdual
CONNECTBYrownum<=length(v_bin)/3);
RETURNv_return;
EXCEPTION
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 中的 二进制 八进制 十进制 十六进制 相互 转换 函数
![提示](https://static.bdocx.com/images/bang_tan.gif)