blocks|key|1490553|text|这样的代码应该可以工作：From+Frans+Bouma's+Blog|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1490554|SELECT+*+FROM
(
++++SELECT+a.*,+rownum+r__
++++FROM
++++(
++++++++SELECT+*+FROM+ORDERS+WHERE+CustomerID+LIKE+'A%25'
++++++++ORDER+BY+OrderDate+DESC,+ShippingDate+DESC
++++)+a
++++WHERE+rownum+<+((pageNumber+*+pageSize)+%2B+1+)
)
WHERE+r__+>=+(((pageNumber-1)+*+pageSize)+%2B+1)|code-block|syntax|javascript|1490555|entityMap|0|LINK|mutability|MUTABLE|url|http://weblogs.asp.net/fbouma/archive/2007/05/21/api-s-and-production-code-shouldn-t-be-designed-by-scientists.aspx^0|C|N|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Something like this should work: <a href="http://weblogs.asp.net/fbouma/archive/2007/05/21/api-s-and-production-code-shouldn-t-be-designed-by-scientists.aspx" rel="noreferrer">From Frans Bouma's Blog</a> 

<pre><code>SELECT * FROM
(
 SELECT a.*, rownum r__
 FROM
 (
 SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
 ORDER BY OrderDate DESC, ShippingDate DESC
 ) a
 WHERE rownum &lt; ((pageNumber * pageSize) + 1 )
)
WHERE r__ &gt;= (((pageNumber-1) * pageSize) + 1)
</code></pre>

blocks|key|1490614|text|关于分页的Ask+Tom和非常非常有用的分析函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1490615|这是该页面的摘录：|1490616|select+*+from+(
++++select+/*%2B+first_rows(25)+*/
+++++object_id,object_name,
+++++row_number()+over
++++(order+by+object_id)+rn
++++from+all_objects
)
where+rn+between+:n+and+:m
order+by+rn;|code-block|syntax|javascript|1490617|entityMap|0|LINK|mutability|MUTABLE|url|https://blogs.oracle.com/oraclemagazine/post/on-top-n-and-pagination-queries^0|5|7|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

<a href="https://blogs.oracle.com/oraclemagazine/post/on-top-n-and-pagination-queries" rel="nofollow noreferrer">Ask Tom</a> on pagination and very, very useful analytic functions.
This is excerpt from that page:
<pre><code>select * from (
 select /*+ first_rows(25) */
 object_id,object_name,
 row_number() over
 (order by object_id) rn
 from all_objects
)
where rn between :n and :m
order by rn;
</code></pre>

blocks|key|1705691|text|尝试以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1705692|SELECT+*
FROM
++(SELECT+FIELDA,
++++FIELDB,
++++FIELDC,
++++ROW_NUMBER()+OVER+(ORDER+BY+FIELDC)+R
++FROM+TABLE_NAME
++WHERE+FIELDA+=+10
++)
WHERE+R+>=+10
AND+R+++<=+15;|code-block|syntax|javascript|1705693|通过+[tecnicume]|offset|length|1705694|entityMap|0|LINK|mutability|MUTABLE|url|http://mbfu.it/r/ta^0|0|0|2|C|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Try the following:

<pre><code>SELECT *
FROM
 (SELECT FIELDA,
 FIELDB,
 FIELDC,
 ROW_NUMBER() OVER (ORDER BY FIELDC) R
 FROM TABLE_NAME
 WHERE FIELDA = 10
 )
WHERE R &gt;= 10
AND R &lt;= 15;
</code></pre>

via<a href="http://mbfu.it/r/ta" rel="noreferrer"> [tecnicume]</a>

blocks|key|1705754|text|我只想总结一下答案和评论。有许多方法可以进行分页。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1705755|在oracle12c之前，没有偏移/获取功能，所以看看@jasonk建议的whitepaper。这是我找到的关于不同方法的最完整的文章，详细解释了各种方法的优缺点。在这里复制粘贴它们会花费大量的时间，所以我就不这么做了。|offset|length|1705756|jooq创建者也有一篇很好的文章，解释了oracle和其他数据库分页的一些常见注意事项。jooq's+blogpost|1705757|好消息是，从oracle+12c开始，我们有了一个新的偏移/获取功能。OracleMagazine+12c+new+features。请参考"Top-N查询和分页“|1705758|您可以通过发出以下语句来检查您的oracle版本|1705759|SELECT+*+FROM+V$VERSION|code-block|syntax|javascript|1705760|entityMap|0|LINK|mutability|MUTABLE|url|http://www.inf.unideb.hu/~gabora/pagination/article/Gabor_Andras_pagination_article.pdf|1|http://blog.jooq.org/2014/06/09/stop-trying-to-emulate-sql-offset-pagination-with-your-in-house-db-framework/|2|http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html^0|0|11|A|0|0|18|F|1|0|Z|V|2|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@$D|14|E|15|1|16]]|A|$]]|$1|F|3|G|5|6|7|17|8|@]|9|@$D|18|E|19|1|1A]]|A|$]]|$1|H|3|I|5|6|7|1B|8|@]|9|@$D|1C|E|1D|1|1E]]|A|$]]|$1|J|3|K|5|6|7|1F|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|1G|8|@]|9|@]|A|$O|P]]|$1|Q|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]|Y|$5|T|U|V|A|$W|Z]]|10|$5|T|U|V|A|$W|11]]]]

Just want to summarize the answers and comments. There are a number of ways doing a pagination.

Prior to oracle 12c there were no OFFSET/FETCH functionality, so take a look at <a href="http://www.inf.unideb.hu/~gabora/pagination/article/Gabor_Andras_pagination_article.pdf" rel="noreferrer">whitepaper</a> as the @jasonk suggested. It's the most complete article I found about different methods with detailed explanation of advantages and disadvantages. It would take a significant amount of time to copy-paste them here, so I won't do it.

There is also a good article from jooq creators explaining some common caveats with oracle and other databases pagination. <a href="http://blog.jooq.org/2014/06/09/stop-trying-to-emulate-sql-offset-pagination-with-your-in-house-db-framework/" rel="noreferrer">jooq's blogpost</a>

Good news, since oracle 12c we have a new OFFSET/FETCH functionality. <a href="http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html" rel="noreferrer">OracleMagazine 12c new features</a>. Please refer to "Top-N Queries and Pagination"

You may check your oracle version by issuing the following statement 

<pre><code>SELECT * FROM V$VERSION
</code></pre>

I am not as familiar with Oracle as I would like to be. I have some 250k records, and I want to display them 100 per page. Currently I have one stored procedure which retrieves all quarter of a million records to a dataset using a data adapter, and dataset, and the dataadapter.Fill(dataset) method on the results from the stored proc. If I have "Page Number" and "Number of records per page" as integer values I can pass as parameters, what would be the best way to get back just that particular section. Say, if I pass 10 as a page number, and 120 as number of pages, from the select statement it would give me the 1880th through 1200th, or something like that, my math in my head might be off. 

I'm doing this in .NET with C#, thought that's not important, if I can get it right on the sql side, then I should be cool. 

Update: I was able to use Brian's suggestion, and it is working great. I'd like to work on some optimization, but the pages are coming up in 4 to 5 seconds rather than a minute, and my paging control was able to integrate in very well with my new stored procs.

Paging with Oracle

数据库

我并不像我希望的那样熟悉Oracle。我有大约25万条记录，我想每页显示100条记录。目前，我有一个存储过程，它使用数据适配器和dataset将25万条记录检索到dataset，并使用dataadapter.Fill(dataset)方法对来自存储过程的结果进行处理。如果我将"Page Number“和"Number ...

问使用Oracle分页
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Oracle分页EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Oracle分页
EN