Oracle paging Great SQL statements _oracle

CREATE OR REPLACE PROCEDURE PROC6338196642095312503719 (Enter the news subject VARCHAR2, enter the news content Varchar2, enter the publication time Varchar2, enter the current page number, Enter the number of rows per page, output the current page number out number, output the Total row count out, the output page number of pages out, enter whether the next page, enter the news numbers varchar2,return_cursor out custo Mtype. Myrctype)
--Functional Description:
--Author:
--Date of writing:

--If you return a result set, you must use a custom cursor return_cursor
Is--or as

--Variable definition area

V_cpagecount integer; --The total number of rows to display
V_cpage integer; --To display the current page of the data

BEGIN

--Stored Procedure body
If enter the news number is NULL then
Begin
---output row number of rows
Select Max (rownum) into the output row count from (
SELECT * FROM Xtnews where 1=1
and enter the news topic is NULL or (enter the news topic is not null and v_xwzt like '% ') | | Enter News Topic | | ' %')
and enter the publish time is null or (enter the publish time is not null and D_FBSJ = to_date (Enter publish time, ' Yyyy-mm-dd '))
Where you enter the news content is null or (input news content is not null and V_XWNR like '% ' | | Enter News content | | ' %');

--Output Total page pages
Select Ceil (output Total row/input per page line) into output page number from dual;
Exception when No_data_found then
Null
End
--Calculates the total number of rows to display for the current page number
If enter current page is not NULL then
--Xia one page
If enter whether next page = 1 Then
--Calculates the current page of the fetch data
V_cpage: = (enter current page number + 1);
--Last page
If v_cpage > output Total pages then
V_cpage: = output total page pages;
End If;
End If;
--Shang One page
If enter whether next page = 0 Then
--Calculates the current page of the fetch data
V_cpage: = (enter current page number-1);
--The first page
If V_cpage = 0 Then
V_cpage: = 1;
End If;
End If;
--The total number of rows to display
V_cpagecount: = v_cpage * Enter the number of rows per page;
End If;
End If;
--Execute the query to get the data to be displayed
Begin
Open Return_cursor for
Select nts.* from (
Select Nt.* from (
Select rownum serial number, n.* from (
SELECT * FROM (
SELECT * FROM (
Select
I_ID News number,
V_XWZT News Theme,
V_XWNR news content,
D_FBSJ release Time,
D_YXSJ Effective Time,
V_FBBM Publishing Department
From Xtnews
where 1=1 and enter the news topic is NULL or (enter the news topic is not null and v_xwzt like '% ' | | Enter News Topic | | ' %')
Where input news content is null or (input news content is not null and news content like '% ' | | Enter News content | | ' %')
Where enter the publish time is null or (enter publication time is not null and publish time = To_date (enter publish time, ' Yyyy-mm-dd '))
) n where type the news number is null or (enter the news number is not NULL and news number = Enter the news number)
ORDER BY RowNum
) NT where NT. Ordinal <= v_cpagecount ORDER BY ordinal desc
) NTS where NTS. Ordinal > (v_cpagecount-Enter the number of rows per page) by ordinal;
Exception when No_data_found then
Null
End

--Output the last calculated current page number
If enter the news number is null and v_cpage are NOT NULL then
Output current page number: = V_cpage;
End If;

End;