Realize arbitrary conversion of binary, octal, decimal and hexadecimal through functions in Oracle

Description: this article mainly refers to: http://www.eygle.com/archives/2004/06/oracle_howto_convert.html

First, create two basic binary conversion functions:

1. Other decimal systems (2, 8, 16) are converted to decimal

Create Or Replace Function To_Dec (p_Str In Varchar2,-- the number to be converted (2Jing 8pm hexadecimal) p_From_Base In Number Default 16)-- binary Return Number Is i_Num Number Default 0 before conversion; i_Hex Varchar2 (16) Default '0123456789ABCDEFF; Begin For i In 1. Length (p_Str) Loop i_Num: = i_Num * p_From_Base + Instr (i_Hex, Upper (Substr (p_Str, I, 1))-1; End Loop; Return iTunnum; End To_Dec

two。 Convert decimal to other decimal systems (2, 8, 8, 16)

Create Or Replace Function To_Base (p_Dec In Number,-- digits to be converted (decimal) p_Base In Number)-- converted binary Return Varchar2 Is i_Str Varchar2 (255) Default Null; i_Num Number Default paired Decoding; i_Hex Varchar2 (16) Default '0123456789 ABCDEFF; Begin If Trunc (p_Dec) p_Dec Or p_Dec

< 0 Then Raise Program_Error; End If; Loop i_Str := Substr(i_Hex, Mod(i_Num, p_Base) + 1, 1) || i_Str; i_Num := Trunc(i_Num / p_Base); Exit When i_Num = 0; End Loop; Return i_Str; End To_Base; 二、通过上面两个函数，可以将10进制作为转换的中间桥梁。在进行任意进制转换时，都可以先将要转换的进制转为10进 制，然后再通过10进制转换为别的进制。 通过下面函数来实现： Create Or Replace Function Bodh_Convert(p_Str In Varchar2, --要转换的数字（2,8,10,16进制） p_From Number, --转换前的进制 p_To Number) --转换后的进制 Return Varchar2 Is v_Temp Number Default 0; v_Num Varchar2(128) Default Null; Begin If p_From = 2 Then If p_To = 8 Then v_Temp := To_Dec(p_Str, p_From); v_Num := To_Base(v_Temp, p_To); Elsif p_To = 10 Then v_Num := To_Dec(p_Str, p_From); Elsif p_To = 16 Then v_Temp := To_Dec(p_Str, p_From); v_Num := To_Base(v_Temp, p_To); End If; Elsif p_From = 8 Then If p_To = 2 Then v_Temp := To_Dec(p_Str, p_From); v_Num := To_Base(v_Temp, p_To); Elsif p_To = 10 Then v_Num := To_Dec(p_Str, p_From); Elsif p_To = 16 Then v_Temp := To_Dec(p_Str, p_From); v_Num := To_Base(v_Temp, p_To); End If; Elsif p_From = 10 Then If p_To = 2 Then v_Num := To_Base(To_Number(p_Str), p_To); Elsif p_To = 8 Then v_Num := To_Base(To_Number(p_Str), p_To); Elsif p_To = 16 Then v_Num := To_Base(To_Number(p_Str), p_To); End If; Elsif p_From = 16 Then If p_To = 2 Then v_Temp := To_Dec(p_Str, p_From); v_Num := To_Base(v_Temp, p_To); Elsif p_To = 8 Then v_Temp := To_Dec(p_Str, p_From); v_Num := To_Base(v_Temp, p_To); Elsif p_To = 10 Then v_Num := To_Dec(p_Str, p_From); End If; End If; Return v_Num; Exception When Others Then Dbms_Output.Put_Line('请输入正确的参数'); End Bodh_Convert; 三、示例： SQL>

Select bodh_convert ('1111) from dual -2-to-octal BODH_CONVERT ('1111 jewelry 2, 8)-17 SQL > select bodh_convert (' 1111, 2) from dual -2-to-decimal BODH_CONVERT ('1111)-15 SQL > select bodh_convert (' 1111) from dual -2-to-hexadecimal BODH_CONVERT ('1111-- F)