如何在 DB2 中创建一个返回序列值的函数? [英] How to create a function in DB2 that returns the value of a sequence?

问题描述

如何在 DB2 中创建一个从序列中获取值并返回的函数?

How to create a function in DB2 that obtains a value from a sequence and returns it?

应该可以在 select 或 insert 语句中使用该函数，例如:

It should be possible to use that function in select or insert statement, e.g:

select my_func() from xxx
insert into xxx values(my_func())

基本上我在复杂公式中使用序列值，我想将计算封装在一个函数中.

Basically I am using the sequence value in a complex formula, and I'd like to encapsulate the calculation inside a function.

我不是在问如何简单地从序列中获取下一个值.

I am not asking how to simply get next value from sequence.

推荐答案

CREATE FUNCTION "MYSCHEMA"."MY_FUNC"(PARAM1 VARCHAR(4000))
     RETURNS INT
SPECIFIC SQL110520140321900 BEGIN ATOMIC
     DECLARE VAR1 INT;
     DECLARE VAR2 INT;
     SET VAR1  = NEXTVAL FOR MY_SEQ;
     SET VAR2 = VAR1 + 2000; --or whatever magic you want to do
     RETURN VAR2;
END

尝试一下:

SELECT MY_FUNC('aa') FROM SYSIBM.SYSDUMMY1;

这篇关于如何在 DB2 中创建一个返回序列值的函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！