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