Paging--rownum in the Oracle database

1. Introduction

When we are making queries, we often encounter requirements such as query-qualified rows or paging queries, MySQL can be completed using the limit clause, in the MSSQL can be done with the TOP clause, then in Oracle, how do we implement it?

Oracle provides a pseudo-column of rownum that generates a serialized number based on the return record.

RowNum and rowID are pseudo-columns, but the two are fundamentally different. RowNum is to assign a logical number to each row based on the results of the SQL query, so different SQL will cause the final rownum to be different; rowID is a physical structure that has a unique physical record when each record is insert into the database.

2. Limit the number of query rows

If you want to limit the first few data of a query result set, you can easily implement it through rownum.

Example:

-- 查找前三条员工的记录SELECT * FROM employee WHERE rownum <= 3;

3. Paging Query

In the database application system, we will often use the paging function, such as 5 records per page, query the 2nd page of how to query it?

SELECT * FROM employee WHERE rownum > 5 AND rownum <= 10;

Can the above SQL statement query the results we want?

When you execute the SQL, you will find that the results are disappointing: You cannot find a record, even if there are 20 records in the table. The question is, where is it?

Because RowNum is a pseudo-column that adds to the result set (that is, a column that is followed by a result set), simply rownum is the sequence number that is added to the qualifying result set. It always starts at 1, so it is impossible to choose the result without 1, and the other value is greater than 1.

RowNum > 5 and rownum <= 10 query is not recorded, because if the first of the rownum = 1, not satisfied with the condition is removed, the second rownum into 1, continue to judge, so never meet the conditions of the record.

Any time want to RowNum = 1 This record discards is not right, it is indispensable in the result set, less rownum=1 like castles in the castle can not exist, so your rownum conditions to include to 1.

Then, if you want to use RowNum > 5 This condition, you will use a subquery, the rownum, and then query the resulting results.

Example:

SELECT * FROM (   SELECT e.*, rownum r FROM employee WHERE rownum <= 10) t WHERE t.r > 5;

4. Precautions for using rownum

1. The rownum cannot be used with > (values greater than 1), >= (values greater than 1), = (values greater than 1), otherwise no result.
2. When using rownum, the query results are sorted and then evaluated rownum only if the order by field is the primary key, but the results may be confusing when the non-primary key fields (such as name) are sorted. The reason for this confusion is that Oracle takes the physical storage location (ROWID) order out of the records that meet the rownum condition, that is, the first 5 data in the physical location, and then sorts the data according to the order by field, rather than the first sort, and then the number of specific records we expect.

5 The following is the process of using a package to write a paged query

1 --Package Description2 CREATE OR REPLACEPackage Pkg_page is3TYPE Page_cur_type isREFCURSOR;4    PROCEDUREGet_page_rec (current_page Number, page_size Number, Page_rec out page_cur_type);5 ENDPkg_page;6 7 --Package Body8 CREATE OR REPLACEPackage BODY Pkg_page is9    --the process of paging a queryTen    PROCEDUREGet_page_rec (current_page Number, page_size Number, Page_rec out Page_cur_type) is OneLower_bound Number(4);--Record Lower number AUpper_bound Number(4);--record upper Limit number -      BEGIN            -Lower_bound:=(Current_page- 1)*page_size; theUpper_bound:=Current_page*page_size; -          -         OPENPage_rec for  -            SELECTID, name, birthday, address, did, salary from(   +              SELECTT1.*, RowNum R from -(SELECTID, name, birthday, address, did, salary fromEmployeeORDER  byname) T1 +              WHERERowNum<=Upper_bound A ) T at            WHERET.r>Lower_bound; -      ENDGet_page_rec; - ENDPkg_page; -  - --Test - DECLARE inPage_index Number(4) := 1;--Page Number -Page_size Number(4) := 4;--shows the number of records per page to Cur_var Pkg_page.page_cur_type; +REC Employee%ROWTYPE; - BEGIN the Pkg_page.get_page_rec (Page_index, Page_size, Cur_var); * LOOP $      FETCHCur_var intoRec;Panax Notoginseng      EXIT  whenCur_var%NOTFOUND; -Dbms_output. Put_Line ('Work No.:' ||Rec.id|| ', Name:' ||Rec.name|| ', Salary:' ||rec.salary); the    ENDLOOP; +    CLOSECur_var; A END;

Paging--rownum in the Oracle database