[20240518]任意进制转换xtoy.sql脚本.txt

[20240518]任意进制转换xtoy.sql脚本.txt

--//前几天尝试改进10进制转任意进制,以及其他进制转10进制脚本,有了这两个脚本就可以通过10进制实现任意进制的转换.

$ 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))
;
--//注:32进制使用oracle sql_id编码.

$ 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 CASE &2 WHEN 16 THEN LOWER ('&&1') ELSE '&&1' END 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进制特殊一些,考虑英文字符大小写问题.

--//两者结合编写代码如下:
$ cat xtoy.sql
column base&&2 format a50
column base&&3 format a50
column base10  format a50

-- 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))
-- ;

column base&&2 format a50
column base&&3 format a50
column base10  format a50

-- 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))
-- ;

    SELECT base&2
          ,LISTAGG
           (
              SUBSTR
              (
                 CASE &&3
                    WHEN 32
                    THEN
                       '0123456789abcdfghjkmnpqrstuvwxyz'
                    ELSE
                       '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ@_'
                 END
                ,MOD (TRUNC (base10n / POWER (&&3, LEVEL - 1)), &&3) + 1
                ,1
              )
           )
           WITHIN GROUP (ORDER BY LEVEL DESC)
              base&&3
          ,TO_CHAR (base10n) base10
      FROM (SELECT CASE &2 WHEN 16 THEN LOWER ('&&1') ELSE '&&1' END base&2
                  ,SUM (n * POWER (&2, p)) base10n
              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')))
CONNECT BY LEVEL <= CEIL (LOG (&&3, base10n + 1))
  GROUP BY base&2, TO_CHAR (base10n);

--//测试看看:
SYS@test> @ xtoy  1f 16 2
BASE16 BASE2 BASE10
------ ----- --------
1f     11111 31

SYS@test> @ xtoy  1f 16 4
BASE16 BASE4 BASE10
------ ----- ------
1f     133   31

SYS@test> @ xtoy  1f 16 8
BASE16 BASE8 BASE10
------ ----- ------
1f     37    31

SYS@test> @ xtoy  _@1 64 10
BASE64 BASE10 BASE10
------ ------ ------
_@1    262017 262017

SYS@test> @ xtoy  262017 10 64
BASE10 BASE64 BASE10
------ ------ ------
262017 _@1    262017

--//看看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

SYS@test> @ xtoy 3154rqzb8xudy 32 16
BASE32         BASE16           BASE10
-------------- ---------------- -------------------
3154rqzb8xudy  309497b7d68ee9be 3500589626208217534

--//d68ee9be = HASH_HEX

SYS@test> @ xtoy d68ee9be 16 10
BASE16         BASE10           BASE10
-------------- ---------------- -------------------
d68ee9be       3599690174       3599690174

SYS@test> @ xtoy 309497b7d68ee9be 16 32
BASE16           BASE32        BASE10
---------------- ------------- -------------------
309497b7d68ee9be 3154rqzb8xudy 3500589626208217534