分页的存储过程
(1)简单的分页存储过程
-----------------------分页存储过程----------------------------------------------- --①申明包--- --②编写存储过程--
-- 申明了一个包 和 java中的包是一样的概念 -- create or replace package pack_page is -- 在包中自定义一个类型 -- type page_cursor is ref cursor ;--定义游标 end pack_page;
-- 分页存储过程 -- create or replace procedure proc_owner_page ( page number,-- 当前页码 pageSize number,-- 每页大小 total out number, -- 总条数 datas out pack_page.page_cursor -- 查询的数据,输出的是一个游标 ) is v_start number; v_sql varchar(200 char); begin -- 查询总条数 -- select count(*) into total from t_owners; -- 查询数据 -- v_start := (page-1)*pageSize; v_sql := 'select * from (select o.*,rownum r from t_owners o) d where d.r >='||v_start||' and d.r < '||(v_start+pageSize); open datas for v_sql ; end;
|
Junit测试
@Test public void testQueryBypage() throws Exception { Connection con=getCon(); String sql = "{call proc_owner_page(?,?,?,?)}"; CallableStatement cst=con.prepareCall(sql); //设置输入参数 cst.setInt(1, 2); cst.setInt(2, 5); //输出参数要注册 cst.registerOutParameter(3, OracleTypes.NUMBER); cst.registerOutParameter(4, OracleTypes.CURSOR); //执行存储过程 cst.execute(); //取出输出参数的值 int total = cst.getInt(3); System.out.println("总共查询到:"+total+"条数据"); ResultSet rs = (ResultSet) cst.getObject(4); while(rs.next()) { System.out.println("id:"+rs.getInt("id")+";name:"+rs.getString("name")); } cst.close(); con.close();
} |
结果:
(2)实际开发中完整的分页存储过程
-- 实际开发中完整的分页存储过程 -- create or replace procedure proc_page ( tname varchar2 ,-- 要分页查询的表名称-- page number,--当前的页码-- pageSize number,-- 每页大小-- term varchar2,-- 查询条件 -- total out number,-- 查询的数据的总条数-- maxPageNum out number,-- 最大页码 -- datas out pack_page.page_cursor -- 查询的数据,输出的是一个游标 -- ) is -- 申明部分 -- v_sql varchar2(500 char); v_start number; begin -- 查询总条数 -- v_sql := 'select count(*) from '||tname; -- 判断是否存在查询条件 -- if term is not null then v_sql := v_sql || ' where '||term; end if; -- 执行查询总条数的sql -- EXECUTE IMMEDIATE v_sql into total; -- 计算最大页码 -- if mod(total,pageSize) <> 0 then maxPageNum := total/pageSize+1; else maxPageNum := total/pageSize; end if; -- 开始查询数据 -- -- 计算开始位置 --
v_start := (page-1)*pageSize;
v_sql := 'select * from(select d.*,rownum r from '||tname||' d'; -- 判断是否有查询条件 -- if term is not null then v_sql := v_sql || ' where '||term; end if; -- 拼接上最后一部分 -- v_sql := v_sql ||') t where t.r >'||v_start||' and t.r <='||(v_start+pageSize);
dbms_output.put_line(v_sql); -- 执行sql语句 -- open datas for v_sql; end; |
Junit测试
@Test public void testQueryBypage2() throws Exception { Connection con=getCon(); String sql = "{call proc_page(?,?,?,?,?,?,?)}"; CallableStatement cst=con.prepareCall(sql); //设置输入参数 cst.setString(1, "t_owners"); cst.setInt(2, 2); cst.setInt(3, 5); cst.setString(4, " ownertypeid=1 "); //输出参数要注册 cst.registerOutParameter(5, OracleTypes.NUMBER); cst.registerOutParameter(6, OracleTypes.NUMBER); cst.registerOutParameter(7, OracleTypes.CURSOR); //执行存储过程 cst.execute(); //取出输出参数的值 int total = cst.getInt(5); int maxPageNum = cst.getInt(6); System.out.println("总共查询到:"+total+"条数据"); System.out.println("最大页码是:"+maxPageNum); //取出游标中的数据 ResultSet rs = (ResultSet) cst.getObject(7); while(rs.next()) { System.out.println("id:"+rs.getInt("id")+";name:"+rs.getString("name")); } cst.close(); con.close();
} |
结果