Oracle practice -- PL/SQL integrated paging Stored Procedure

Oracle, paging, and stored procedure are combined to create a paging stored procedure using PL/SQL.

1. Declare a referenced cursor

[SQL] view plaincopyprint? 01. create or replace package cur_ref_type
02. as -- is or as can be used
03. type cur_ref is ref cursor;
04. end;
Create or replace package cur_ref_type
As -- is or as can be used
Type cur_ref is ref cursor;
End; 2. Write the Stored Procedure

[SQL] view plaincopyprint? 01. create or replace procedure divideByPge
02. (tableName varchar2, pageSize number, currentPage number,
03. totalPage out number, totalRecord out number, results out cur_ref_type.cur_ref) -- bring the package name
04. is
05. v_ SQL varchar2 (200); -- used to write SQL statements
06. v_begin number; -- start position
07. v_end number; -- end position
08. begin
09. v_begin: = (currentPage-1) * pageSize;
10. v_end: = currentPage * pageSize;
11. v_ SQL: = 'select count (*) from' | tableName; -- concatenates a table name string into an SQL statement.
12. execute immediate v_ SQL into totalRecord; -- Query all records
13. totalPage: = ceil (totalRecord/pageSize); -- calculate the total number of pages
14. v_ SQL: = 'select * from (select rownum rn, '| tableName | '. * from '| tableName | 'where rownum <=' | v_end | ') where rn>' | v_begin;
15. open results for v_ SQL;
16. end;
Create or replace procedure divideByPge
(TableName varchar2, pageSize number, currentPage number,
TotalPage out number, totalRecord out number, results out cur_ref_type.cur_ref) -- package name
Is
V_ SQL varchar2 (200); -- used to write SQL statements
V_begin number; -- start position
V_end number; -- end position
Begin
V_begin: = (currentPage-1) * pageSize;
V_end: = currentPage * pageSize;
V_ SQL: = 'select count (*) from' | tableName; -- concatenates a table name string into an SQL statement.
Execute immediate v_ SQL into totalRecord; -- Query all records
TotalPage: = ceil (totalRecord/pageSize); -- calculate the total number of pages
V_ SQL: = 'select * from (select rownum rn, '| tableName | '. * from '| tableName | 'where rownum <=' | v_end | ') where rn>' | v_begin;
Open results for v_ SQL;
End; 3. Java client call

[Java] view plaincopyprint? 01. String driver = "oracle. jdbc. driver. OracleDriver ";
02. String url = "jdbc: oracle: thin: @ localhost: 1521: orcl ";
03. String username = "scott ";
04. String password = "ysjian ";
05. try {
06. Class. forName (driver );
07. Connection conn =
08. DriverManager. getConnection (url, username, password );
09. CallableStatement cs = conn. prepareCall ("{call
10. divideByPge (?,?,?,?,?,?)} ");
11. cs. setString (1, "emp"); // table name
12. cs. setInt (2, 3); // page unit
13. cs. setInt (3, 1); // current page
14. cs. registerOutParameter (4, OracleTypes. INTEGER); // register the output parameter
15. cs. registerOutParameter (5, OracleTypes. INTEGER );
16. cs. registerOutParameter (6, OracleTypes. CURSOR); // register the output parameter CURSOR
17. cs.exe cute (); // Execute
18. ResultSet rs = (ResultSet) cs. getObject (6); // obtain the cursor
19. while (rs. next ()){
20. int empno = rs. getInt ("empno ");
21. String ename = rs. getString ("ename ");
22. System. out. println (empno + "-->" + ename );
23 .}
24.} catch (ClassNotFoundException | SQLException e ){
25. e. printStackTrace ();
26 .}