oracle database - Large data sets.Squence value increment always 1 - Stack Overflow

admin管理员组

文章数量:1296456

In this case always when run this sql statement SEQUENCE NUMBER INCREMENT BY 1.WHY INCREMENT EVEN NOT INSERT TO THE TABLES.

INSERT ALL WHEN ID = 23 THEN INTO ARTICLES
  (ID, NAME, DESCRIPTION)
VALUES
  (TEST.NEXTVAL, NAME, DESCRIPTION) WHEN ID > 100 THEN INTO BOOKS
  (ID, NAME)
VALUES
  (TEST.NEXTVAL, NAME)
  SELECT 98 as ID,
         'Mark Seemann' AS NAME,
         'DEPENDENCY INJECTION' as DESCRIPTION
    FROM DUAL

In this case always when run this sql statement SEQUENCE NUMBER INCREMENT BY 1.WHY INCREMENT EVEN NOT INSERT TO THE TABLES.

INSERT ALL WHEN ID = 23 THEN INTO ARTICLES
  (ID, NAME, DESCRIPTION)
VALUES
  (TEST.NEXTVAL, NAME, DESCRIPTION) WHEN ID > 100 THEN INTO BOOKS
  (ID, NAME)
VALUES
  (TEST.NEXTVAL, NAME)
  SELECT 98 as ID,
         'Mark Seemann' AS NAME,
         'DEPENDENCY INJECTION' as DESCRIPTION
    FROM DUAL

• oracle-database
• sequence

Share Improve this question Follow edited Feb 12 at 9:21 MT0 169k1212 gold badges6666 silver badges127127 bronze badges asked Feb 12 at 6:08 KAAMIRLIKAAMIRLI 1911 silver badge33 bronze badges 1

• I wouldn't use INSERT ALL - it is better to programmatically determine which action to take and which table to insert into in client code (or PL/SQL) before invoking SQL to perform the change. If you were conditionally inserting into only one table, and working with many rows rather than one, the MERGE statement would be a much better choice and would avoid incrementing the sequence even when no record is inserted. But for single row operations like this, just use conditional logic outside SQL in whatever the client language is, not within SQL itself. SQL is not a programming language. – Paul W Commented Feb 12 at 12:55

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

That's because Oracle fetches the NEXTVAL before when conditions are being checked.

Supposing that ID = 50 (which is not 23, and is not larger than 100),

• sequence gets its next value first
• when is evaluated
  • the 1st condition isn't met
  • the 2nd condition isn't met
• no rows are being inserted into any of two tables
• sequence is, though, incremented

---

Although this is not the case in code you posted as it always inserts only one row per table, note that if there were two or more rows to be inserted - which actually might be the case as you mentioned "large data sets" (and if this code is simplified) - the sequence will be incremented only once for the whole insert all, which means that all rows you insert will share the same sequence value.

And one of the solution is: wrap the TEST.NEXTVAL into a FUNCTION.

本文标签： oracle databaseLarge data setsSquence value increment always 1Stack Overflow