您好,登錄后才能下訂單哦!
Oralce中怎么將64進制轉(zhuǎn)換為10進制,相信很多沒有經(jīng)驗的人對此束手無策,為此本文總結(jié)了問題出現(xiàn)的原因和解決方法,通過這篇文章希望你能解決這個問題。
1.實現(xiàn)函
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進制基數(shù)
A-Z(0-25),a-z(26-51),0-9(52-61),+(62),/(63)
3.實現(xiàn)原理
通過字符對應(yīng)的ASCII值與實際表示的64進制值進行換算,以此來減少判斷分類。換算關(guān)系如下:
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.應(yīng)用
rowid是用64進制來表示的,它的組成:數(shù)據(jù)對象號(6位)+相對文件號(3位)+數(shù)據(jù)塊號(6位)+在數(shù)據(jù)塊中的行數(shù)(3位)。
Oracle包dbms_rowid中提供了使用rowid來查詢其信息的函數(shù),如下所示:
select rowid, dbms_rowid.rowid_object(rowid) object_id, --51366 (AAAMim)AAFAAAAAMAAC 數(shù)據(jù)對象號 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中的行數(shù) from test where rowid = 'AAAMimAAFAAAAAMAAC';
下面這個函數(shù)對上面的函數(shù)改造后可以一次性返回這些信息
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
看完上述內(nèi)容,你們掌握Oralce中怎么將64進制轉(zhuǎn)換為10進制的方法了嗎?如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容,歡迎關(guān)注億速云行業(yè)資訊頻道,感謝各位的閱讀!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。