java调用存储过程实现分页功能

创建程序包：

SQL> create or replace package mypack
  2  as
  3  type emp_cursor is ref cursor;
  4  end mypack;
  5  /

程序包已创建。

创建存储过程：

create or replace procedure pro_emp(
--传入表名
v_in_tableName in varchar2,
--传人每页显示几条记录
v_in_pageSize in number,
--传入显示第几页的记录
v_in_pageNow in number,
--输出总共多少页
v_out_pageCount out number,
--输出总共有多少条记录
v_out_myRows out number,
--输出当页的记录
p_cursor out mypack.emp_cursor)
as
v_sql varchar2(1000);
v_begin number(4):=(v_in_pageNow-1)*v_in_pageSize+1;
v_end number(4):=v_in_pageNow*v_in_pageSize;
begin
--oracle里字符串用单引号，赋值用:=，连接用||
v_sql:='select * from (select t.*,rownum rn from (select * from '||v_in_tableName||' ) t where rownum<='||v_end||' ) where rn>='||v_begin;
open p_cursor for v_sql;
v_sql:='select count(*) from '||v_in_tableName;
--将执行结果写入v_out_myRows
execute immediate v_sql into v_out_myRows;
--注意oracle里取余必须用mod()函数，判断是否等于0用=，不是==
if mod(v_out_myRows,v_in_pageSize)=0 then
v_out_pageCount:=v_out_myRows/v_in_pageSize;
else
v_out_pageCount:=v_out_myRows/v_in_pageSize+1;
end if;
--close p_cursor;
end pro_emp;
/

java调用存储过程实现分页：

import java.sql.*;

public class oracleFenYe {
	public static void main(String[] args) {
		Connection conn=null;
		CallableStatement cstmt=null;
		ResultSet rs=null;
		try{
			//注册驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//获取连接
			conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORA","scott","tiger");
			//调用存储过程
			cstmt=conn.prepareCall("{call pro_emp(?,?,?,?,?,?)}");
			//对?赋值
			cstmt.setString(1, "EMP");
			cstmt.setInt(2, 3);
			cstmt.setInt(3, 2);
			cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
			cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
			cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);	
			//执行
			cstmt.execute();
			//获取结果
			int myCounts=cstmt.getInt(4);
			System.out.println("总页数为："+myCounts);
			int myRows=cstmt.getInt(5);
			System.out.println("总记录数为："+myRows);
			rs=(ResultSet)cstmt.getObject(6);
			while(rs.next()){
				System.out.println("用户名为："+rs.getString(2)+" 职位为："+rs.getString(3)+" 薪水为"+rs.getInt(6));
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				//逆序关闭资源
				rs.close();
				cstmt.close();
				conn.close();
			}
			catch(Exception e1){
				e1.printStackTrace();
			}
		}
	}
}

亮成果：

最后解释一下基于rownum分页语句：

SQL> select * from (select a.*,rownum rn from (select ename,sal from emp) a where rownum < 10) where
 rn > 5;

ENAME             SAL         RN
---------- ---------- ----------
BLAKE            2850          6
CLARK            2450          7
SCOTT            3000          8
KING             7000          9

        之所以内存查询语句使用rownum<10而外层查询语句使用rn>5，这是因为内存查询：select a.*,rownum rn from (select ename,sal from emp) a where rownum < 10的执行顺序是先执行from找到数据源，再执行where字句筛选部分数据，最后执行select，返回结果集，这里使用rownum是因为此时的rownum是在select查找出结果集以后再加上rownum这个伪列，这整个当成一个结果集作为外层select语句的数据源，此时那个rownum列已经是结果集的一部分了，而不再是伪列，而rownum是oracle的关键词，所以为了区分外层就用rn这个别名来筛选数据了，如果继续使用rownum就会出错了....

遇到的Bug:

java.sql.SQLException: 索引中丢失  IN 或 OUT 参数:: 3

解决办法：存储过程第3个参数忘记赋值或者忘记注册，也有可能是顺序搞错或者类型搞错

java.sql.SQLException: Cursor is closed.

解决办法：在存储过程中取消关闭游标即可。因为java程序需要调用游标来获取结果集，如果关闭了就无法获取结果了

欢迎关注行者摩罗微信公众号(xingzhemoluo)，共同交流编程经验，扫描下方二维码即可;