Oracle generates uuid and queries uuid

If you haven't already created the table, you can do it in the following way.

Create table testuu (

Id varchar2 (2000) default sys_guid ()

Name varchar2 (2000))

If you have already created a table, make sure that your original primary key ID does not have any real business meaning, and use the following method to modify it.

Alter table testuu modify id default sys_guid ()

Update testuu set id = sys_guid ()

When Oracle creates a new system table, the primary key is required to be 32-bit uuid, and it is guessed that Oracle will certainly provide related functions.

Looking through the relevant documents, it is found that the function sys_guid () provided by Oracle is used to get 32-bit uuid, which is simply used as

Select sys_guid () from dual

This function returns 32-bit uuid in uppercase and can be converted to lowercase using lower (sys_guid ()).

It is very convenient to generate cross-system unique identifier UUID in Oracle, which is simpler than generating sequence. Just use sys_guid () directly.

For example, select sys_guid () from dual produces a 32-bit hexadecimal random number related to the MAC address and generation time.

The data type is raw (16) with 32 characters.

Its generation mechanism is sufficient to ensure that the possibility of massive guid repetition generated by all systems in the world is very small. This is very important in the AIXM concept.

Splicing according to time:

Select lower (sys_guid ()) | | to_char (sysdate,'yyyyMMddHh34miss') from dual

If you want the returned uuid to meet the standard format, you can use the following function:

CREATE OR REPLACE FUNCTION

Get_uuid RETURN VARCHAR IS guid VARCHAR (50)

BEGIN guid: = lower (RAWTOHEX (sys_guid ())

RETURN substr (guid,1,8) | |'-'| | substr (guid,9,4) | |'-'| | substr (guid,13,4) | |'-'| | substr (guid,17,4) | |'-'| | substr (guid,21,12) |

END get_uuid

SQL > select get_uuid from dual

GET_UUID

A8f662b8-6e7a-13fe-e040-970a437c6bd7