Oracle paging Stored Procedure

-- Create a package specification
Create or replace package package_page
Type cursor_page is ref cursor;
Procedure proc_page (
P_curpage number, -- current page
P_pagesize number, -- size of each page
P_tablename varchar2, -- table name EMP E
P_where varchar2, -- Query condition E. ename like '% S %'
P_tablecolumn varchar2, -- Query column E. ID, E. ename, E. Job
P_order varchar2, -- Sort E. ename DESC
P_rowcount out number, -- total number of items, output parameter
P_pagecount out number, -- total number of pages
P_cursor out cursor_page); -- result set
End package_page;

-- Create a package master break
Create or replace package body package_page
Is
-- Stored Procedure
Procedure proc_page (

P_curpage number, -- current page
P_pagesize number, -- size of each page
P_tablename varchar2, -- table name EMP E
P_where varchar2, -- Query condition E. ename like '% S %'
P_tablecolumn varchar2, -- Query column E. ID, E. ename, E. Job
P_order varchar2, -- Sort E. ename DESC
P_rowcount out number, -- total number of items, output parameter
P_pagecount out number, -- total number of pages
P_cursor out cursor_page -- result set
)
Is
V_count_ SQL varchar2 (2000 );
V_select_ SQL varchar2 (2000 );
Begin
-- Query the total number of items
V_count_ SQL: = 'select count (*) from' | p_tablename;
-- Connection query condition (''also belongs to is null)
If p_where is not null then
V_count_ SQL: = v_count_ SQL | 'where' | p_where;
End if;
-- Execute the query to query the total number of items
Execute immediate v_count_ SQL into p_rowcount;

-- Dbms_output.put_line ('Total number of queries SQL => '| v_count_ SQL );
-- Dbms_output.put_line ('Total number of queries COUNT = '| p_rowcount );

-- Get the total number of pages
If Mod (p_rowcount, p_pagesize) = 0 then
P_pagecount: = p_rowcount/p_pagesize;
Else
P_pagecount: = p_rowcount/p_pagesize + 1;
End if;

-- If the query record is greater than 0, the query result set is displayed.
If p_rowcount> 0 and p_curpage> = 1 and p_curpage <= p_pagecount then

-- Query all (only one page)
If p_rowcount <= p_pagesize then
V_select_ SQL: = 'select' | p_tablecolumn | 'from' | p_tablename;
If p_where is not null then
V_select_ SQL: = v_select_ SQL | 'where' | p_where;
End if;
If p_order is not null then
V_select_ SQL: = v_select_ SQL | 'ORDER BY' | p_order;
End if;
Elsif p_curpage = 1 then -- query the first page
V_select_ SQL: = 'select' | p_tablecolumn | 'from' | p_tablename;
If p_where is not null then
V_select_ SQL: = v_select_ SQL | 'where' | p_where | 'and rownum <=' | p_pagesize;
Else
V_select_ SQL: = v_select_ SQL | 'where rownum <= '| p_pagesize;
End if;
If p_order is not null then
V_select_ SQL: = v_select_ SQL | 'ORDER BY' | p_order;
End if;
Else -- query a specified page
V_select_ SQL: = 'select * from (select '| p_tablecolumn |', rownum row_num from '| p_tablename;
If p_where is not null then
V_select_ SQL: = v_select_ SQL | 'where' | p_where;
End if;
If p_order is not null then
V_select_ SQL: = v_select_ SQL | 'ORDER BY' | p_order;
End if;
V_select_ SQL: = v_select_ SQL | ') Where row_num>' | (p_curpage-1) * p_pagesize | 'and row_num <=' | (p_curpage * p_pagesize );
End if;
-- Execute Query
Dbms_output.put_line ('query Statement => '| v_select_ SQL );
Open p_cursor for v_select_ SQL;
End if;

End proc_page;
End package_page;

---------------- Test ----------------
Declare
V_rowcount number (5, 0 );
V_pagecount number;
V_cursor package_page.cursor_page;
Begin
Package_page.proc_page (, 'empe', 'ename like ''% S % ''', 'e. * ', 'ename DESC', v_rowcount, v_pagecount, v_cursor );
Dbms_output.put_line (v_rowcount );
Dbms_output.put_line (v_pagecount );

 

 

 

 

 

-- After freedom is modified --

 

 

-- Create a package specification
Create or replace package package_page
Type cursor_page is ref cursor;
Procedure proc_page (
P_select_ SQL varchar2, -- query statement
P_count_ SQL varchar2, -- Statement for querying the total number of records
P_rowcount out number, -- total number of rows
Cursor_page out package_page.cursor_page); -- result set
End package_page;

-- Create a package master break

Create or replace package body package_page is
-- Stored Procedure
Procedure proc_page (p_select_ SQL varchar2,
P_count_ SQL varchar2,
P_rowcount out number,
Cursor_page out package_page.cursor_page) is

Begin
-- Query the total number of items
-- Execute the query to query the total number of items
Execute immediate p_count_ SQL
Into p_rowcount;

-- If the query record is greater than 0, the query result set is displayed.
If p_rowcount> 0 then
-- Execute Query
Open cursor_page for p_select_ SQL;
Else
Open cursor_page for select * from dual;
End if;
End proc_page;
End package_page;