参考别的进制转换自己写了一个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~25
a~z 97~122 -71= 26~51
0~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