iSeries DB2-是否有任何方法可以从插入语句中选择标识值?

请考虑“ID”是您的身份列的名称。以下stmt将返回新生成的id(与插入stmt插入的id相同):

SELECT ID FROM FINAL TABLE (
    INSERT INTO AwesomeTable (column1, column2, etc.)
            VALUES (value1, value2, etc.)    
    )

我在publish网站上找到了一些解释:

     /* The following SELECT statement references an INSERT statement in its
           FROM clause.  It inserts an employee record from host variables into
           table company_b.  The current employee ID from the cursor is selected
           into the host variable new_id.  The keywords FROM FINAL TABLE
           determine that the value in new_id is the value of ID after the
           INSERT statement is complete.

           Note that the ID column in table company_b is generated and without
           the SELECT statement an additional query would have to be made in
           order to retreive the employee's ID number.
        */
        EXEC SQL SELECT ID INTO :new_id
                 FROM FINAL TABLE(INSERT INTO company_b
                 VALUES(default, :name, :department, :job, :years, :salary, 
                        :benefits, :id));

IDENTITY_VAL_LOCAL IBM documentation

是一个 非确定性函数 返回最近分配的 标识列的值。

CREATE TABLE EMPLOYEE
    (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
     NAME CHAR(30),
     SALARY DECIMAL(5,2),
     DEPT SMALLINT)

INSERT INTO EMPLOYEE
    (NAME, SALARY, DEPTNO)
    VALUES('Rupert', 989.99, 50)

SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1

CREATE TABLE AUTOINC (                                       
   AUTO91 INTEGER       GENERATED ALWAYS AS IDENTITY,          
   SCDS91 CHAR(35)      NOT NULL DEFAULT '',                   
   MCLD91 DECIMAL(3,0)  NOT NULL DEFAULT 0,                    
   CONSTRAINT PK_AUTOINC PRIMARY KEY(AUTO91));

//请注意自动增量字段所在的默认关键字。

insert into AUTOINC Values( default ,'SYSC' , 0 )

select **IDENTITY_VAL_LOCAL**() from AUTOINC **fetch first row only**