[20240516]建立任意进制转10进制脚本xto10.sql脚本.txt

[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