Oracle paging statement introduction and paging Stored Procedure

-- Oracle paging statements
-- Id of each record
Select t1. *, rownum rn from (select * from emp) t1;
-- Retrieve the first 10 records
Select t1. *, rownum rn from (select * from emp) t1 where rownum <= 10;
-- Extract 6th to 10th records. This statement can be used as a paging template.
Select * from
(Select t1. *, rownum rn from (select * from emp) t1 where rownum <= 10)
Where rn> = 6;
-- Start the paging Process
-- 1. Create a package first. The test_cursor type defined in the package is a cursor.
Create or replace package testpackage
Type test_cursor is ref cursor;
End testpackage;
-- 2. Compile the paging Stored Procedure
Create or replace procedure fenye
(TableName in varchar2,
PageSize in number, -- maximum number of records per page
PageNow in number, -- current page number
MyRows out number, -- total number of records queried
MyPageCount out number, -- total number of pages of the queried records
P_cursor out testpackage. test_cursor -- returns the record set of the query result.
) Is
-- Definition
-- Define SQL statements
V_ SQL varchar2 (1000 );
-- Define two integers and assign values
V_begin number: = (pageNow-1) * pageSize + 1;
V_end number: = pageNow * pageSize;
Begin
-- Execution part
-- Arrange employees' salaries from high to low
V_ SQL: = 'select * from (select t1. *, rownum rn from (select * from' | tableName
| 'Order by sal) t1 where rownum <= '| v_end |') where rn> = '| v_begin;
Open p_cursor for v_ SQL;
-- Calculate myRows and myPageCount
-- Assign a value to v_ SQL again
V_ SQL: = 'select count (*) from' | tableName;
-- Execute the SQL statement and pay the returned value to myRows.
Execute immediate v_ SQL into myRows;
-- Calculate myPageCount
If mod (myRows, pageSize) = 0 then
MyPageCount: = myRows/pageSize;
Else
MyPageCount: = myRows/pageSize + 1;
End if;
-- Close the cursor
-- Close p_cursor;
End;

-- Use java code to call this stored procedure

Package test;
Import java. SQL .*;
Public class FenYe {
Public static void main (String [] args ){
Try {
// Load the database driver
Class. forName ("oracle. jdbc. driver. OracleDriver ");
// Obtain the database connection
Connection conn = DriverManager. getConnection (
"Jdbc: oracle: thin: @ 127.0.0.1: 1521: ORCL", "scott", "tiger ");
// Create a CallableStatement object
CallableStatement cs = conn. prepareCall ("{call fenye (?,?,?,?,?,?)} ");
// For the first three input parameters? Assignment
Cs. setString (1, "emp"); // The emp table to be queried
Cs. setInt (); // 5 records are displayed on each page
Cs. setInt (3, 1); // first display the first page
// Total number of registration records
Cs. registerOutParameter (4, oracle. jdbc. OracleTypes. INTEGER );
// Total number of registered pages
Cs. registerOutParameter (5, oracle. jdbc. OracleTypes. INTEGER );
// Register the result set
Cs. registerOutParameter (6, oracle. jdbc. OracleTypes. CURSOR );
// Execute the Stored Procedure
Cs.exe cute ();

// The total number of records returned for receiving
Int rowNum = cs. getInt (4 );
// The total number of pages returned for receiving
Int pageCount = cs. getInt (5 );
// Receives the returned result set
ResultSet rs = (ResultSet) cs. getObject (6 );

// Print the output result
System. out. println ("Total number of records:" + rowNum );
System. out. println ("total page number:" + pageCount );
System. out. println ("===========" + 5 + "entries per page ============ ");
System. out. println ("=========== current is the" + 1 + "Page ============== ");
Int I = 1;
While (rs. next ()){
System. out. println ("(" + (I ++) + ") No.:" + rs. getInt (1) + ", name:" + rs. getString (2) + ", salary:" + rs. getInt (6 ));
}
} Catch (Exception e ){
E. printStackTrace ();
}
}
}

It should be noted that the above Code is only for a simple demonstration of calling the paging stored procedure in the database, so the connection and shutdown of the database resources are optimized (or even the connection is not closed, close the result set ).

In actual development, you must disable and release database resources.