Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to achieve PL/SQL to write Oracle database paging stored procedures", the content is simple and easy to understand, organized clearly, I hope to help you solve doubts, the following let Xiaobian lead you to study and learn "how to achieve PL/SQL to write Oracle database paging stored procedures" this article bar.

Oracle database paging is easy to understand. This article to oracle database SCOTT user EMP table, for example, with PL/SQL Developer to write a paging stored procedure, requirements are: you can enter the table name, the number of records displayed per page, the current page, the total number of records returned, the total number of pages and the results returned set.

Because you need to return the result set from the query, you need to create a package in PL/SQL. This package defines a refcursor type to record the result set from the sql statement query. The code to create the package is as follows:

create or replace package pagingPackage as type paging_cursor is ref cursor; end pagingPackage;

Next we start Oracle paging process, we can use select emp.*, rownum from emp; to show the line markers for each row. You can then paginate the content according to the row label. The following SQL statement can be used as a template for Oracle pagination.

select * from (select t1.*, rownum rn from (select * from emp) t1 where rownum=8;

With the refcursor type and paging template above, let's start writing the paging storage procedure. The code is as follows:

create procedure paging (tableName in varchar2 ,--tableNamepageSizes in number,--pageNumber in number, --rowNums out number,--pageNumber out number,--pagingPackage.paging_cursor) is --definitionpart--definitionsql statement, stringv_sql varchar2(1000);--Define two integers to represent the number of records at the beginning and end of each page v_begin number:=(pageNow-1)*pageSizes+1; v_end number:=pageNow*pageSizes; begin --execution part v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum='||v_begin; --Associate cursors with sql statements open paging_cursor for v_sql; --Calculate rowNumbers and pageNum --Organize a sql statement v_sql: ='select count(*) from '|| tableName; --Execute immediate v_sql into rowNumbers; --Compute pageNum if mod(rowNumbers,pageSizes)=0 then pageNum := rowNums/pageSizes; else pageNum := rowNums/pageSizes+1; end if; end;

Write code to test pagination in Java, as follows:

package com.test.oracletest; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet;//test paging public class OraclePaging { public static void main(String[] args) { try { //load driver Class.forName("oracle.jdbc.driver.OracleDriver"); //Get connections Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "SCOTT", "tiger"); //Create CallableStatement to retrieve stored procedures of database CallableStatement cst = connection .prepareCall("{call paging(?,?,?,?,?,?)} "); //To? assignment cst.setString(1, "emp"); cst.setInt(2, 6); cst.setInt(3, 2); //Register the output of the stored procedure cst.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); cst.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); cst.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); //Implementation cst.execute(); //Get output items int rowNums = cst.getInt(4); int pageNum = cst.getInt(5); ResultSet rs = (ResultSet) cst.getObject(6); System.out.println("rowNumbers = " + rowNumbers); System.out.println("Total number of pages pageNum = " + pageNum); System.out.println("EMPNO" + '\t' + "ENAME" + '\t' + "ROWNUM"); while (rs.next()) { System.out.println(rs.getInt("EMPNO") + " " + '\t' + rs.getString("ENAME") + '\t' + rs.getInt("RN")); } } catch (Exception e) { e.printStackTrace(); } finally { //Close Resources } } }

The results of the implementation are shown in the figure below:

We can also modify the innermost view of the template to meet some other basic sorting requirements. This is Oracle's pagination philosophy.#

The above is "how to achieve PL/SQL to write Oracle database paging stored procedures" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.