Unite into Batch Inserts for entities with columns having the StoreGeneratedPattern attribute value set to Identity or Computed (Oracle)
Hello,
I see that batch for "Statements with the RETURNING clause, for example, statements that update or insert entities with columns having the StoreGeneratedPattern attribute value set to Identity or Computed." is not supported.
The code generated for such an insert is for this sample table is:
DECLARE
updatedRowid ROWID;
BEGIN
INSERT INTO ACTIONAR(CNP, NUME, PRENUME, TIP, REZIDENTA)
VALUES (:p0, :p1, :p2, :p3, :p4)
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT ID FROM ACTIONAR WHERE ROWID = updatedRowid;
END;
The SQL for batched inserts could look like this:
DECLARE
updatedRowid1 ROWID;--First in batch
updatedRowid2 ROWID;--Second in batch
BEGIN
INSERT INTO ACTIONAR(CNP, NUME, PRENUME, TIP, REZIDENTA) --First in batch
VALUES (:p0, :p1, :p2, :p3, :p4)
RETURNING ROWID INTO updatedRowid1;
INSERT INTO ACTIONAR(CNP, NUME, PRENUME, TIP, REZIDENTA) --Second in batch
VALUES (:p5, :p6, :p7, :p8, :p9)
RETURNING ROWID INTO updatedRowid2;
OPEN :outParameter FOR
SELECT ID FROM ACTIONAR WHERE ROWID = updatedRowid1 --First store generated ID
union all
SELECT ID FROM ACTIONAR WHERE ROWID = updatedRowid2; --Second store generated ID
END;
the result is a cursor with the ids for the generated entries. the order is preseved so no confusions are made.
Further, another optimization would seem to be to keep and return the generated values directly, not to re-select them from table by rowid:
DECLARE
generatedId1 number; --First in batch (type of variable is known)
generatedId2 number; --Second in batch (type of variable is known)
BEGIN
INSERT INTO ACTIONAR(CNP, NUME, PRENUME, TIP, REZIDENTA)--First in batch
VALUES (:p0, :p1, :p2, :p3, :p4)
RETURNING ID INTO generatedId1;
INSERT INTO ACTIONAR(CNP, NUME, PRENUME, TIP, REZIDENTA)--Second in batch
VALUES (:p5, :p6, :p7, :p8, :p9)
RETURNING ID INTO generatedId2;
OPEN :outParameter FOR
SELECT 1,generatedId1 FROM dual --First store generated ID
union all
SELECT 2,generatedId2 FROM dual; --Second store generated ID
END;