I am assuming that
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
returns the data you want in the order you want, just without pagination. I'm going to refer to this query as #base_query#
in my answer below for simplicity. If so, and given that you you need to support an older version of Oracle, you'd convert that to a paginated query as
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));
using the calculations you have for the minimum and maximum row that you want returned. You haven't told us what values you are passing for the various parameters. If we guess that p_page_index
is 1 for the first page (some people use 0-based indexing), you'd probably actually want
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;
So if p_page_index
= 1 and p_page_size
= 10, you'd get rows 1 to 10. If p_page_index
= 2 and p_page_size
= 10, you'd get rows 11 to 20. etc.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…