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

来源：这里教程网时间：2026-03-03 20:38:24 作者：

[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