转自:http://www.cnblogs.com/hnsdwhl/archive/2011/09/04/2166522.html
这个我试过,好使,贴出来,分享一下,
首先是分页存储过程
--创建包定义
create or replace package pack_page is
type t_cursor is ref cursor;
procedure GetDataByPage(
p_tableName varchar2,
p_fields varchar2,
p_filter varchar2,
p_sort varchar2,
p_currentPage number,
p_pageSize number,
p_cursor out t_cursor,
p_totalRecords out number
);
end pack_page;
--创建包体
create or replace package body pack_page is
procedure GetDataByPage(
p_tableName varchar2, --要查询的表名
p_fields varchar2, --要查询的字段
p_filter varchar2, --过滤条件
p_sort varchar2, --排序字段以及方向
p_currentPage number, --当前页
p_pageSize number, --每页长度
p_cursor out t_cursor,--分页结果集
p_totalRecords out number --总记录数
)
is
v_sql varchar2(1000):=''; --执行的sql语句
v_startRecord number(4); --每页起始位置
v_endRecord number(4); --每页结束位置
begin
v_sql:=' select to_number(count(*)) from '||p_tableName;
--如果有过滤条件
if p_filter is not null then
v_sql:=v_sql||' where 1=1 and '|| p_filter;
end if;
execute immediate v_sql into p_totalRecords; --设置总记录数
v_startRecord:=(p_currentPage-1)*p_pageSize; --设置当前页起始位置
v_endRecord:=p_currentPage*p_pageSize; --设置当前页结束位置
v_sql:=' select ' ||p_fields||' from(select '||p_fields||' ,rownum r from '||
' (select '||p_fields||' from '||p_tableName;
--如果有过滤条件
if p_filter is not null then
v_sql:=v_sql||' where 1=1 and '|| p_filter;
end if;
--如果有排序条件
if p_sort is not null then
v_sql:=v_sql||' order by '||p_sort;
end if;
v_sql:=v_sql||' ) A where rownum<= ' ||to_char(v_endRecord)||' )B where
r> '|| to_char(v_startRecord);
--打开游标
open p_cursor for v_sql;
end GetDataByPage;
end pack_page;
--测试查询
declare
v_cur pack_page.t_cursor;
one_emp emp%rowtype;
v_totalRecords number;
begin
pack_page.GetDataByPage('emp','empno,ename,job,mgr,hiredate,sal,comm,deptno','sal>1100','sal desc',1,10,v_cur,v_totalRecords);
--fetch v_cur into one_emp;
while v_cur%found
loop
dbms_output.put_line(one_emp.empno||','||one_emp.ename);
fetch v_cur into one_emp;
end loop;
dbms_output.put_line('总记录数为'||v_totalRecords);
end;
通过JDBC调用测试,
package com.accp.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
public class Test {
public static void main(String[] args) {
Test test = new Test();
List<Employee> list = test.getData();
for (Employee employee : list) {
System.out.println(employee);
}
}
private List<Employee> getData() {
Connection con = DBConnect.getConnect();
CallableStatement cs = null;
ResultSet rs = null;
String sql = "{call pack_page.GetDataByPage(?,?,?,?,?,?,?,?)}";
List<Employee> list = new ArrayList<Employee>();
try {
cs = con.prepareCall(sql);
cs.setString(1, "emp");
cs.setString(2, "empno,ename,job,mgr,hiredate,sal,comm,deptno");
cs.setString(3, "sal>1100");
cs.setString(4, "sal desc");
cs.setInt(5, 1);
cs.setInt(6, 10);
cs.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);
cs.registerOutParameter(8, Types.INTEGER);
cs.executeQuery();
int totalCount = cs.getInt(8);
System.out.println("共有" + totalCount + "条");
rs = (ResultSet) cs.getObject(7);
while(rs.next()){
Employee emp = new Employee();
emp.setEmpno(rs.getInt(1));
emp.setEname(rs.getString(2));
emp.setJob(rs.getString(3));
emp.setMgr(rs.getInt(4));
Date date = rs.getDate(5);
Date hiredate = new Date(date.getTime());
emp.setHiredate(hiredate);
emp.setSal(rs.getInt(6));
emp.setComm(rs.getInt(7));
emp.setDeptno(rs.getInt(8));
list.add(emp);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
DBConnect.closeAll(con, cs, rs);
}
return list;
}
}
测试结果:
共有11条
Employee [comm=0, deptno=10, empno=7839, ename=KING, hiredate=1981-11-17, job=PRESIDENT, mgr=0, sal=5000]
Employee [comm=0, deptno=20, empno=7788, ename=SCOTT, hiredate=1987-04-19, job=ANALYST, mgr=7566, sal=3000]
Employee [comm=0, deptno=20, empno=7902, ename=FORD, hiredate=1981-12-03, job=ANALYST, mgr=7566, sal=3000]
Employee [comm=0, deptno=20, empno=7566, ename=JONES, hiredate=1981-04-02, job=MANAGER, mgr=7839, sal=2975]
Employee [comm=0, deptno=30, empno=7698, ename=BLAKE, hiredate=1981-05-01, job=MANAGER, mgr=7839, sal=2850]
Employee [comm=0, deptno=10, empno=7782, ename=CLARK, hiredate=1981-06-09, job=MANAGER, mgr=7839, sal=2450]
Employee [comm=300, deptno=30, empno=7499, ename=ALLEN, hiredate=1981-02-20, job=SALESMAN, mgr=7698, sal=1600]
Employee [comm=0, deptno=30, empno=7844, ename=TURNER, hiredate=1981-09-08, job=SALESMAN, mgr=7698, sal=1500]
Employee [comm=0, deptno=10, empno=7934, ename=MILLER, hiredate=1982-01-23, job=CLERK, mgr=7782, sal=1300]
Employee [comm=500, deptno=30, empno=7521, ename=WARD, hiredate=1981-02-22, job=SALESMAN, mgr=7698, sal=1250]