SQL statements with excellent oracle Paging

Create or replace procedure PROC6338196642095312503719 (enter the news topic Varchar2, enter the news content Varchar2, enter the publishing time Varchar2, enter the current page Number, enter the Number of lines per page, and output the current page Number OUT, output total Number of rows OUT Number, total output page Number OUT Number, whether to enter the next Number, input news Number Varchar2, RETURN_CURSOR out customtype. MYRCTYPE)
-- Function Description:
-- Prepared:
-- Writing Date:

-- If a result set is returned, the custom cursor Return_Cursor must be used.
IS -- OR

-- Variable definition Area

V_cPageCount integer; -- total number of data rows to be displayed
V_cPage integer; -- display the current page of data

BEGIN

-- Stored procedure subject
If the input news number is null then
Begin
--- Number of output rows
Select max (rownum) into output the total number of rows from (
Select * from xtnews where 1 = 1
And input news topic is null or (input news topic is not null and V_XWZT like '%' | input news topic | '% ')
And input the release time is null or (input release time is not null and D_FBSJ = to_date (input release time, 'yyyy-mm-dd '))
) Where input news content is null or (input news content is not null and V_XWNR like '%' | input news content | '% ');

-- Total output page count
Select ceil (total number of output rows/number of input rows per page) into total number of output pages from dual;
Exception when no_data_found then
Null;
End;
-- Calculates the total number of data rows to be displayed on the current page number.
If the current page number is not null then
-- Xia page 1
If the input is next page = 1 then
-- Calculate the current page for retrieving data
V_cPage: = (enter the current page number + 1 );
-- Last page
If v_cPage> total output page number then
V_cPage: = Total number of output pages;
End if;
End if;
-- Shang page
If the input is next page = 0 then
-- Calculate the current page for retrieving data
V_cPage: = (enter the current page number-1 );
-- Last page
If v_cPage = 0 then
V_cPage: = 1;
End if;
End if;
-- Total number of data rows to be displayed
V_cPageCount: = v_cPage * enter the number of lines per page;
End if;
End if;
-- Execute a query to obtain the data to be displayed
Begin
Open return_cursor
Select nt. * from (
Select nt. * from (
Select rownum number, n. * from (
Select * from (
Select * from (
Select
I _ID news number,
V_XWZT news topic,
V_XWNR news content,
D_FBSJ release date,
D_YXSJ effective time,
V_FBBM Release Department
From xtnews
Where 1 = 1 and input news topic is null or (input news topic is not null and V_XWZT like '%' | input news topic | '% ')
) Where input news content is null or (input news content is not null and news content like '%' | input news content | '% ')
) Where input publishing time is null or (input publishing time is not null and publishing time = to_date (input publishing time, 'yyyy-mm-dd '))
) N where enter 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. No. <= v_cPageCount order by no. desc
) CNT where CNT. No.> (v_cPageCount-enter the number of rows per page) order by no;
Exception when no_data_found then
Null;
End;

-- Output the last calculated current page number
If the input news number is null and v_cPage is not null then
Output Current page number: = v_cPage;
End if;

END;