[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