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

来源：这里教程网时间：2026-03-03 20:00:49 作者：

[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