使用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.

我正在用C#在.NET中进行此操作，认为这并不重要，如果我可以在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.

更新:我能够使用Brian的建议，并且效果很好.我想进行一些优化，但是页面将​​在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.

推荐答案

类似的方法应该起作用:

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屋！