Oracle universal paging Stored Procedure

Oracle universal paging Stored Procedure

Oracle universal paging Stored Procedure

-- Create a package and define a cursor type in the package
Create or replace package testfenyepackage
Type test_cursor is ref cursor;
End testfenyepackage;

-- Compile the paging Process
Create or replace procedure fenyeTest
(
TableName varchar2, -- table name
PageSize number, -- number displayed per page
PageNow number, -- current page number
Outrows out number, -- total number of returned records
Pagecount out number, -- total number of pages
P_cursor out testfenyepackage. test_cursor -- returned record set
)
As
-- Definition
-- String of the first SQL statement
V_ SQL varchar2 (1000 );
-- Defines the integer variable at the beginning and end of the page.
V_begin number: = (pageNow-1) * pageSize + 1;
V_end number: = pageNow * pageSize;
Begin
-- Execution part
V_ SQL: = 'select * from (select t1. *, rownum rn from (select * from' | tableName | ') t1
Where rownum <= '| v_end |') where rn> = '| v_begin;
-- Associate the cursor with SQL
Open p_cursor for v_ SQL;
-- Calculate Outrows pagecount
-- Organize an SQL statement
V_ SQL: = 'select count (*) from' | tableName;
-- Execute the SQL statement and assign the returned result to Outrows
Execute immediate v_ SQL into Outrows;
-- Calculate the number of pages
If mod (Outrows, pageSize) = 0 then
Pagecount: = Outrows/pageSize;
Else
Pagecount: = Outrows/pageSize + 1;
End if;
End;

Use JDBC to call Oracle stored procedures and functions in Java

Local compilation of Oracle stored procedures

Use of Oracle stored procedures and REF CURSOR

Solution to prompt "insufficient Permissions" during Oracle Storage

Oracle uses the stored procedure to return the result set development report

Tips for using temporary tables in Oracle stored procedures