oracle to_char 进制转换_oracle 进制转换

CREATEORREPLACEPACKAGE pkg_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;

/

CREATEORREPLACEPACKAGE BODY pkg_number_transIS

FUNCTIONf_bin_to_oct(p_strINVARCHAR2)RETURNVARCHAR2IS

----------------------------------------------------------------------------------------------------------------------

-- 对象名称: f_bin_to_oct

-- 对象描述: 二进制转换八进制

-- 输入参数: p_str 二进制字符串

-- 返回结果: 八进制字符串

-- 测试用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual;

-- 备    注: 需要定义f_stragg函数和type_str_agg类型

----------------------------------------------------------------------------------------------------------------------

v_return VARCHAR2(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 二进制字符串

-- 返回结果: 十进制字符串

-- 测试用例: SELECT pkg_number_trans.f_bin_to_dec('11110001010') FROM dual;

----------------------------------------------------------------------------------------------------------------------

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 二进制字符串

-- 返回结果: 十六进制字符串

-- 测试用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual;

-- 备    注: 需要定义f_stragg函数和type_str_agg类型

----------------------------------------------------------------------------------------------------------------------

v_return VARCHAR2(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 八进制字符串

-- 返回结果: 二进制字符串

-- 测试用例: SELECT pkg_number_trans.f_oct_to_bin('3612') FROM dual;

-- 备    注: 需要定义f_stragg函数和type_str_agg类型

----------------------------------------------------------------------------------------------------------------------

v_return VARCHAR2(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 八进制字符串

-- 返回结果: 十进制字符串

-- 测试用例: SELECT pkg_number_trans.f_oct_to_dec('3612') FROM dual;

----------------------------------------------------------------------------------------------------------------------

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 八进制字符串

-- 返回结果: 十六进制字符串

-- 测试用例: SELECT pkg_number_trans.f_oct_to_hex('3612') FROM dual;

----------------------------------------------------------------------------------------------------------------------

v_return VARCHAR2(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 十进制字符串

-- 返回结果: 二进制字符串

-- 测试用例: SELECT pkg_number_trans.f_dec_to_bin('1930') FROM dual;

----------------------------------------------------------------------------------------------------------------------

v_return VARCHAR2(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 十进制字符串

-- 返回结果: 八进制字符串

-- 测试用例: SELECT pkg_number_trans.f_dec_to_oct('1930') FROM dual;

----------------------------------------------------------------------------------------------------------------------

v_return VARCHAR2(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

WHENOTHERSTHEN

RETURNNULL;

ENDf_dec_to_oct;

FUNCTIONf_dec_to_hex(p_intINVARCHAR2)RETURNVARCHAR2IS

----------------------------------------------------------------------------------------------------------------------

-- 对象名称: f_dec_to_oct

-- 对象描述: 十进制转换十六进制

-- 输入参数: p_str 十进制字符串

-- 返回结果: 十六进制字符串

-- 测试用例: SELECT pkg_number_trans.f_dec_to_hex('1930') FROM dual;

----------------------------------------------------------------------------------------------------------------------

v_return VARCHAR2(4000);

BEGIN

SELECTupper(TRIM(to_char(p_int,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')))INTOv_returnFROMdual;

RETURNv_return;

EXCEPTION

WHENOTHERSTHEN

RETURNNULL;

ENDf_dec_to_hex;

FUNCTIONf_hex_to_bin(p_strINVARCHAR2)RETURNVARCHAR2IS

----------------------------------------------------------------------------------------------------------------------

-- 对象名称: f_dec_to_oct

-- 对象描述: 十六进制转换二进制

-- 输入参数: p_str 十六进制字符串

-- 返回结果: 二进制字符串

-- 测试用例: SELECT pkg_number_trans.f_hex_to_oct('78A') FROM dual;

----------------------------------------------------------------------------------------------------------------------

v_return VARCHAR2(4000);

BEGIN

SELECTto_char(to_number(f_stragg(data1)))INTOv_return

FROM(SELECT(CASEupper(substr(p_str, rownum, 1))

WHEN'0'THEN'0000'

WHEN'1'THEN'0001'

WHEN'2'THEN'0010'

WHEN'3'THEN'0011'

WHEN'4'THEN'0100'

WHEN'5'THEN'0101'

WHEN'6'THEN'0110'

WHEN'7'THEN'0111'

WHEN'8'THEN'1000'

WHEN'9'THEN'1001'

WHEN'A'THEN'1010'

WHEN'B'THEN'1011'

WHEN'C'THEN'1100'

WHEN'D'THEN'1101'

WHEN'E'THEN'1110'

WHEN'F'THEN'1111'

END) data1

FROMdual

CONNECTBYrownum <= length(p_str));

RETURNv_return;

EXCEPTION

WHENOTHERSTHEN

RETURNNULL;

ENDf_hex_to_bin;

FUNCTIONf_hex_to_oct(p_strINVARCHAR2)RETURNVARCHAR2IS

----------------------------------------------------------------------------------------------------------------------

-- 对象名称: f_dec_to_oct

-- 对象描述: 十六进制转换八进制

-- 输入参数: p_str 十六进制字符串

-- 返回结果: 八进制字符串

-- 测试用例: SELECT pkg_number_trans.f_hex_to_oct('78A') FROM dual;

----------------------------------------------------------------------------------------------------------------------

v_return VARCHAR2(4000);

v_bin    VARCHAR2(4000);

BEGIN

SELECTpkg_number_trans.f_hex_to_bin(p_str)INTOv_binFROMdual;

SELECTpkg_number_trans.f_bin_to_oct(v_bin)INTOv_returnFROMdual;

RETURNv_return;

EXCEPTION

WHENOTHERSTHEN

RETURNNULL;

ENDf_hex_to_oct;

FUNCTIONf_hex_to_dec(p_strINVARCHAR2)RETURNVARCHAR2IS

----------------------------------------------------------------------------------------------------------------------

-- 对象名称: f_hex_to_dec

-- 对象描述: 十六进制转换十进制

-- 输入参数: p_str 十六进制字符串

-- 返回结果: 十进制字符串

-- 测试用例: SELECT pkg_number_trans.f_hex_to_dec('78A') FROM dual;

----------------------------------------------------------------------------------------------------------------------

v_return  VARCHAR2(4000);

BEGIN

SELECTSUM(data1)INTOv_return

FROM(SELECT(CASEupper(substr(p_str, rownum, 1))

WHEN'A'THEN'10'

WHEN'B'THEN'11'

WHEN'C'THEN'12'

WHEN'D'THEN'13'

WHEN'E'THEN'14'

WHEN'F'THEN'15'

ELSEsubstr(p_str, rownum, 1)

END) * power(16, length(p_str) - rownum) data1

FROMdual

CONNECTBYrownum <= length(p_str));

RETURNv_return;

EXCEPTION

WHENOTHERSTHEN

RETURNNULL;

ENDf_hex_to_dec;

ENDpkg_number_trans;

/

CREATEORREPLACETYPE type_str_aggASOBJECT

(

total VARCHAR2(4000),

STATICFUNCTIONodciaggregateinitialize(sctxINOUTtype_str_agg)

RETURNNUMBER,

MEMBERFUNCTIONodciaggregateiterate

(

SELFINOUTtype_str_agg,

VALUEINVARCHAR2

)RETURNNUMBER,

MEMBERFUNCTIONodciaggregateterminate

(

SELFINtype_str_agg,

returnvalueOUTVARCHAR2,

flagsINNUMBER

)RETURNNUMBER,

MEMBERFUNCTIONodciaggregatemerge

(

SELFINOUTtype_str_agg,

ctx2INtype_str_agg

)RETURNNUMBER

)

/

CREATEORREPLACETYPE BODY type_str_aggIS

STATICFUNCTIONodciaggregateinitialize(sctxINOUTtype_str_agg)

RETURNNUMBERIS

BEGIN

sctx := type_str_agg(NULL);

RETURNodciconst.success;

END;

MEMBERFUNCTIONodciaggregateiterate

(

SELFINOUTtype_str_agg,

VALUEINVARCHAR2

)RETURNNUMBERIS

BEGIN

SELF.total := SELF.total || VALUE;

RETURNodciconst.success;

END;

MEMBERFUNCTIONodciaggregateterminate

(

SELFINtype_str_agg,

returnvalueOUTVARCHAR2,

flagsINNUMBER

)RETURNNUMBERIS

BEGIN

returnvalue := SELF.total;

RETURNodciconst.success;

END;

MEMBERFUNCTIONodciaggregatemerge

(

SELFINOUTtype_str_agg,

ctx2INtype_str_agg

)RETURNNUMBERIS

BEGIN

SELF.total := SELF.total || ctx2.total;

RETURNodciconst.success;

END;

END;

/

CREATEORREPLACEFUNCTIONf_stragg(p_input VARCHAR2)RETURNVARCHAR2

PARALLEL_ENABLE

AGGREGATE USING type_str_agg;

/