Oracle过程分页返回的行比预期的多

Question

这是一个用于示例目的的简化过程。我遇到的问题是，当我转到应用程序的下一页时，它会带回5，然后10，然后15，然后10。每次我只想带5个回来。它似乎发生在日期下降的时候。

procedure GET_DATA( p_sort_col IN VARCHAR2, p_sort_order IN VARCHAR2,
p_page_index IN NUMBER DEFAULT null,
p_page_size IN NUMBER DEFAULT null, p_cursor  out l_cursor)
AS
begin
OPEN p_cursor FOR
select * from (
select* from (select rownum rn, Name, DateCol, ROW_NUMBER() Over( ORDER BY 
CASE
WHEN p_sort_col = 'Name' and p_sort_order = 'asc' THEN
Name
END ASC,
CASE 
WHEN p_sort_col = 'DateCol' and p_sort_order = 'asc' THEN
DateCol
END ASC,
CASE 
WHEN p_sort_col = 'Name' and p_sort_order = 'desc' THEN
Name
END DESC,
CASE 
WHEN p_sort_col = 'DateCol' and p_sort_order = 'desc' THEN
DateCol
END DESC) from gdpr_document_manager_audit  
) where  rownum < ((p_page_index * p_page_size) + 1 )         
)WHERE rn >= (((p_page_index-1) * p_page_size));
END GET_DATA;

实际查询

Answer 1

我假设

select event_type, doc_page, application_number, 
document_reference, username, application_year, 
full_name, date_and_time
from gdpr_document_manager_audit
ORDER BY 
CASE
WHEN p_sort_col = 'EventType' and p_sort_order = 'asc' THEN event_type 
WHEN p_sort_col = 'ApplicationYear' and p_sort_order = 'asc' THEN application_year 
WHEN p_sort_col = 'ApplicationNumber' and p_sort_order = 'asc' THEN application_number 
WHEN p_sort_col = 'DocumentReference' and p_sort_order = 'asc' THEN doument_reference 
WHEN p_sort_col = 'Username' and p_sort_order = 'asc' THEN username 
WHEN p_sort_col = 'Name' and p_sort_order = 'asc' THEN full_name 
END ASC,
CASE 
WHEN p_sort_col = 'DateAndTime' and p_sort_order = 'asc' THEN date_and_time
END ASC,
CASE 
WHEN p_sort_col = 'EventType' and p_sort_order = 'desc' THEN event_type 
WHEN p_sort_col = 'ApplicationYear' and p_sort_order = 'desc' THEN application_year 
WHEN p_sort_col = 'ApplicationNumber' and p_sort_order = 'desc' THEN application_number 
WHEN p_sort_col = 'DocumentReference' and p_sort_order = 'desc' THEN document_reference 
WHEN p_sort_col = 'Username' and p_sort_order = 'desc' THEN username 
WHEN p_sort_col = 'Name' and p_sort_order = 'desc' THEN full_name 
END DESC,
CASE 
WHEN p_sort_col = 'DateAndTime' and p_sort_order = 'desc' THEN date_and_time
END DESC

按您想要的顺序返回您想要的数据，只是没有分页。为了简单起见，我将在下面的回答中将此查询称为#base_query#。如果是这样，并且考虑到您需要支持旧版本的Oracle，您可以将其转换为分页查询

。

select * 
from ( select /*+ FIRST_ROWS(n) */ 
a.*, ROWNUM rnum 
from ( #base_query# ) a 
where ROWNUM <= ((p_page_index * p_page_size) + p_page_size ) ) 
where rnum  >= (((p_page_index-1) * p_page_size));

使用您想要返回的最小和最大行的计算。您还没有告诉我们您为各种参数传递了什么值。如果我们猜测第一页的p_page_index为1(有些人使用基于0的索引)，您实际上可能需要

select * 
from ( select /*+ FIRST_ROWS(n) */ 
a.*, ROWNUM rnum 
from ( #base_query# ) a 
where ROWNUM <= (p_page_index * p_page_size) 
where rnum  >= (p_page_index-1) * p_page_size + 1;

所以如果p_page_index= 1和p_page_size= 10，你会得到第1到第10行。如果p_page_index= 2和p_page_size= 10，您将得到第11至20行。等。