oracle pagination, increment fields, back insert record ID


And PG SQL mysql limit the different oracle pagination can use the following statement:

$ Query = ‘Select * from (Select A. *, RowNum Rn from (‘. $ Query. ‘) A Where RowNum <=’. $ End. ‘) Where Rn> =’. $ Start;


$ Query: is you need pagination SQL

Rownum: log sequence number returns a result set

$ Start $ end: starting serial number of the paging

Second, since the increase fields:

Id need to increment table atable (id, a) First, create a sequence:

create sequence seq_ atable minvalue 1 maxvalue 999999999999999999 start with 1 increment by 1 nocache

There are two ways to use the increment fields:

  1. Use sequence + trigger increment increment field insert statement does not require tube

Such as: 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’);

  1. Using only sequence, you need to insert data from the increase in value of a field insertion sequence

Such as: insert into atable (id, a) values ??(seq_ atable. Nextval, ‘test’);

Return just inserted record increment field values

Such as the above example, we insert a record, I want to immediately return the ID number of the newly inserted record, how can I deal with it?

We must first solve the problem of the increment field, the above two methods which is more suitable for this usage? Recommended to use the second increment sequence, otherwise deal with this problemfrom too much trouble.

Second increment field, insert a record immediately after the implementation of the following statement that returns the ID of the current insert data.

$ Query = “se lect seq_ atable. Currval from dual”;

seq_ atable. the currval value only in the same session, a seq_ atable of nextval within that session after the effective 🙂 So there is no problem to take the wrong value.