Hi friends!!! In today’s post we are going to work with Autonumerics in Oracle, MySQL and SQL Server . We will see how to obtain the last value of the fields IDENTITY (SQL SERVER), AUTO_INCREMENT (MySQL) and IDENTITY (ORACLE from version 12c. Sequence in previous versions).
If you have in mind to make a select MAX (…) from field to consult … DO NOT DO IT! It’s not a good idea. The problem is that if you try It will work fine on your test rig, but it won’t really work for you.
Why is it unreliable to get auto-numbers with Select MAX?
The reason is that in environments with many simultaneous users, between when you enter your order in the table and make the query to see the maximum ID, another user may have entered another order in the same table that will already have a higher ID. If you simply find out the maximum you run the risk (almost certainly) that your order lines and your customer will be associated with an order that does not correspond to them. Do you see the problem?
Let’s see how to obtain the autonumeric values in Oracle, MySQL and SQL Server
SQL SERVER:
SELECT SCOPE_IDENTITY ()
Returns the last identity value inserted in a identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two instructions are in the same stored procedure, function, or batch, are in the same scope.
MYSQL:
SELECT LAST_INSERT_ID ()
In the case of MySQL this statement returns the last auto-numeric that has been generated in the same connection that we are using currently. As long as we don’t share connections (something unlikely in any case) we would have no problem getting it with confidence.
ORACLE:
In the case of Oracle, it is a bit more complex. To get started, and incredibly, Oracle did not have auto-incremental fields until release of its 12c version in June 2013. Before that you had to manage them using Sequences and triggers to achieve the same.
CREATE TABLE Orders ( OrderID INT PRIMARY KEY, .... );
We also have to create a sequence of the order id
CREATE SEQUENCE OrderSequence MINVALUE 1 MAXVALUE 1000 INCREMENT BY 1;
And a trigger that triggers the sequence.
CREATE OR REPLACE TRIGGER orders_bit BEFORE INSERT ON Orders FOR EACH ROW BEGIN SELECT OrderSequence.NEXTVAL INTO: new. OrderID FROM dual; END; As a step
As a final step, we had to make a returning to insert
INSERT INTO Orders (CustomerID, EmployeeID, OrderDate ....) VALUES (1, 1 ...) RETURNING OrderID INTO: last_insert_id
In version 12c they have improved it and the creation sentence of the incremental car is:
CREATE TABLE Orders (OrderID NUMBER GENERATED ALWAYS AS IDENTITY, ...);
Now, yes, to obtain the value we must do exactly the same as before in the insert statement, that is, use RETURNING INTO to get the value, as we just saw. More information in https://docs.oracle.com/en/database/other-databases/nosql-database/19.1/java-driver-table/creating-tables-identity-column.html
We hope you have been useful friends! See you next time, if you need help with this or another database issue, do not hesitate to contact with us.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn