Oralce中怎么将64进制转换为10进制

Oralce中怎么将64进制转换为10进制，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

1.实现函

       Create Or Replace Function Sf_To_Deci(Str In Varchar2) Return Number As          Digit Number(32);        Begin          Select Sum(Data)                Into Digit                From (Select (Case                                           When Ascii(Substr(Str, Rownum, 1)) = 43 Then                                                62                                           When Ascii(Substr(Str, Rownum, 1)) = 47 Then                                                63                                           When Ascii(Substr(Str, Rownum, 1)) Between 48 And 57 Then                                                Ascii(Substr(Str, Rownum, 1)) + 4                                           When Ascii(Substr(Str, Rownum, 1)) Between 65 And 90 Then                                                Ascii(Substr(Str, Rownum, 1)) - 65                                           When Ascii(Substr(Str, Rownum, 1)) Between 97 And 122 Then                                                Ascii(Substr(Str, Rownum, 1)) - 71                                           Else                                                Null                                         End) * Power(64, Length(Str) - Rownum) Data                                From Dual                          Connect By Rownum <= Length(Str));          Return Digit;        End;

2.64进制基数

A-Z(0-25)，a-z(26-51)，0-9(52-61)，+(62)，/(63)

3.实现原理

通过字符对应的ASCII值与实际表示的64进制值进行换算，以此来减少判断分类。换算关系如下：

A~Z   65~90        -65     =    0~25a~z   97~122         -71     =    26~510~9   48~57          +4      =    52~61+    43       +19        =    62/    47       +16        =     63

4.应用

rowid是用64进制来表示的，它的组成：数据对象号（6位）+相对文件号（3位）+数据块号（6位）+在数据块中的行数（3位）。

Oracle包dbms_rowid中提供了使用rowid来查询其信息的函数，如下所示：

 select                 rowid,                dbms_rowid.rowid_object(rowid) object_id,  --51366 (AAAMim)AAFAAAAAMAAC 数据对象号                dbms_rowid.rowid_relative_fno(rowid) file_id, --5        AAAMim(AAF)AAAAAMAAC 相对文件号                dbms_rowid.rowid_block_number(rowid) block_id, --12      AAAMimAAF(AAAAAM)AAC 在第几个块                dbms_rowid.rowid_row_number(rowid) num   --2   AAAMimAAFAAAAAM(AAC)在block中的行数        from test where rowid = 'AAAMimAAFAAAAAMAAC';

下面这个函数对上面的函数改造后可以一次性返回这些信息

Create Or Replace Function Rowid_To_Info(Str In Varchar2) Return Varchar2 As          Digit Varchar2(32);          Rest  Varchar2(400);          Str_1 Varchar2(10) := Substr(Str, 1, 6);          Str_2 Varchar2(10) := Substr(Str, 7, 3);          Str_3 Varchar2(10) := Substr(Str, 10, 6);          Str_4 Varchar2(10) := Substr(Str, 16, 3);          Type Str_Varray Is Varray(4) Of Varchar2(10);          Type Rest_Varray Is Varray(4) Of Varchar2(20);          v_Str_Varray  Str_Varray := Str_Varray(Str_1, Str_2, Str_3, Str_4);          v_Rest_Varray Rest_Varray := Rest_Varray('Object_id：','File_id：','Block_id：','Block_num：');        Begin          For i In 1 .. 4 Loop                Select Sum(Data)                  Into Digit                  From (Select (Case                                                 When Ascii(Substr(v_Str_Varray(i), Rownum, 1)) = 43 Then                                                  62                                                 When Ascii(Substr(v_Str_Varray(i), Rownum, 1)) = 47 Then                                                  63                                                 When Ascii(Substr(v_Str_Varray(i), Rownum, 1)) Between 48 And 57 Then                                                  Ascii(Substr(v_Str_Varray(i), Rownum, 1)) + 4                                                 When Ascii(Substr(v_Str_Varray(i), Rownum, 1)) Between 65 And 90 Then                                                  Ascii(Substr(v_Str_Varray(i), Rownum, 1)) - 65                                                 When Ascii(Substr(v_Str_Varray(i), Rownum, 1)) Between 97 And 122 Then                                                  Ascii(Substr(v_Str_Varray(i), Rownum, 1)) - 71                                                 Else                                                  Null                                           End) * Power(64, Length(v_Str_Varray(i)) - Rownum) Data                                  From Dual                                Connect By Rownum <= Length(v_Str_Varray(i)));                If i = 4 Then                  Rest := Rest || v_Rest_Varray(i) || Digit;                Else                  Rest := Rest || v_Rest_Varray(i) || Digit || chr(9);                End If;                    End Loop;          Return Rest;        End;    

查询：

SQL> select rowid_to_info('AAAMimAAFAAAAAMAAC')from dual;        ROWID_TO_INFO('AAAMIMAAFAAAAAMAAC')        --------------------------------------------------------------------------------        Object_id：51366        File_id：5      Block_id：12    Block_num：2

看完上述内容，你们掌握Oralce中怎么将64进制转换为10进制的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注行业资讯频道，感谢各位的阅读！