oracle存储过程分页以及在java中调用

/*******************************************************************/
>oracle存储过程分页以及在java中调用
/*******************************************************************/

0.常用的判断游标的函数
/*******************************************************************/
--%NOTFOUND  执行行没有找到。  
--%FOUND  执行行找到  
--%ROWCOUNT 游标影响行数  
--%ISOPEN 当前游标是否打开  
/*******************************************************************/

1.存储过程代码(注意动态游标指向结果集后不要关闭否则取不到结果
  如果在java中调用还会报出无效的ref游标exception)
/*******************************************************************/

create or replace package test_package as
type test_cursor_type is ref cursor;//定义动态游标类型
end;

create or replace procedure pro_pageing(
 v_table in varchar2,--//分页的表
 v_order in varchar2,--//排序
 v_pageSize in number,--//页大小
 v_pageNum in number,--//第几页
 v_sumPage out number,--//总页数
 v_sumRecord out number,--//总条数
 p_cursor out test_package.test_cursor_type--//分页后的结果集
)
is
v_sql varchar2(1000);
v_begin number:=(v_pageNum-1)*v_pageSize+1;
v_end number:=v_pageSize*v_pageNum;
begin
   v_sql:='select * from
 (select t1.*,rownum rn from (select * from '||v_table||')t1 where rownum<='||v_end||' order by sal '||v_order||')
 where rn>='||v_begin;
   --将游标指向结果集
   open p_cursor for v_sql;
   
   --查询表中总共有多少条记录
   v_sql:='select count(empno) from emp';
   execute immediate v_sql into v_sumRecord;--//执行动态sql然后将返回的值给变量
   
   --根据总记录数得到总共的页数
   if mod(v_sumRecord,v_pageSize)=0 then
   v_sumPage:=v_sumRecord/v_pageSize;
   else
    v_sumPage:=v_sumRecord/v_pageSize+1;
   end if;
   
   --注意这里不要关闭游标否则取不到结果
   --close p_cursor;
end;
/*******************************************************************/

2.pl/sql块调用代码
/*******************************************************************/
declare
v_sumPage number;--//总页数
v_sumRecord number;--//总条数
p_cursor test_package.test_cursor_type;
--v_row scott.emp%rowtype;--多了一列rownum
begin
 --执行存储过程前面不许要exec 只有函数的时候要
 scott.pro_pageing('emp','asc',5,1,v_sumPage,v_sumRecord,p_cursor);
 dbms_output.put_line(p_cursor%ROWCOUNT);
end;
/*******************************************************************/

3.java中的调用代码
/*******************************************************************/
public static void main(String[] args) {
		ResultSet rs = null;
		Connection conn = null;
		try {
			String username = "scott";
			String pwd = "root";
			String driver = "oracle.jdbc.driver.OracleDriver";
			String url = "jdbc:oracle:thin:@192.168.11.4:1521:orcl";

			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, pwd);

			// create or replace procedure pro_pageing(
			// v_table in varchar2,--//分页的表
			// v_order in varchar2,--//排序
			// v_pageSize in number,--//页大小
			// v_pageNum in number,--//第几页
			// v_sumPage out number,--//总页数
			// v_sumRecord out number,--//总条数
			// p_cursor out test_package.test_cursor_type--//分页后的结果集
			// )
			CallableStatement proc = conn
					.prepareCall("{call pro_pageing(?,?,?,?,?,?,?)}");
			// 将第一个参数注册为输出参数

			proc.setString(1, "emp");
			proc.setString(2, "desc");
			proc.setInt(3, 5);
			proc.setInt(4, 1);

			// 注册输出参数
			proc.registerOutParameter(5, OracleTypes.INTEGER);
			proc.registerOutParameter(6, OracleTypes.INTEGER);
			proc.registerOutParameter(7, OracleTypes.CURSOR);

			proc.execute();

			int v_sumPage = proc.getInt(5);
			int v_sumRecord = proc.getInt(6);

			System.out.println("总共有【" + v_sumPage + "】页");
			System.out.println("总共有【" + v_sumRecord + "】条");

			// 得到游标
			 rs= (ResultSet) proc.getObject(7);
			 
			while (rs.next()) {
				Double empno = rs.getDouble("empno");
				String ename = rs.getString("ename");
				String job = rs.getString("job");
				Double mgr = rs.getDouble("mgr");
				Date hiredate = rs.getDate("hiredate");
				Double sal = rs.getDouble("sal");
				Double comm = rs.getDouble("comm");
				Double deptno = rs.getDouble("deptno");
				System.out.println(empno + "\t" + ename + "\t" + job + "\t"
						+ mgr + "\t" + hiredate + "\t" + sal + "\t" + comm
						+ "\t" + deptno + "\t");
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
/*******************************************************************/