[20240930]建立完善calcz.sql脚本.txt

--//tpt脚本有1个calc.sql脚本，可以做一些10，16进制计算，带入参数必须显示说明10，16进制，我利用hd.sql脚本代码生成更加灵活
--//的代码，自己也写一个看看。

1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试：
SCOTT@book01p> @ calc xa0 + 10

                                DEC                  HEX
----------------------------------- --------------------
                         170.000000                   AA

SCOTT@book01p> @ calc xa0 + a
    to_number(substr('a',instr(upper('a'),'X')+1), p3.format) calc_dec,
              *
ERROR at line 22:
ORA-01722: invalid number

--//calc代码如法识别a表示16进制。

3.代码如下：
$ cat calcz.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name:   calcz.sql
-- Purpose:     Basic calculator and dec/hex converter
--
-- Author:      lfree
--
-- Usage:       @calcz <num1> <operation> <num2>
--              @calcz 10 + 10
--              @calcz a + 0x10
--              @calcz 0xFFFF - 0x5F
--              @calcz xBB * 1234
-- Other:
--              Can calculate only 2 operands a time
--              if the string contains 0x, a,b,c,d,e,f return hex
--
--------------------------------------------------------------------------------
set termout off
define vc=&1
col n1 new_value _tpt_n1

column text format a20
column hex_status format a10
SELECT text
      ,n10 n1
      ,REPLACE (TO_CHAR (n10, LPAD ('x', 16, 'x')), ' ') c16
      ,hex_status
  FROM (SELECT '&&vc' text
--             ,DECODE ( hex_status ,'hex', TO_NUMBER ( SUBSTR ('&&vc', INSTR (LOWER ('&&vc'), 'x',-1) + 1) ,'xxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,DECODE ( hex_status ,'hex', TO_NUMBER ( replace (lower('&&vc'), 'x') ,'xxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,hex_status
          FROM (SELECT CASE
                          WHEN REGEXP_LIKE (LOWER ('&&vc'), '^[0-9]+$') THEN 'dec'
                          WHEN REGEXP_LIKE (LOWER ('&&vc') ,'^0?[xX]?[0-9a-f]+[xX]?$') THEN 'hex'
                          ELSE 'other'
                       END AS Hex_Status
                  FROM DUAL));


define vc=&3
col n2 new_value _tpt_n2

column text format a20
column hex_status format a10
SELECT text
      ,n10 n2
      ,REPLACE (TO_CHAR (n10, LPAD ('x', 16, 'x')), ' ') c16
      ,hex_status
  FROM (SELECT '&&vc' text
--             ,DECODE ( hex_status ,'hex', TO_NUMBER ( SUBSTR ('&&vc', INSTR (LOWER ('&&vc'), 'x',-1) + 1) ,'xxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,DECODE ( hex_status ,'hex', TO_NUMBER ( replace (lower('&&vc'), 'x') ,'xxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,hex_status
          FROM (SELECT CASE
                          WHEN REGEXP_LIKE (LOWER ('&&vc'), '^[0-9]+$') THEN 'dec'
                          WHEN REGEXP_LIKE (LOWER ('&&vc') ,'^0?[xX]?[0-9a-f]+[xX]?$') THEN 'hex'
                          ELSE 'other'
                       END AS Hex_Status
                  FROM DUAL));
set termout on

COL calc_dec HEAD "DEC" FOR 999999999999999999999999999.999999
COL calc_hex HEAD "HEX" FOR A20 JUSTIFY RIGHT
COL calc_hex1 HEAD "HEX1" FOR A20 JUSTIFY RIGHT

whenever sqlerror exit failure rollback

select &_tpt_n1 &2 &_tpt_n2 calc_dec
, TO_CHAR (&_tpt_n1 &2 &_tpt_n2, 'FM0xxxxxxxxxxxxxxx') calc_hex
, upper(TO_CHAR (&_tpt_n1 &2 &_tpt_n2, 'FM0xxxxxxxxxxxxxxx')) calc_hex1
from dual ;

4.简单测试：
SCOTT@book01p>  @calcz 10 + 10

                                DEC                  HEX                 HEX1
----------------------------------- -------------------- --------------------
                          20.000000 0000000000000014     0000000000000014

SCOTT@book01p> @calcz a + 0x10

                                DEC                  HEX                 HEX1
----------------------------------- -------------------- --------------------
                          26.000000 000000000000001a     000000000000001A

SCOTT@book01p> @calcz a + 10x

                                DEC                  HEX                 HEX1
----------------------------------- -------------------- --------------------
                          26.000000 000000000000001a     000000000000001A