apache > db
Apache DB Project
 
Font size:      

IDENTITY_VAL_LOCAL

IDENTITY_VAL_LOCAL

Derby supports the IDENTITY_VAL_LOCAL function.

Syntax:

 IDENTITY_VAL_LOCAL ( )

The IDENTITY_VAL_LOCAL function is a non-deterministic function that returns the most recently assigned value for an identity column, where the assignment occurred as a result of a single row INSERT statement using a VALUES clause.

The IDENTITY_VAL_LOCAL function has no input parameters. The result is a DECIMAL (31,0), regardless of the actual data type of the corresponding identity column.

The value returned by the IDENTITY_VAL_LOCAL function is the value assigned to the identity column of the table identified in the most recent single row INSERT statement. The INSERT statement must contain a VALUES clause on a table containing an identity column. The assigned value is an identity value generated by Derby. The function returns a null value when a single row INSERT statement with a VALUES clause has not been issued for a table containing an identity column.

The result of the function is not affected by the following:

  • A single row INSERT statement with a VALUES clause for a table without an identity column
  • A multiple row INSERT statement with a VALUES clause
  • An INSERT statement with a fullselect

Examples:

ij> create table t1(c1 int generated always as identity, c2 int);
0 rows inserted/updated/deleted
ij> insert into t1(c2) values (8);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1 
-------------------------------
1 
1 row selected
ij> select IDENTITY_VAL_LOCAL()+1, IDENTITY_VAL_LOCAL()-1 from t1;
1                                |2 
-------------------------------------------------------------------
2                                |0 
1 row selected
ij> insert into t1(c2) values (IDENTITY_VAL_LOCAL());
1 row inserted/updated/deleted
ij> select * from t1;
C1             |C2 
-------------------------------
1              |8 
2              |1 
2 rows selected
ij> values IDENTITY_VAL_LOCAL();
1 
-------------------------------
2 
1 row selected
ij> insert into t1(c2) values (8), (9);
2 rows inserted/updated/deleted
-- multi-values insert, return value of the function should not change
ij> values IDENTITY_VAL_LOCAL();
1 
-------------------------------
2 
1 row selected
ij> select * from t1;
C1             |C2 
-------------------------------
1              |8 
2              |1 
3              |8 
4              |9 
4 rows selected
ij> insert into t1(c2) select c1 from t1;
4 rows inserted/updated/deleted
-- insert with sub-select, return value should not change
ij> values IDENTITY_VAL_LOCAL();
1 
-------------------------------
2 
1 row selected
ij> select * from t1;
C1             |C2 
-------------------------------
1              |8 
2              |1 
3              |8 
4              |9 
5              |1 
6              |2 
7              |3 
8              |4 
8 rows selected 

Previous Page
Next Page
Table of Contents
Index