How to create a function in DB2 that returns the value of a sequence?

32,564
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

To try it out:

SELECT MY_FUNC('aa') FROM SYSIBM.SYSDUMMY1;
Share:
32,564
Juha Syrjälä
Author by

Juha Syrjälä

GitHublinkedin

Updated on May 26, 2020

Comments

  • Juha Syrjälä
    Juha Syrjälä almost 4 years

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

    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.

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