[20240516]建立任意进制转10进制脚本xto10.sql脚本.txt
--//bash shell 64进制编码从0开始. 0-9 , a-z , A-Z.这样仅仅62个编码.还剩下2个.@_
--//这样完整的编码如下:
$ base64=$(echo {0..9} {a..z} {A..Z} @ _)
$ echo $base64
0 1 2 3 4 5 6 7 8 9 a b c d e f g h i j k l m n o p q r s t u v w x y z A B C D E F G H I J K L M N O P Q R S T U V W X Y Z @ _
--//定义成bash数组如下:
$ BASE64=($(echo {0..9} {a..z} {A..Z} @ _))
$ echo ${BASE64[*]}
0 1 2 3 4 5 6 7 8 9 a b c d e f g h i j k l m n o p q r s t u v w x y z A B C D E F G H I J K L M N O P Q R S T U V W X Y Z @ _
--//0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ@_
--//实际上最大64进制.代码来自Franck Pachot ,我修改一下支持最大64进制,原始链接已经找不到了.
$ cat 10tox.sql
set term off
column 2 new_value 2
select null "2" from dual where 1=2;
select nvl('&2',16) "2" from dual;
set term on
column base10 format a30
column base&&2 format a30
select to_char('&&1') base10,
listagg(
substr(
case &&2 when 32 then
'0123456789abcdfghjkmnpqrstuvwxyz'
else
'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ@_'
end,
mod(
trunc(&&1/power(&&2,level-1)),
&&2
) + 1 ,
1
)
) within group (order by level desc) base&&2
from dual
connect by level <= ceil(log(&&2,&&1+1))
;
--//耐心阅读,很容易反向实现任意进制转10进制脚本xto10.sql脚本,
$ cat xto10.sql
set term off
column 2 new_value 2
select null "2" from dual where 1=2;
select nvl('&2',16) "2" from dual;
set term on
column base10 format a30
column base&&2 format a30
SELECT '&&1' base&2, TO_CHAR (SUM (n * POWER (&2, p))) base10
FROM ( SELECT INSTR
(
CASE &&2
WHEN 32
THEN
'0123456789abcdfghjkmnpqrstuvwxyz'
WHEN 16
THEN
'0123456789abcdef'
ELSE
'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ@_'
END
,SUBSTR
(
CASE &2 WHEN 16 THEN LOWER ('&&1') ELSE '&&1' END
,LEVEL
,1
)
)
- 1
N
,LENGTH ('&&1') - LEVEL P
,SUBSTR ('&&1', LEVEL, 1) C
FROM DUAL
CONNECT BY LEVEL <= LENGTH ('&&1'));
--//16进制特殊一些,考虑英文字符大小写问题.
$ echo $((62#A92))
138944
SCOTT@test01p> @ xto10 A92 62
BASE62 BASE10
------------------------------ ------------------------------
A92 138944
SCOTT@test01p> @ xto10 1f 16
BASE16 BASE10
------------------------------ ------------------------------
1f 31
SCOTT@test01p> @ undefparm
SCOTT@test01p> define 2
SP2-0135: symbol 2 is UNDEFINED
SCOTT@test01p> @ xto10 1F
BASE16 BASE10
------------------------------ ------------------------------
1F 31
--//支持大小写混合输入.
$ echo $((64#_@1))
262017
SCOTT@test01p> @ xto10 _@1 64
BASE64 BASE10
------------------------------ ------------------------------
_@1 262017
SCOTT@test01p> @ xto10 210 10
BASE10 BASE10
------------------------------ ------------------------------
210 210
--//看看sql_id的计算.
SCOTT@test01p> select * from dept;
DEPTNO DNAME LOC
---------- -------------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@test01p> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
3599690174 3154rqzb8xudy 0 59838 3383998547 d68ee9be 2024-05-15 21:33:53 16777217
SCOTT@test01p> select * from v$db_object_cache where name='select * from dept' and rownum=1
2 @ prxx
==============================
OWNER :
NAME : select * from dept
DB_LINK :
NAMESPACE : SQL AREA
TYPE : CURSOR
SHARABLE_MEM : 16176
LOADS : 1
EXECUTIONS : 1
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 0
INVALIDATIONS : 0
HASH_VALUE : 3599690174
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : VALID
TIMESTAMP : 2024-05-15/21:31:36
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 1
PINNED_TOTAL : 2
PROPERTY :
FULL_HASH_VALUE : 124fbd0eb1f2b39a309497b7d68ee9be
CON_ID : 3
CON_NAME :
ADDR : 000007FF15B216D0
EDITION :
PL/SQL procedure successfully completed.
--//取FULL_HASH_VALUE后16个字符.
SYS@test> @ hex 309497b7d68ee9be
DEC HEX
----------------------------------- --------------------
3500589626208217534.000000 309497B7D68EE9BE
SYS@test> @ 10tox 3500589626208217534 32
BASE10 BASE32
--------------------- ------------------------------
3500589626208217534 3154rqzb8xudy
--//sql_id=3154rqzb8xudy.
SCOTT@test01p> @xto10 309497b7d68ee9be 16
BASE16 BASE10
------------------------------ ------------------------------
309497b7d68ee9be 3500589626208217534
SCOTT@test01p> @ xto10 3154rqzb8xudy 32
BASE32 BASE10
------------------------------ ------------------------------
3154rqzb8xudy 3500589626208217534