PL/SQL中编写Oracle数据库分页的存储过程

2020-07-26 14:05:30

字体：大中小

来源：转载

供稿：网友

其实 Oracle数据库的分页还是比较容易理解的。此文以oracle数据库中的SCOTT用户的EMP表为例，用PL/SQL Developer编写一个分页存储过程，要求是：可以输入表名，每页显示记录数，当前页，返回总记录数，总页数和返回的结果集。

由于需要返回查询出来的结果集，需要在PL/SQL中创建一个package，这个包里面定义一个refcursor类型，用于记录sql语句查询出来的结果集。创建包的代码如下：

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

接下来开始Oracle的分页过程，我们可以用select emp.*,rownum from emp;来显示地表示出每行的行标。然后可以根据行标对内容进行分页，下面这个SQL语句可以作为Oracle分页的模板。

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

有了上面的refcursor类型和分页模板，下面开始编写分页的存储过程，代码如下：

create procedure paging (tableName in varchar2 ,--表名 pageSizes in number,--每页显示记录数 pageNow in number,--当前页 rowNums out number,--总记录数 pageNum out number,--总页数 paging_cursor out pagingPackage.paging_cursor) is  --定义部分 --定义sql语句，字符串 v_sql varchar2(1000); --定义两个整数，用于表示每页的开始和结束记录数 v_begin number:=(pageNow-1)*pageSizes+1; v_end number:=pageNow*pageSizes; begin  --执行部分  v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;  --把游标和sql语句关联  open paging_cursor for v_sql;  --计算rowNums和pageNum  --组织一个sql语句  v_sql:='select count(*) from '||tableName;  --执行该sql语句,并赋给rowNums  execute immediate v_sql into rowNums;  --计算pageNum  if mod(rowNums,pageSizes)=0 then   pageNum := rowNums/pageSizes;   else    pageNum := rowNums/pageSizes+1;    end if;  end;

在Java中编写代码测试分页，代码如下：

package com.test.oracletest; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; //测试分页 public class OraclePaging {   public static void main(String[] args) {     try {       // 加载驱动       Class.forName("oracle.jdbc.driver.OracleDriver");       // 获取连接       Connection connection = DriverManager.getConnection(           "jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "SCOTT", "tiger");       // 创建CallableStatement，调取数据库的存储过程       CallableStatement cst = connection           .prepareCall("{call paging(?,?,?,?,?,?)}");       // 给？赋值       cst.setString(1, "emp");       cst.setInt(2, 6);       cst.setInt(3, 2);       // 注册存储过程的输出项       cst.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);       cst.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);       cst.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);       // 执行       cst.execute();       // 获取输出项       int rowNums = cst.getInt(4);       int pageNum = cst.getInt(5);       ResultSet rs = (ResultSet) cst.getObject(6);       System.out.println("总记录数rowNums = " + rowNums);       System.out.println("总页数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 {       // 关闭资源     }   } }

执行的结果如图所示：