Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve paging query in SQLServer and Oracle, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. Paging algorithm

At first, when I was looking for information on the Internet, I saw a lot of paged content and felt a lot of confusion. Actually, that's not the case. The online materials are more or less the same. The problem is with me. I don't understand what is the premise of paging. We all know that whenever there is a page, these variables are involved: how many records per page (pageSize), current page (pageNow), total number of records (totalRecords), total number of pages (totalPages), start page (beginRow), end page (endRow). Those data paging algorithms on the Internet are useful to pageSize, beginPage and endPage. In fact, these variables need to be classified: I divide them into three categories: a. Need to be queried from the database: totalRecords. "select count (*) from tableName" B. The most basic needs to be provided by users: pageSize and pageNow. (personally, I think this is the premise of the paging algorithm) C. Calculated from other variables: totalPages, beginRow, and endRow. (here you need to calculate that beginRow and endRow are used in the paging query, and totalPages is the information that the page needs to provide.) Specific calculation formula:

TotalPages: if ((totalRecords% pageSize) = = 0) {totalPages = totalRecords/ pageSize;} else {totalPages = totalRecords/ pageSize + 1;} beginRow: (pageNow-1) * PageSize + 1 endRow: pageNow * PageSize

So that the values of these variables can be obtained. Please go on to see parts 2 and 3 on how to use it.

Common paging methods in 2.Oracle

In fact, whether it is Oracle or SQLServer, the implementation of paging queries are based on subqueries. In my own words: select is set in select. There are three ways of Oracle paging. I'm only going to talk about one that is easy to understand. Take the staff table (emp) as an example. Assuming that there are 10 records, paging now requires 5 records per page, and the current page is 2. 5. The query shows that the record is 6-10. Let's do it with a specific number, and then change it to a variable.

The first step of Oracle implementation:

Select a. The journal rownum rn from (select * from emp) a, where rownum is the line number assigned within Oracle. The select * from emp in parentheses queries all the records in the emp table. Then we further query the query results as views. The outer select adds a rownum in addition to querying all of the emp for use by subsequent queries.

The second step of Oracle implementation:

Select a. The journal Rownum rn from (select * from emp) a where rownum=6 and rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.