在不使用REGEXP_REPLACE和PL/SQL的情况下形成UUID字符串

Question

我想格式化sys_guid()函数的结果，如this answer中提议的那样

select regexp_replace(rawtohex(sys_guid())
       , '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
       , '\1-\2-\3-\4-\5') 
         as FORMATTED_GUID 
 from dual

出于性能原因，我希望避免使用regexp_replace (因为我处理了大量的记录)。

我的场景可以简化为这个用例：

 select rawtohex(sys_guid()) GUID
 from dual connect by level <= 2;

显然，我不能使用substr和串联，因为每个SUBSTR将处理不同的SYS_GUID。我还想继续使用SQL，而不使用上下文切换到PL/SQL函数。

是否知道如何使用掩码在SQL中格式化与日期或数字类似的字符串：

 to_char(rawtohex(sys_guid(),'CCCCCCCC-CCCC-CCCC-CCCC-CCCCCCCCCCCC') /* note, this is clear illegal */

Answer 1

不幸的是，不能以数字格式包含字符串文本，否则可以将十六进制字符串转换为数字，然后再返回，在正确的位置插入格式掩码中的文字--但只能在日期中这样做。

您可以使用substr()，因为位置是固定的。你担心的是

显然，我不能使用substr和串联，因为每个SUBSTR将处理不同的SYS_GUID。

使用子查询分解(a.ka )。一个公共表表达式/ CTE )意味着substr()调用该CTE中的一行都会看到相同的GUID；此方法不会为每个SYS_GUID生成一个新的GUID。

with t as (
  select rawtohex(sys_guid()) guid from dual
  connect by level <= 2
)
select guid, substr(guid, 1, 8)
  ||'-'|| substr(guid, 9, 4)
  ||'-'|| substr(guid, 13, 4)
  ||'-'|| substr(guid, 17, 4)
  ||'-'|| substr(guid, 21, 12) as formatted_guid
from t;

GUID                             FORMATTED_GUID                         
-------------------------------- ----------------------------------------
2F6BA62518F926D0E0534D49E50ABB46 2F6BA625-18F9-26D0-E053-4D49E50ABB46    
2F6BA62518FA26D0E0534D49E50ABB46 2F6BA625-18FA-26D0-E053-4D49E50ABB46    

这比大量数据上的正则表达式要快得多。使用一个循环中的100000个值(在PL/SQL块中，在循环中完成最少的工作以使其实际正确计算，并使用dbms_utility.get_cpu_time检查经过的时间)，regex版本大约需要2.51秒，而子字符串版本大约需要0.29秒。当然，你的系统会得到不同的数字，但它仍然应该是相同的数量级。