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

[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