Oracle practice -- PL/SQL integrated paging Stored Procedure

Source: Internet
Author: User

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 .}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.