[20240511]建立10进制转任意进制10tox.sql脚本.txt

[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.