Tuesday, March 21, 2017

oracle sys_guid(), mssql newid(), mysql trigger all for default uniqueidentifier ID

inORACLE


DROP TABLE "CMS_TEMP";

CREATE TABLE "CMS_TEMP" (   
"ID" VARCHAR2(36) NOT NULL,
"NAME" NVARCHAR2(150),
"RCTIME" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL
);

create or replace FUNCTION NEWID RETURN VARCHAR2 IS guid VARCHAR2(36);
BEGIN
    SELECT SYS_GUID() INTO guid FROM DUAL;
guid := 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');
--OR
guid := SUBSTR(guid,  1, 8) ||
        '-' || SUBSTR(guid,  9, 4) ||
        '-' || SUBSTR(guid, 13, 4) ||
        '-' || SUBSTR(guid, 17, 4) ||
        '-' || SUBSTR(guid, 21);
    RETURN guid;
END NEWID;

create or replace TRIGGER SetGUIDforCMS_TEMP BEFORE INSERT ON CMS_TEMP
FOR EACH ROW
BEGIN
    :new.ID := NEWID();
END;

insert into "CMS_TEMP" (NAME)
values ('new name 1');

select * from cms_temp;

DECLARE
  v_Return VARCHAR2(36);
BEGIN
  v_Return := NEWID;
  DBMS_OUTPUT.PUT_LINE(v_Return);
END;

select NEWID from dual;

inMSSQL


Data Type: uniqueidentifier
Default Value or Binding: (newid())

CREATE TABLE [dbo].[table1](
    [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_table1_ID]  DEFAULT (newid()),
    [Name] [nvarchar](50) NOT NULL,
    [rcdate] datetime NOT NULL DEFAULT GETUTCDATE()
) ON [PRIMARY]
GO

inMYSQL

CREATE TRIGGER `before_insert_table1` BEFORE INSERT ON `table1`
FOR EACH ROW BEGIN
    SET new.id = UPPER(uuid());
END

No comments: