使用 Oracle 进行分页 [英] Paging with Oracle

问题描述

我对 Oracle 并不像我想的那样熟悉.我有大约 25 万条记录，我想每页显示 100 条记录.目前我有一个存储过程，它使用数据适配器、数据集和 dataadapter.Fill(dataset) 方法将一百万条记录中的所有四分之一检索到数据集.如果我将页码"和每页记录数"作为整数值，我可以作为参数传递，那么返回该特定部分的最佳方法是什么.比如说，如果我通过 10 作为页码，120 作为页数，从 select 语句中它会给我第 1880 到 1200，或者类似的东西，我脑子里的数学可能会出错.

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.

我在 .NET 中用 C# 做这件事，认为这并不重要，如果我能在 sql 方面做到这一点，那么我应该很酷.

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.

更新:我能够使用布赖恩的建议，而且效果很好.我想进行一些优化，但页面会在 4 到 5 秒而不是一分钟内出现，而且我的分页控件能够与我的新存储过程很好地集成.

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.

推荐答案

这样的事情应该可以工作:来自 Frans Bouma 的博客

Something like this should work: From Frans Bouma's Blog

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

这篇关于使用 Oracle 进行分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！