2013-07-01 要如何於 SQL 中取用 UUID？(SQL UDF GENSYSUUID)

要如何於 SQL 中取用 UUID？(SQL UDF GENSYSUUID)

AS400 DB2 SQL 並不支援直接取用 UUID，而是須透過呼叫系統函式 _GENUUID 來產生，
下述 SQL UDF GENSYSUUID，產生 UUID (16 bytes)的 16 進位字串(32 bytes)，提供直接於 SQL 中直接取用 UUID。



File  : QRPGLESRC

Member: GENSYSUUID

Type  : RPGLE

Usage : CRTBNDRPG GENSYSUUID


     **
     **  Program . . : GENSYSUUID
     **  Description : Generate UUID(16 bytes) to HexString(32 bytes)
     **  Author  . . : Vengoal Chang
     **  Published . : AS400ePaper
     **  Date  . . . : June 26, 2013
     **
     **
     **
     **  Programmer's notes:
     **
     ** CREATE FUNCTION QGPL.GENSYSUUID ( )
     **  RETURNS CHAR(32)
     **  LANGUAGE RPGLE
     **  SPECIFIC QGPL.GENSYSUUID
     **  NOT DETERMINISTIC
     **  NO SQL
     **  CALLED ON NULL INPUT
     **  EXTERNAL NAME 'QGPL/GENSYSUUID'
     **  PARAMETER STYLE SQL ;
     **
     ** Run STRSQL:
     ** Select GENSYSUUID ( )  from sysIBM.sysdummy1
     **
     ** CREATE TABLE QGPL/LICENSE (
     **            KEYUUID CHAR (32 ) NOT NULL,
     **            CUSTNAME VARCHAR (32 ) NOT NULL WITH DEFAULT,
     **            PRODUCT  VARCHAR (32 ) NOT NULL WITH DEFAULT  )
     **
     ** CREATE TRIGGER QGPL.LICENSE_BI BEFORE INSERT ON QGPL.LICENSE
     **         REFERENCING NEW N FOR EACH ROW MODE DB2ROW
     **         SET N.KETUUID = QGPL.GENSYSUUID();
     **
     ** INSERT INTO license(custname, product) VALUES('Oracle', 'DB2')
     ** select * from qgpl/license
     **
     **
     H Option( *NoSrcStmt ) DftActGrp( *No )
     H Debug
     **
      *
      * MI builtin to create a hex dump of a spot in memory
      *
     D hexdump         PR                  EXTPROC('cvthc')
     D  output                       32A
     D  input                        16A
     D  output_len                   10I 0 value

     D HexUUID         S             32A

     D UUID_template   Ds
     D  UtBytPrv                     10u 0 Inz( %Size( UUID_template ))
     D  UtBytAvl                     10u 0
     D                                8a   Inz( *Allx'00' )
     D  UUID                         16a
     **
     D GenUuid         PR                  ExtProc('_GENUUID')
     D UUID_template                   *   Value

     D pRtnUUID        S             32
     D pRtnUUIDIn      S              5I 0
     D sqlstate        S              5A
     d functname       S            517A   VARYING
     d specname        S            128A   VARYING
     d errormsg        S             70A   VARYING
     **
     C     *Entry        Plist
     C                   Parm                    pRtnUUID
     C                   Parm                    pRtnUUIDIn
     C                   Parm                    sqlstate
     C                   Parm                    functname
     C                   Parm                    specname
     C                   Parm                    errormsg

     C                   Callp     GenUuid( %Addr( UUID_template ))

     C                   Callp     HexDump( HexUUID :
     C                                      UUID    :
     C                                      %size(HexUUID)
     C                                    )

     C                   Eval      pRtnUUID = HexUUID
     C*                  dump
     **
     C                   Return





參考資訊:

Generate Universal Unique Identifier (GENUUID)