DB2带UPDATE,INSERT语句的函数
带UPDATE,INSERT 语句的函数返回值应该是一个TABLE如：RETURNS TABLE(COL VARCHAR(36))
函数中返回应该： return select NEWID from sysibm.sysdummy1;
在调用时例子：
select * from table(UF_GETID('RE')) as t
创建ID函数:
Code
CREATE FUNCTION DB2INST1.UF_GETID(SNTYPE VARCHAR(36))
RETURNS TABLE(COL VARCHAR(36))
SPECIFIC DB2INST1.SQL090904180732700
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
MODIFIES SQL DATA
NULL CALL
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
declare NEWID varchar(36);
DECLARE v_step integer; ---默认自增量
DECLARE NUM varchar(36); ---当前最大值
DECLARE NOWDATE VARCHAR(50); ---获取时间字符串
DECLARE V_TYPE VARCHAR(50); ---输入的类型
SET v_step =1; --默认为自增1
SET V_TYPE = SNTYPE; ---设置输入的类型
----对不存在的类型
if not exists(select * from serviceid where sntype=V_TYPE) then
---得到当前时间
set NOWDATE = replace(replace(replace(to_char(current timestamp, 'YYYY-MM-DD HH24:MI:SS'),'-',''),':',''),' ','');
---拼接这个ID字符串
SET NEWID = SNTYPE||NOWDATE||'1';
---加入这条记录
insert into SERVICEID(SNTYPE,MAXNUM,STEP) VALUES(V_TYPE,NEWID,v_step);
else
---得到当前时间
set NOWDATE = replace(replace(replace(to_char(current timestamp, 'YYYY-MM-DD HH24:MI:SS'),'-',''),':',''),' ','');
---查到当前数据库中最大的ID
set NUM = (select MAXNUM from SERVICEID WHERE SNTYPE=V_TYPE);
set NUM=replace(NUM,SUBSTRING(NUM,1,16,CODEUNITS32),'');
---查询出当前最大值并加1得到新的ID
set NUM = RTRIM(char(INTEGER(NUM)+1));
---得到当前时间
set NOWDATE = replace(replace(replace(to_char(current timestamp, 'YYYY-MM-DD HH24:MI:SS'),'-',''),':',''),' ','');
---拼接这个ID字符串
SET NEWID=SNTYPE||NOWDATE||NUM;
update SERVICEID SET MAXNUM=NEWID WHERE SNTYPE=V_TYPE ;
end if;
return select NEWID from sysibm.sysdummy1;
END
;
调用这个执行这个函数：
select * from table(UF_GETID('RE')) as t
页:
[1]