[20241110]使用xto10.sql脚本遇到的问题.txt

[20241110]使用xto10.sql脚本遇到的问题.txt

--//自己在使用xto10.sql脚本时，输入参数错误，我没有想到竟然执行有结果，例子：

SYS@book> @ xto10 d5f8b304 10
BASE10                         BASE10
------------------------------ ------------------------------
d5f8b304                       136591304

--//按照道理我输入一个16进制数，转换10进制，应该输入如下：

SYS@book> @ xto10 d5f8b304 ''

BASE16                         BASE10
------------------------------ ------------------------------
d5f8b304                       3589845764

SYS@book> @ xto10 d5f8b304 16

BASE16                         BASE10
------------------------------ ------------------------------
d5f8b304                       3589845764

--//注：如果没有参数2缺省16。
--//我前面执行也 成功，相当于

d5f8b304
d->13 f->15 b->11

13*10^7+ 5*10^6+ 15*10^5+ 8*10^4+ 11*10^3+ 304 = 136591304

--//主要问题是无法保证输入参数1的是对应进制X的类型，比如d5f8b304。例子：
SYS@book> @ xto10 z  2
BASE2                          BASE10
------------------------------ ------------------------------
z                              35
--//无法保证z是2进制数。导致结果出现35.
--//也就是先要根据参数2判断参数1是否符合需求，采用继续执行。

--//而16进制限制16进制码表，找不到z出现负数。
SYS@book> @ xto10 z 16
BASE16                         BASE10
------------------------------ ------------------------------
z                              -1

--//做一点改进，限制编码长度，并且如果n=-1,在显示结果时提示错误。
SYS@book> @ xto10 8d 10
BASE10                         BASE10                         ERRMSG
------------------------------ ------------------------------ ------
8d                             79                             bad!
--//注：d 在10进制取不到编码返回-1，这样计算结果时80-1=79.但是我判断里面出现返回-1的情况，ERRMSG显示错误。

SCOTT@book> @ xto10 8d 16
BASE16                         BASE10                         ERRMSG
------------------------------ ------------------------------ ----
8d                             141                            good

--//10tox.sql以及xtoy.sql脚本，也应该存在类似问题，有空也检查看看。

--//源代码修改如下：
$ cat xto10.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name:   xto10.sql
-- Purpose:     decimal convert Ximal
--
-- Author:      lfree
--
-- Usage:
--     @ xto10 <Ximal_value> Ximal
--     convert hexadecimal to decimal
--     @ xto10 <hexadecimal>
--     @ xto10 <hexadecimal> 16
--
--------------------------------------------------------------------------------
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
column errmsg format a6

SELECT CASE &2 WHEN 16 THEN LOWER ('&&1') ELSE '&&1' END base&2, TO_CHAR (SUM (n * POWER (&2, p))) base10,min(decode(n,-1,'bad!','good')) errmsg
--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
                              substr('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ@_',1,&&2)
                        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'));