Oracle paging stored procedures and calling scripts in PLSQL

Writing Process: I collected and tested a lot of Oracle paging stored procedure code online. After finishing the code, I finally passed the test. Here are the test steps: 1. Run the create package command; 2. Run the create Stored Procedure command; 3. Run the call paging Stored Procedure statement test environment: windows2003 + Oracle11g + PLSQLDeveloper -- 1. Create the package command createorr

Writing Process: I collected and tested a lot of Oracle paging stored procedure code online. After finishing the code, I finally passed the test. Here are the test steps: 1. Run the create package command; 2. Run the create Stored Procedure command; 3. Run the call paging Stored Procedure statement test environment: windows2003 + Oracle11g + PLSQL Developer -- 1. create or r

Writing Process: I collected and tested a lot of Oracle paging stored procedure code online. After finishing the code, I finally passed the test and shared it with you.

Test procedure: 1. Run the create package command; 2. Run the create Stored Procedure command; 3. Run the call paging Stored Procedure statement.

Test environment: windows2003 + Oracle11g + PLSQL Developer

-- 1. Create a package command

Create or replace package mypackage
Type cursor_page is ref cursor;
Procedure myprocdure (
P_TableName varchar2, -- table name
P_Fields varchar2, -- query a column
P_Orderby varchar2, -- Sort
P_Where varchar2, -- Query Condition
P_pagesize Number, -- size of each page
P_pageIndex Number, -- current page
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 mypackage;

-- 2. Create a stored procedure command

Create or replace Package Body mypackage
Is
-- Stored Procedure
Procedure myprocdure (
P_TableName varchar2, -- table name
P_Fields varchar2, -- query a column
P_Orderby varchar2, -- Sort
P_Where varchar2, -- Query Condition
P_pagesize Number, -- size of each page
P_pageIndex Number, -- current page
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 );

S_TableName nvarchar2 (255); -- Name of the paging table

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_pageIndex> = 1 and p_pageIndex <= p_pagecount then

-- Query all (only one page)
If p_rowcount <= p_pagesize then
V_select_ SQL: = 'select' | p_Fields | 'from' | p_TableName;
If p_Where is not null then
V_select_ SQL: = v_select_ SQL | 'where' | p_Where;
End if;
If p_Orderby is not null then
V_select_ SQL: = v_select_ SQL | 'ORDER BY' | p_Orderby;
End if;
Elsif p_pageIndex = 1 then -- query the first page
V_select_ SQL: = 'select' | p_Fields | '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_Orderby is not null then
V_select_ SQL: = v_select_ SQL | 'ORDER BY' | p_Orderby;
End if;
Else -- query a specified page

If instr (p_TableName, ')> 0 then
S_TableName: = replace (substr (p_TableName, instr (p_TableName, ') + 1 ),'','');
V_select_ SQL: = 'select * from (select '| s_TableName |'. '| p_Fields |', rownum row_num from '| p_TableName;
Else
V_select_ SQL: = 'select * from (select '| p_TableName |'. '| p_Fields |', rownum row_num from '| p_TableName;
End if;

If p_Where is not null then
V_select_ SQL: = v_select_ SQL | 'where' | p_Where;
End if;
If p_Orderby is not null then
V_select_ SQL: = v_select_ SQL | 'ORDER BY' | p_Orderby;
End if;
V_select_ SQL: = v_select_ SQL | ') where row_num>' | (p_pageIndex-1) * p_pagesize | 'and row_num <=' | (p_pageIndex * p_pagesize );
End if;
-- Execute Query
-- Dbms_output.put_line ('query Statement => '| v_select_ SQL );
Open p_cursor for v_select_ SQL;
Else
-- Dbms_output.put_line ('query Statement => '| 'select * from' | p_TableName | 'where 1! = 1 ');
Open p_cursor for 'select * from' | p_TableName | 'where 1! = 1 ';
End if;

End myprocdure;
End mypackage;


-- 3. Call the paging Stored Procedure statement
Declare
P_TableName varchar2 (2000 );
P_Fields varchar2 (2000 );
P_Orderby varchar2 (200 );
P_Where varchar2 (200 );
P_pagesize Number;
P_pageIndex Number;
P_rowcount Number;
P_pagecount number;
P_cursor mypackage. cursor_page;
Begin
P_TableName: = 'goodsdoc ';
P_Fields: = '*';
P_Orderby: = 'goodsname ';
P_Where: = '1 = 1 ';
P_pagesize: = 100;
P_pageIndex: = 1;
Mypackage. myprocdure (p_TableName, p_Fields, p_Orderby, p_Where, p_pagesize, p_pageIndex, p_rowcount, p_pagecount, p_cursor );
DBMS_OUTPUT.PUT_LINE ('total record count' | p_rowcount | 'total page count' | p_pagecount );
END;