Paging of Oracle Stored Procedure Implementation

-- Book table
Create table book (
Book_id VARCHAR2 (20 ),
Book_name VARCHAR2 (100 ),
Book_publish VARCHAR2 (100)
);

-- In indicates the input parameter. The default value is in.
-- Out indicates the output parameter.
Create or replace procedure sp_pro7 (spBookId IN VARCHAR2, spBookName in VARCHAR2, spBookPub VARCHAR2)
IS
BEGIN
Insert into book VALUES (spBookId, spBookName, spBookPub );
END;

-- Returns a single value.
Create or replace procedure sp_pro8 (spBookId IN VARCHAR2, spBookName OUT VARCHAR2)
IS
BEGIN
SELECT t. book_name INTO spBookName FROM book t WHERE t. book_id = spBookId;
END;

-- Return result set
-- 1 create a cursor
Create or replace package testPackage
TYPE test_cursor is ref cursor;
END testPackage;
-- 2 creation process
Create or replace procedure sp_pro9 (spBookId VARCHAR2, sp_cursor OUT testPackage. test_cursor) IS
BEGIN
OPEN sp_cursor for select t. book_id, t. book_name, t. book_publish FROM book t;
END;
 
-- Paging
SELECT *
FROM (select rownum rn, T. * from book t where rownum <5)
Where rn> 2;

Create or replace procedure fenye (
Tablename VARCHAR2,
PageSize NUMBER, -- NUMBER of records displayed on one page
PageNow NUMBER, -- current page NUMBER
MyRows out number, -- total NUMBER of records
MyPageCount out number,
Sp_cursor OUT testPackage. test_cursor) IS
V_ SQL VARCHAR2 (1000 );
V_begin NUMBER: = (pageNow-1) * pageSize + 1;
V_end NUMBER: = (pageNow) * pageSize;
BEGIN
V_ SQL: = 'select * FROM (SELECT ROWNUM RN, T. * FROM '| tablename | 'twhere ROWNUM <' | v_end | ') where rn>' | v_begin;
OPEN sp_cursor FOR v_ SQL;
-- Calculate myrows mypagecount
V_ SQL: = 'select COUNT (*) from' | tablename;
-- Execute SQL quickly and assign the result to myrows
Execute immediate v_ SQL INTO myrows;
If mod (myrows, pageSize) = 0 THEN
MyPageCount: = myrows/pageSize;
ELSE
MyPageCount: = myrows/pageSize + 1;
End if;
-- Close the cursor
CLOSE sp_cursor;
END;

 

 

 

Package com. leeket;

Import java. SQL. CallableStatement;
Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. ResultSet;

/**
* @ Author Administrator
* @ Description pagination
*/
Public class TestProcedure4 {

Public static void main (String [] args ){

CallableStatement call = null;
Connection conn = null;
ResultSet rs = null;
Try {

Class. forName ("Oracle. jdbc. driver. OracleDriver ");
Conn = DriverManager
. GetConnection ("jdbc: oracle: thin: pas_perf/pas_perf @ localhost: 1521: orcl ");
Call = conn. prepareCall ("{call fenye (?,?,?,?,?,?)} ");
Call. setString (1, "BOOK ");
Call. setInt (2, 10 );
Call. setInt (3, 1 );
Call. registerOutParameter (4, oracle. jdbc. OracleTypes. INTEGER );
Call. registerOutParameter (5, oracle. jdbc. OracleTypes. INTEGER );
Call. registerOutParameter (6, oracle. jdbc. OracleTypes. CURSOR );
Call.exe cute ();
// Pay attention to the order of greetings When retrieving the returned values
Int num = call. getInt (4 );
Int myPageCount = call. getInt (5 );
Rs = (ResultSet) call. getObject (6 );
If (rs! = Null)
While (rs. next ()){
System. out. println (num + "\ t" + myPageCount + "\ t" + rs. getString (2) + "\ t" + rs. getString (3) + "\ t" + rs. getString (4 ));
}
} Catch (Exception e ){
E. printStackTrace ();
} Finally {
Try {
If (rs! = Null)
Rs. close ();
If (call! = Null)
Call. close ();
If (conn! = Null)
Conn. close ();
} Catch (Exception e ){
E. printStackTrace ();
}
}
}
}