摘要：
带UPDATE,INSERT语句的函数返回值应该是一个TABLE如：RETURNSTABLE(COLVARCHAR(36))函数中返回应该：returnselectNEWIDfromsysibm.sysdummy1;在调用时例子：select*fromtable(UF_GETID('RE'))ast创建ID函数:CodeCREATEFUNCTIONDB2INST1.UF_GETID(SNTYPEV
带UPDATE,INSERT 语句的函数返回值应该是一个TABLE如：RETURNS TABLE(COL VARCHAR(36))
函数中返回应该：return select NEWID from sysibm.sysdummy1;
在调用时例子：
select*fromtable(UF_GETID('RE'))ast
创建ID函数:
CodeCREATEFUNCTIONDB2INST1.UF_GETID(SNTYPEVARCHAR(36))
RETURNSTABLE(COLVARCHAR(36))
SPECIFICDB2INST1.SQL090904180732700
LANGUAGESQL
DETERMINISTIC
NOEXTERNALACTION
MODIFIESSQLDATA
NULLCALL
INHERITSPECIALREGISTERS
BEGINATOMIC
declareNEWIDvarchar(36);
DECLAREv_stepinteger;---默认自增量DECLARENUMvarchar(36);---当前最大值DECLARENOWDATEVARCHAR(50);---获取时间字符串DECLAREV_TYPEVARCHAR(50);---输入的类型SETv_step=1;--默认为自增1SETV_TYPE=SNTYPE;---设置输入的类型----对不存在的类型ifnotexists(select*fromserviceidwheresntype=V_TYPE)then---得到当前时间setNOWDATE=replace(replace(replace(to_char(currenttimestamp,'YYYY-MM-DDHH24:MI:SS'),'-',''),':',''),'','');
---拼接这个ID字符串SETNEWID=SNTYPE||NOWDATE||'1';
---加入这条记录insertintoSERVICEID(SNTYPE,MAXNUM,STEP)VALUES(V_TYPE,NEWID,v_step);
else---得到当前时间setNOWDATE=replace(replace(replace(to_char(currenttimestamp,'YYYY-MM-DDHH24:MI:SS'),'-',''),':',''),'','');
---查到当前数据库中最大的IDsetNUM=(selectMAXNUMfromSERVICEIDWHERESNTYPE=V_TYPE);
setNUM=replace(NUM,SUBSTRING(NUM,1,16,CODEUNITS32),'');
---查询出当前最大值并加1得到新的IDsetNUM=RTRIM(char(INTEGER(NUM)+1));
---得到当前时间setNOWDATE=replace(replace(replace(to_char(currenttimestamp,'YYYY-MM-DDHH24:MI:SS'),'-',''),':',''),'','');
---拼接这个ID字符串SETNEWID=SNTYPE||NOWDATE||NUM;
updateSERVICEIDSETMAXNUM=NEWIDWHERESNTYPE=V_TYPE;
endif;
returnselectNEWIDfromsysibm.sysdummy1;
END
;
调用这个执行这个函数：
select*fromtable(UF_GETID('RE'))ast