Detailed Data paging in Oracle

Today when you do the project to achieve paging functionality, previously only on MySQL, Oracle has not tried, but know that there is such a rownum this thing.

But this thing is not so easy to use, there are quite a lot of places to pay attention to. It is not a physical column, but is added by Oracle itself in the result set.

First, let's take a look at our table structure first:

Let's create a new table:

It's just a field ID, which makes it easy for us to see the results directly.

Let's just insert a few of the data:

Let's get in 5 first.

Let's go straight to one:

SQL code

1. Select Rownum,id from Test;

We see the results:

I'm glad it's all the same, ronwum and ID are the same, easy to see.

But the results will probably make you depressed.

To continue down, we delete several data and insert a few more:

To see the results, we deleted the following two, 4,5 the two records, and inserted the 8,9 two records. We'll do the query again:

SQL code

1. Select Rownum,id from Test;

We see that the ID for 8,9 has replaced the previous 4,5 to get RowNum 4, 5. What this illustrates is that rownum is not physically present, and if it is physical, it will definitely delete the 4,5 of rownum as 4,5 deletes, but it does not, but instead takes the rownum of the newly inserted record as this value, This means that rownum is definitely just a logical column, it has a special name--pseudo column.

Here we continue inserting the data to facilitate the following experiment:

What would we do if we needed to get the first 5 records? We see that the previous rownum are assigned based on the results we have found, and then we know that maybe we can:

SQL code

1. Select Rownum,id from Test where rownum the <= 5 order by ID;

But the cup is, we are wrong, look at the results:

Why is it wrong?

The reason is that rownum will be numbered before we can find out the results are not sorted. Because this is the reason, we just add a word query on the OK.

SQL code

1. Select Rownum,id from (SELECT * from test-ID) where rownum <= 5;

Let's look at the results:

Now no problem, has been sorted according to RowNum, that is, to achieve our requirements, identified the top 5.

Do not be happy too early, check the top 5 no problem, the middle of the record, the 2nd to 5th one, or greater than 5. Let's take a look at:

SQL code

1. Select Rownum,id from (SELECT * from test-ID) where rownum >=1 and rownum <= 4;

Look at the results:

This has data, and normal, very good.

But do not be happy, we usually do not page as long as the first one, if the middle of it? Let's see:

SQL code

1. Select Rownum,id from (SELECT * from test-ID) where rownum >=1 and rownum <= 4;

Don't plunge your eyes this time:

Cups.

Why do we have the >=1 we just got, and now >=2 not?

The reason is that Oracle will assign values from 1 when the assignment is rownum, and when we do rownum >=1, because =1 this condition is established, it can continue to take down a rownum, continue to assign value to 2, successive assignment.

And when we use RowNum >=2, because =2 this condition is not tenable, because when take the first rownum=1, will discard it, and when the next one, rownum or 1, or not satisfied, has been such a cycle, the final result is no data can be detected.

But we are paging really want to do so, how to do?

In fact, is also simple, or subquery way, not directly with rownum that we are the rownum of the query in the name of an alias and then through it to limit the OK.

Let's see:

SQL code

1. Select Rn,id from (select RowNum as Rn,id to (SELECT * from test) where RN >=2 and RN <= 4;

Perhaps a lot of friends can not see what the meaning, we first look at the results:

It's normal, no problem.

But why would that be okay? In fact, we in the subquery above, directly to the rownum with RN as an alias, it is completely recorded, here we use it to limit the condition is not the original rownum. The reason to use two subqueries is because the first sort needs to be a subquery to get the correct rownum to define the alias.

Believe that this, everyone should know how to use Oracle to achieve pagination