[20240511]建立10进制转任意进制10tox.sql脚本.txt
--//翻开以前笔记,找到1个脚本10x.sql(改名10tox.sql),可以实现10进制转任意进制.受字符集限制最大64进制.
--//bash shell 64进制编码从0开始. 0-9 , a-z , A-Z.这样仅仅62个编码.还剩下2个.@_,以该编码为基础来实现
--//bash shell 64进制完整的编码如下:
$ 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))
;
--//如果参数2不输入,缺省转换为16进制.
--//如果参数2=32,采用sql_id的编码,实际上就是'0123456789abcdefghijklmnopqrstuvwxyz'字符串里面取消eilo字符. 输出sql_id值.
--//简单验证:
--//63*64*64+62*64+1 = 262017
$ echo $((64#_@1))
262017
SCOTT@test01p> @ 10tox 262017 64
BASE10 BASE64
------------------------------ ------------------------------
262017 _@1
--//15*16*16+11*16+1 = 4017
$ echo $((16#fb1))
4017
SCOTT@test01p> @ 10tox 4017 16
BASE10 BASE16
------------------------------ ------------------------------
4017 fb1
SCOTT@test01p> @ undefparm
SCOTT@test01p> define 2
SP2-0135: symbol 2 is UNDEFINED
SCOTT@test01p> @ 10tox 4017
BASE10 BASE16
------------------------------ ------------------------------
4017 fb1
--//如果没有输入参数2默认16进制.
SCOTT@test01p> @ 10tox 4017 ''
BASE10 BASE16
------------------------------ ------------------------------
4017 fb1
--// 36*62*62+9*62+2 = 138944
$ echo $((62#A92))
138944
SCOTT@test01p> @ 10tox 138944 62
BASE10 BASE62
------------------------------ ------------------------------
138944 A92
--//看看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.