[20240925]10,16进制转10,16进制的sqj脚本.txt

--//工作需要一个10,16进制转10,16进制的sql脚本，主要hash_value值有时候输入16进制有时候输入10进制，我需要执行SQL代码自动识
--//别。

1.例子：
SCOTT@book01p(289,22246)> @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.

SCOTT@book01p(289,22246)> select * from dept where deptno=20;
    DEPTNO DNAME                          LOC
---------- ------------------------------ -------------
        20 RESEARCH                       DALLAS

SCOTT@book01p(289,22246)> @ hash

HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
  95129850 80baj2c2ur47u            0     102650      2852011669   5ab90fa  2024-09-25 09:03:46    16777217

SYS@book> @ sharepool/shp4 80baj2c2ur47u 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address   0000000069C6DB90 0000000069C6E0F0 select * from dept where deptno=20                0          0          0 00000000676BB9C8 0000000067D9E208       8128      12128       3299     23555      23555   95129850 80baj2c2ur47u          0
parent handle address  0000000069C6E0F0 0000000069C6E0F0 select * from dept where deptno=20                0          0          0 000000006695B300 00                     4064          0          0      4064       4064   95129850 80baj2c2ur47u      65535

SYS@book> @ fchaz 0000000067D9E208
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 0000000067D9D9B0          1          1 KGLH0^5ab90fa          4096 recr           4095 000000006695B300 0000000067D9D9B0 0000000067D9E9B0

--//5ab90fa = 95129850,我希望生成的SQL脚本不管输入5ab90fa，95129850自动识别转换为95129850。

2.脚本建立：
$ cat hd.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- Name:        hd.sql
-- Purpose:     display hex or dec to hex or dec
--
-- Author:      lfree
-- Usage:
--     @ hd <hex|dec>
--
--------------------------------------------------------------------------------

define vc=&1

column text format a20
column hex_status format a10
SELECT text
      ,n10
      ,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) ,'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]+$') THEN 'hex'
                          ELSE 'other'
                       END AS Hex_Status
                  FROM DUAL));

--//注：WHEN REGEXP_LIKE (lower('&&1'), '^[0-9]+$') THEN 'dec'要写在前面，就是如果我输入1234，脚本优先认为是10进制数。
--//如果颠倒过来，1234也可以认为是16进制数字。

3.测试：
SCOTT@book01p> @ hd 1234
TEXT                                   N10 C16               HEX_STATUS
-------------------- --------------------- ----------------- ----------
1234                                  1234 4d2               dec

SCOTT@book01p> @ hd x1234
TEXT                                   N10 C16               HEX_STATUS
-------------------- --------------------- ----------------- ----------
x1234                                 4660 1234              hex

SCOTT@book01p> @ hd xa0
TEXT                                   N10 C16               HEX_STATUS
-------------------- --------------------- ----------------- ----------
xa0                                    160 a0                hex

SCOTT@book01p> @ hd 0xa0
TEXT                                   N10 C16               HEX_STATUS
-------------------- --------------------- ----------------- ----------
0xa0                                   160 a0                hex