如何在 Oracle 上生成版本 4（随机）UUID？

您可以编写一个 Java 过程，然后编译它并在 Oracle 中运行它。在此过程中，您可以使用：

UUID uuid = UUID.randomUUID();
return uuid.toString();

生成所需的值。

以下是有关如何在 Oracle 中编译 java 过程的链接。

我现在使用它作为解决方法：

create or replace function random_uuid return RAW is
  v_uuid RAW(16);
begin
  v_uuid := sys.dbms_crypto.randombytes(16);
  return (utl_raw.overlay(utl_raw.bit_or(utl_raw.bit_and(utl_raw.substr(v_uuid, 7, 1), '0F'), '40'), v_uuid, 7));
end random_uuid;

该函数需要 和 。两者都需要执行授权。dbms_cryptoutl_raw

grant execute on sys.dbms_crypto to uuid_user;

这是一个完整的示例，基于 @Pablo Santa Cruz 的答案和您发布的代码。

我不确定为什么您会收到错误消息。这可能是 SQL Developer 的问题。当您在 SQL*Plus 中运行它并添加一个函数时，一切正常：

   create or replace and compile
   java source named "RandomUUID"
   as
   public class RandomUUID
   {
      public static String create()
      {
              return java.util.UUID.randomUUID().toString();
      }
   }
   /

Java created.

   CREATE OR REPLACE FUNCTION RandomUUID
   RETURN VARCHAR2
   AS LANGUAGE JAVA
   NAME 'RandomUUID.create() return java.lang.String';
   /

Function created.

   select randomUUID() from dual;

RANDOMUUID()
--------------------------------------------------------------
4d3c8bdd-5379-4aeb-bc56-fcb01eb7cc33

---

但如果可能的话，我会坚持下去。在 My Oracle Support 上查看 ID 1371805.1 - 此错误应该在 11.2.0.3 中修复。SYS_GUID

编辑

哪一个更快取决于函数的使用方式。

在 SQL 中使用时，Java 版本看起来稍微快一些。但是，如果要在 PL/SQL 上下文中使用此函数，则 PL/SQL 函数是 速度提高一倍。（可能是因为它避免了在引擎之间切换的开销。

下面是一个简单示例：

--Create simple table
create table test1(a number);
insert into test1 select level from dual connect by level <= 100000;
commit;

--SQL Context: Java function is slightly faster
--
--PL/SQL: 2.979, 2.979, 2.964 seconds
--Java: 2.48, 2.465, 2.481 seconds
select count(*)
from test1
--where to_char(a) > random_uuid() --PL/SQL
where to_char(a) > RandomUUID() --Java
;

--PL/SQL Context: PL/SQL function is about twice as fast
--
--PL/SQL: 0.234, 0.218, 0.234
--Java: 0.52, 0.515, 0.53
declare
    v_test1 raw(30);
    v_test2 varchar2(36);
begin
    for i in 1 .. 10000 loop
        --v_test1 := random_uuid; --PL/SQL
        v_test2 := RandomUUID; --Java
    end loop;
end;
/

---

版本 4 GUID 不是完全随机的。有些字节应该是固定的。我不确定为什么要这样做，或者这是否重要，但根据 https://www.cryptosys.net/pki/uuid-rfc4122.html：

生成版本 4 UUID 的过程如下：

Generate 16 random bytes (=128 bits)
Adjust certain bits according to RFC 4122 section 4.4 as follows:
    set the four most significant bits of the 7th byte to 0100'B, so the high nibble is "4"
    set the two most significant bits of the 9th byte to 10'B, so the high nibble will be one of "8", "9", "A", or "B".
Encode the adjusted bytes as 32 hexadecimal digits
Add four hyphen "-" characters to obtain blocks of 8, 4, 4, 4 and 12 hex digits
Output the resulting 36-character string "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"

Java 版本中的值似乎符合标准。

https://stackoverflow.com/a/10899320/1194307

以下函数使用 sys_guid（） 并将其转换为 uuid 格式：

create or replace function random_uuid return VARCHAR2 is
  v_uuid VARCHAR2(40);
begin
  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') into v_uuid from dual;
  return v_uuid;
end random_uuid;

它不需要创建dbms_crypto包并授予它。

它可能不是唯一的，但会生成一个“类似 GUID”的随机字符串：

 FUNCTION RANDOM_GUID
    RETURN VARCHAR2 IS
    RNG    NUMBER;
    N      BINARY_INTEGER;
    CCS    VARCHAR2 (128);
    XSTR   VARCHAR2 (4000) := NULL;
  BEGIN
    CCS := '0123456789' || 'ABCDEF';
    RNG := 15;

    FOR I IN 1 .. 32 LOOP
      N := TRUNC (RNG * DBMS_RANDOM.VALUE) + 1;
      XSTR := XSTR || SUBSTR (CCS, N, 1);
    END LOOP;

    RETURN XSTR;
  END RANDOM_GUID;

改编自DBMS_RANDOM来源。字符串。

对我来说，获得基于 Java 的函数的最简单和最短的方法是：

create or replace function random_uuid return varchar2 as
language java
name 'java.util.UUID.randomUUID() return String';

我无法完全理解为什么如果我添加它不编译。.toString()

根据 UUID 版本 4，格式应为 xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx。@lonecat答案提供此格式，@ceving答案也部分提供版本 4 的要求。缺少的部分是格式 y，y 应该是 8、9、a 或 b 之一。

混合这些答案并修复 y 部分后，代码如下所示：

create or replace function fn_uuid return varchar2 is
  /* UUID Version 4 must be formatted as xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx where x is any hexadecimal character (lower case only) and y is one of 8, 9, a, or b.*/

  v_uuid_raw raw(16);
  v_uuid     varchar2(36);
  v_y        varchar2(1);
begin

  v_uuid_raw := sys.dbms_crypto.randombytes(16);
  v_uuid_raw := utl_raw.overlay(utl_raw.bit_or(utl_raw.bit_and(utl_raw.substr(v_uuid_raw, 7, 1), '0F'), '40'), v_uuid_raw, 7);

  v_y := case round(dbms_random.value(1, 4))
            when 1 then
             '8'
            when 2 then
             '9'
            when 3 then
             'a'
            when 4 then
             'b'
           end;

  v_uuid_raw := utl_raw.overlay(utl_raw.bit_or(utl_raw.bit_and(utl_raw.substr(v_uuid_raw, 9, 1), '0F'), v_y || '0'), v_uuid_raw, 9);
  v_uuid     := regexp_replace(lower(v_uuid_raw), '([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');

  return v_uuid;
end fn_uuid;

ceving 接受的答案与RFC4122不一致：clock_seq_hi_and_reserved的两个最高有效位（位 6 和 7）应分别设置为 0 和 1。这使得 y 等于 uğur-yeşilyurt 格式 xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx 已经提到的 8,9，a 或 b

我的解决方案沿着RFC进行了直截了当的处理：

create or replace function random_uuid return raw is
  /*
  Set the four most significant bits (bits 12 through 15) of the
      time_hi_and_version field to the 4-bit version number from
      Section 4.1.3.
  */
  v_time_hi_and_version raw(2) := utl_raw.bit_and(utl_raw.bit_or(dbms_crypto.randombytes(2), '4000'), '4FFF');
  /*
  Set the two most significant bits (bits 6 and 7) of the
      clock_seq_hi_and_reserved to zero and one, respectively.
  */
  v_clock_seq_hi_and_reserved raw(1) := utl_raw.bit_and(utl_raw.bit_or(dbms_crypto.randombytes(1), '80'), 'BF');
  /*
  Set all the other bits to randomly (or pseudo-randomly) chosen
      values.
  */
  v_time raw(6) := dbms_crypto.randombytes(6);
  v_clock_seq_low_and_node raw(7) := dbms_crypto.randombytes(7);
begin
  return v_time || v_time_hi_and_version || v_clock_seq_hi_and_reserved || v_clock_seq_low_and_node;
end random_uuid;

编辑：

虽然第一次实现很容易理解，但它的效率相当低。下一个解决方案是速度快 3 到 4 倍。

create or replace function random_uuid2 return raw is
  v_uuid raw(16) := dbms_crypto.randombytes(16);
begin
   v_uuid :=  utl_raw.bit_or(v_uuid, '00000000000040008000000000000000');
   v_uuid := utl_raw.bit_and(v_uuid, 'FFFFFFFFFFFF4FFFBFFFFFFFFFFFFFFF');
  return v_uuid;
end;

该测试表明，random_uuid大约需要 1 毫秒，random_uuid2仅 250 微秒。第一个版本中的串联消耗了太多时间;

declare
   dummy_uuid raw(16);
begin
   for i in 1 .. 20000 loop
      --dummy_uuid := random_uuid;
      dummy_uuid := random_uuid2;
   end loop;
end;

我和我的一个朋友编写了一些纯 PLSQL 函数，可以生成 UUID 版本 4 并格式化任何类型的 GUID。格式化程序也以两种方式编写。一个聚合字符串，一个使用正则表达式来格式化 UUID

CREATE OR REPLACE FUNCTION RANDOM_UUD_RAW
  RETURN RAW IS V_UUID RAW(16);
  BEGIN V_UUID := SYS.DBMS_CRYPTO.Randombytes(16);
    V_UUID := UTL_RAW.Overlay(UTL_RAW.Bit_or(UTL_RAW.Bit_and(UTL_RAW.Substr(V_UUID, 7, 1), '0F'), '40'), V_UUID, 7, 1);
    V_UUID := UTL_RAW.Overlay(UTL_RAW.Bit_or(UTL_RAW.Bit_and(UTL_RAW.Substr(V_UUID, 9, 1), '3F'), '80'), V_UUID, 9, 1);
    RETURN V_UUID;
  END RANDOM_UUD_RAW; --
CREATE OR REPLACE FUNCTION UUID_FORMATTER_CONCAT(V_UUID RAW)
  RETURN VARCHAR2 IS V_STR VARCHAR2(36);
  BEGIN V_STR := lower(SUBSTR(V_UUID, 1, 8) || '-' || SUBSTR(V_UUID, 9, 4) || '-' || SUBSTR(V_UUID, 13, 4) || '-' || SUBSTR(V_UUID, 17, 4) || '-' || SUBSTR(V_UUID, 21));
    RETURN V_STR;
  END UUID_FORMATTER_CONCAT; --
CREATE OR REPLACE FUNCTION UUID_FORMATTER_REGEX(V_UUID RAW)
  RETURN VARCHAR2 IS V_STR VARCHAR2(36);
  BEGIN V_STR := lower(regexp_replace(V_UUID, '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5'));
    RETURN V_STR;
  END UUID_FORMATTER_REGEX; --
CREATE OR REPLACE FUNCTION RANDOM_UUID_STR
  RETURN VARCHAR2 AS BEGIN RETURN UUID_FORMATTER_CONCAT(RANDOM_UUD_RAW());
  END RANDOM_UUID_STR; --
CREATE OR REPLACE FUNCTION RANDOM_UUID_STR_REGEX
  RETURN VARCHAR2 AS BEGIN RETURN UUID_FORMATTER_REGEX(RANDOM_UUD_RAW());
  END RANDOM_UUID_STR_REGEX;

我对上述任何一个答案都不完全满意：Java 通常没有安装，需要授权并且是顺序的。dbms_cryptosys_guid()

我决定了这个。

create or replace function random_uuid return VARCHAR2 is
   random_hex varchar2(32);
begin
  random_hex := translate(DBMS_RANDOM.string('l', 32), 'ghijklmnopqrstuvwxyz', '0123456789abcdef0123');
  return          substr(random_hex, 1, 8) 
        || '-' || substr(random_hex, 9, 4)  
        || '-' || substr(random_hex, 13, 4)  
        || '-' || substr(random_hex, 17, 4)  
        || '-' || substr(random_hex, 21, 12);
end random_uuid;
/

dbms_random is (by default) public so no grants are required and it is reasonably random. Note that is not cryptographically secure so if you need that, use the approach above. The hex value distribution is also skewed by the translate function. If you need real UUID 4 output then you can tweak the substr (I just need uniqueness).dbms_randomdbms_crypto

The same technique could be used in sql without a function with some imagination:

select 
          substr(rand, 1, 8) 
|| '-' || substr(rand, 9, 4)  
|| '-' || substr(rand, 13, 4)  
|| '-' || substr(rand, 17, 4)  
|| '-' || substr(rand, 21, 12)  
from (select translate(DBMS_RANDOM.string('l', 32), 'ghijklmnopqrstuvwxyz', '0123456789abcdef0123') rand  from dual);