你的位置:首页 > Java教程

[Java教程]java调用Oracle分页存储过程


Java程序

package com.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;import com.jdbc.BaseJdbcDAO;import oracle.jdbc.OracleTypes;public class Test {/*** @param args*/public static void main(String[] args) {Connection conn = BaseJdbcDAO.getConnection2("com");String sqlSel = "SELECT * FROM POSITION";String sqlCount = "SELECT COUNT(*) FROM POSITION";String sql = "{ call SP_PAGE(?,?,?,?,?,?,?) }";ResultSet rs = null;try {CallableStatement call = conn.prepareCall(sql);call.setInt(1, 20);call.setInt(2, 100);call.setString(3, sqlSel);call.setString(4, sqlCount);call.registerOutParameter(5, Types.INTEGER);call.registerOutParameter(6, Types.INTEGER);call.registerOutParameter(7, OracleTypes.CURSOR);call.execute();// 取出结果集int pageCount = call.getInt(5);//总页数int total = call.getInt(6);//总记录数rs = (ResultSet) call.getObject(7);//信息列表集System.out.println("页总数:" + pageCount);System.out.println("记录总数:" + total);int i = 1;while (rs.next()) {System.out.println(">>" + i + "号码:" + rs.getString(2) + "名称:"+ rs.getString(3) + "序号:" + rs.getInt(1));i++;}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}/* 何问起 hovertree.com */

 

--创建游标包


create or replace PACKAGE "PKG_QUERY" AStype refCursorType is REF CURSOR; --游标类型定义,用于返回数据集END;/* 何问起 hovertree.com */

--存储过程

create or replace procedure sp_Page(p_PageSize int, --每页记录数p_PageNo int, --当前页码,从 1 开始p_SqlSelect varchar2, --查询语句,含排序部分p_SqlCount varchar2, --获取记录总数的查询语句p_pageCount out int,--总共多少页p_OutRecordCount out int,--返回总记录数p_OutCursor out PKG_QUERY.refCursorType)asv_sql varchar2(3000);v_count int;v_heiRownum int;v_lowRownum int; begin ----取记录总数   execute immediate p_SqlCount into v_count;   p_OutRecordCount := v_count;   --计算mypageCount--  if mod(v_count,p_PageSize)=0 then    p_pageCount:=v_count/p_PageSize;  else    p_pageCount:=v_count/p_PageSize+1;  end if;     ----执行分页查询   v_heiRownum := p_PageNo * p_PageSize;   v_lowRownum := v_heiRownum - p_PageSize + 1;   v_sql := 'SELECT B.* FROM (SELECT A.*, rownum rn FROM ('|| p_SqlSelect ||') A WHERE rownum <= '|| to_char(v_heiRownum) || ' ) B WHERE rn >= ' || to_char(v_lowRownum) ;  --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rnOPEN p_OutCursor FOR v_sql;end sp_Page;/* 何问起 hovertree.com */

推荐:http://www.cnblogs.com/roucheng/p/3504465.html