Oracle inserts data and obtains the ID of the auto-increment Sequence

Oracle inserts data and obtains the ID of the auto-increment sequence. This article describes how to create the auto-increment sequence and how to obtain the corresponding auto-increment ID after inserting the data, as follows:

Create Table atable (ID, a), where ID needs to be auto-incrementing, then we create a sequence:

Create sequence seq_atable minvalue 1 maxvalue 9999999 start with 1 increment by 1 nocache

There are two ways to use the auto-increment field:

Sequence and trigger are used for auto-increment, so the insert statement does not need to handle the auto-increment field.
For example, create or replace trigger trg_atable before insert on atable for each row begin select seq_atable.nextval into: New. ID from dual; end;

Insert data: insert into atable (a) values ('test ');

Note: I have created sequence and trigger:, and then inserted the data in procedure. During the insertion, no ID field is required. In the application, Hibernate is used, although hibernate also processes the ID when adding a record, after adding the record, the query record finds that the ID is set according to the sequence and trigger rules.

Only sequence is used. When inserting data, the auto-increment field inserts the next value of the sequence.
For example, insert into atable (ID, a) values (seq_atable.nextval, 'test ');

How can I get the corresponding auto-increment ID after inserting data?

First, we need to solve the problem of auto-increment fields. Which of the above two methods is more suitable for this use? We recommend that you use the second auto-incrementing sequence. Otherwise, it will be troublesome to solve this problem.

The second method of the auto-increment field is used. After a record is inserted, the following statement is executed immediately to return the ID of the currently inserted data.

$ Query = "select seq_atable.currval from dual ";

The value of seq_atable.currval is valid only after seq_atable.nextval occurs in the same session. Therefore, the error value is not obtained.

 

This article from: It Knowledge Network (http://www.itwis.com) detailed source reference: http://www.itwis.com/html/database/oracle/20100630/8645.html