oracle 存储过程分页示例

最近在学校ssm和存储过程，先做个小项目，所有业务逻辑都在存储过程中实现并且不把数据库表名，字段名暴露给服务器。最重要最麻烦的存储过程动态分页查询。经过疯狂百度，看了很多网友写的实例，自己终于实现了用存储过程完成分页功能，这次特地整理出来以便希望能帮助到大家。

首先创建个简单的表tbl_hos_post:

drop table tbl_hos_post;        
create table tbl_hos_post(
    post_id int primary key,
    post_name varchar2(50) not null,
    post_salary number(16,2) not null
);
drop sequence seq_hos_post_id;
create sequence seq_hos_post_id     --创建序列
    increment by 1
    start with 1
    nocycle;
drop trigger hos_post_trigger;
create trigger hos_post_trigger BEFORE      --创建触发器
insert on tbl_hos_post for each ROW
begin 
    select seq_hos_post_id.nextval into:New.post_id from dual;
end;
/

新增测试数据：

insert into tbl_hos_post(post_name,post_salary)  values('护士',5000);
insert into tbl_hos_post(post_name,post_salary) values('医生',10000);
insert into tbl_hos_post(post_name,post_salary) values('护士长',7000);
insert into tbl_hos_post(post_name,post_salary) values('副主任医师',30000);
insert into tbl_hos_post(post_name,post_salary) values('科室主任',40000);
insert into tbl_hos_post(post_name,post_salary) values('医师顾问',25000);
insert into tbl_hos_post(post_name,post_salary) values('院长',80000);
insert into tbl_hos_post(post_name,post_salary) values('副院长',70000);
insert into tbl_hos_post(post_name,post_salary) values('仓库管理员',4000);
insert into tbl_hos_post(post_name,post_salary) values('采购专员',5000);

存储过程动态分页查询：

create or replace procedure sp_hos_query_Post(--          输入样例
       o_result out sys_refcursor,    --out 返回的结果集  null
       o_page_count out int,          --out 数据总条数    null
       i_page_size int,               --in 分页大小       10
       i_page_index int,              --in 当前页数       1
       i_order varchar2,              --in 根据xx值排序   null
       i_order_sort int,              --in 1 表示asc 升序  2表示desc降序  null
       i_query_id int,                --in 查找id         2   
       i_query_name varchar2,         --in 查找name       %医生%
       i_query_salary_start number,   --in 查找工资最低   2000
       i_query_salary_end number      --in 查找工资最高   8000      

) is
strPageSql varchar2(1000);                 
strResultSql varchar2(1000);
strWhere varchar2(1000);
strOrderBy varchar2(1000);
o_page_start int;
o_page_end int;
--o_page_count int;
--o_result sys_refcursor;

begin
  --设置返回的数据范围  o_page_start 到 o_page_end
  o_page_start := (i_page_index - 1) * i_page_size + 1; 
  o_page_end := o_page_start + i_page_size - 1;
  strWhere := ' ';
  strOrderBy := ' ';

  -- 设置排序条件与升降序
  if i_order is not null or i_order!= 0 then
    begin
           strOrderBy := strOrderBy || 'order by post_' || i_order;
         if  i_order_sort = 2 then
             strOrderBy := strOrderBy || ' desc';
         else
             strOrderBy := strOrderBy || ' asc';   
         end if;
    end;
  end if;

  --设置查询条件
  if i_query_id is not null or i_query_id!=0 then
    strWhere := strWhere || ' and post_id = '|| i_query_id;
  end if;
  if i_query_name is not null then
    strWhere := strWhere || ' and post_name like ''' || i_query_name||'''';
  end if;
  if i_query_salary_start is not null or i_query_salary_start!=0 then
    strWhere := strWhere || ' and post_salary >= '|| i_query_salary_start;
  end if;
  if i_query_salary_end is not null or i_query_salary_end!=0 then
    strWhere := strWhere || ' and post_salary <= '|| i_query_salary_end;
  end if;

  --获取查询结果总记录数
 strPageSql :='select count(*) from tbl_hos_post where 1=1 '|| strWhere || strOrderBy;
 dbms_output.put_line(strPageSql);
 execute immediate strPageSql into o_page_count;

 --拼凑查询语句
 strResultSql := 'select'
                 || ' post_id as no,'
                 || ' post_name as name,'
                 || ' post_salary as salary'
                 || ' from (select rownum rn,a.* from tbl_hos_post a'
                 || '     where 1=1'
                 || strWhere || strOrderBy
                 || '     )'
                 || ' where 1=1'
                 || ' and rn >= ' || o_page_start
                 || ' and rn <= ' || o_page_end;
 dbms_output.put_line(strResultSql);
 --将查询结果赋予o_result
 open o_result for strResultSql;
end sp_hos_query_Post;

使用mybatis调用存储过程
Post.java：

package cn.nveanve.hosmanager.entity;

/*
 * 职位 类  基类
 */
public class Post {
    private Integer no;
    private String name;
    private double salary;
    public Integer getNo() {
        return no;
    }
    public void setNo(Integer no) {
        this.no = no;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public double getSalary() {
        return salary;
    }
    public void setSalary(double salary) {
        this.salary = salary;
    }
    public Post(Integer no, String name, double salary) {
        super();
        this.no = no;
        this.name = name;
        this.salary = salary;
    }
    public Post() {
        super();
    }

}

postMapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.nveanve.hosmanager.mapper.PostMapper">
    <resultMap type="Post" id="postMap"></resultMap>

    <select id="queryPost" statementType="CALLABLE" parameterType="java.util.HashMap">
        call sp_hos_query_post(
            #{result,mode=OUT,jdbcType=CURSOR,resultMap=postMap}, <!-- 返回的结果集 -->
            #{count,mode=OUT,jdbcType=INTEGER},                   <!-- 数据总条数 -->
            #{size,mode=IN,jdbcType=INTEGER},                     <!-- 分页大小  -->
            #{index,mode=IN,jdbcType=INTEGER},                    <!-- 当前页数 -->
            #{order,mode=IN,jdbcType=VARCHAR},                    <!-- 根据xx值排序  表部分字段名 例如 id，name，salary-->
            #{orderSort,mode=IN,jdbcType=INTEGER},                <!-- 1 表示asc 升序  2表示desc降序 -->
            #{queryId,mode=IN,jdbcType=INTEGER},                  <!-- 查找id  -->
            #{queryName,mode=IN,jdbcType=VARCHAR},                <!-- 查找name  例如：  %医生% -->
            #{querySalaryStart,mode=IN,jdbcType=DOUBLE},          <!-- 查找工资最低 -->
            #{querySalaryEnd,mode=IN,jdbcType=DOUBLE}             <!-- 查找工资最高 -->
        )
    </select>

</mapper>

postMapper.java：

package cn.nveanve.hosmanager.mapper;

import java.util.HashMap;

import cn.nveanve.hosmanager.entity.Post;

public interface PostMapper {
    public void queryPost(HashMap<String, Object> data);
}

PostService.java：

package cn.nveanve.hosmanager.service;

import java.util.HashMap;

import cn.nveanve.hosmanager.dto.PostQuery;
import cn.nveanve.hosmanager.entity.Post;

public interface PostService {
    public HashMap<String, Object> queryPost(PostQuery 

PostServiceImp：

package cn.nveanve.hosmanager.service;

import java.util.HashMap;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.nveanve.hosmanager.dto.PostQuery;
import cn.nveanve.hosmanager.entity.Post;
import cn.nveanve.hosmanager.mapper.PostMapper;

@Service
@Transactional
public class PostServiceImp implements PostService {

    @Resource
    PostMapper postMapper;  
    @Override
    public HashMap<String, Object> queryPost(PostQuery query) {
        HashMap<String, Object> data = new HashMap<String,Object>();
        data.put("size", query.getSize());
        data.put("index", query.getIndex());
        data.put("order", PostQuery.orders[query.getOrder()]);
        data.put("orderSort", query.getSort());
        data.put("querySalaryStart", query.getSalaryStart());
        data.put("querySalaryEnd", query.getSalaryEnd());
        data.put("queryId", query.getId());
        data.put("queryName", query.getName());
        postMapper.queryPost(data);
        return data;
    }
}

控制器代码PostController.java：

package cn.nveanve.hosmanager.web;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import cn.nveanve.hosmanager.dto.PostQuery;
import cn.nveanve.hosmanager.entity.Post;
import cn.nveanve.hosmanager.service.PostService;

@Controller
public class PostController {
    @Autowired
    private PostService postService;

    @RequestMapping(value="/queryPost.do")
    public ModelAndView queryPost(PostQuery query){
        //System.out.println("queryPost");
        ModelAndView andView =new ModelAndView("Post/post_list");
        HashMap<String, Object> pageData = postService.queryPost(query);
        List<Post> posts = (List<Post>) pageData.get("result");//当前分页中的结果集
        int count = (int) pageData.get("count");//查询结果总数
        andView.addObject("posts",posts);
        andView.addObject("query", query);
        andView.addObject("pageCount",count);
        return andView;
    }
}

PostQuery.java:

package cn.nveanve.hosmanager.dto;

public class PostQuery {
    public final static String[] orders = {null,"id","name","salary"};
    private Integer id;
    private String name;
    private Integer salaryStart ;
    private Integer salaryEnd;
    private Integer order=0;
    private Integer sort;
    private Integer index =1;
    private Integer size =10;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getSalaryStart() {
        return salaryStart;
    }
    public void setSalaryStart(Integer salaryStart) {
        this.salaryStart = salaryStart;
    }
    public Integer getSalaryEnd() {
        return salaryEnd;
    }
    public void setSalaryEnd(Integer salaryEnd) {
        this.salaryEnd = salaryEnd;
    }
    public Integer getOrder() {
        return order;
    }
    public void setOrder(Integer order) {
        this.order = order;
    }
    public Integer getSort() {
        return sort;
    }
    public void setSort(Integer sort) {
        this.sort = sort;
    }
    public Integer getIndex() {
        return index;
    }
    public void setIndex(Integer index) {
        this.index = index;
    }
    public Integer getSize() {
        return size;
    }
    public void setSize(Integer size) {
        this.size = size;
    }
    public PostQuery(Integer id, String name, Integer salaryStart,
            Integer salaryEnd, Integer order, Integer sort, Integer index,
            Integer size) {
        super();
        this.id = id;
        this.name = name;
        this.salaryStart = salaryStart;
        this.salaryEnd = salaryEnd;
        this.order = order;
        this.sort = sort;
        this.index = index;
        this.size = size;
    }
    public PostQuery() {
        super();
    }


}

post_list.jsp:

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ include file="../common/taglibs.jsp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet"  href="./jsp/libs/bootstrap-3.3.7-dist/css/bootstrap.min.css">
<script type="text/javascript" src="./jsp/libs/jquery/jquery-3.2.1.js"></script>
<title>Insert title here</title>
</head>
<body>
    <div class="continer-fluid">
        <div class="row">

            <div class="col-sm-10 col-sm-offset-1 ">
                <form id="form1" action="queryPost.do" class="form">
                    <div class="form-group col-sm-3">
                        <label class="col-sm-6" for="">职位id：</label>
                        <div class="col-sm-6">
                            <input style="width:60px;" name="id"  type="number" class="form-control input-sm"/>
                        </div>
                        <input id="order" name="order" type="hidden" value="${query.order}">
                        <input id="sort" name="sort" type="hidden" value="${query.sort}">
                    </div>
                    <div class="form-group col-sm-3">
                        <label for="" class="col-sm-6">职位名称：</label>
                        <div class="col-sm-6">
                            <input style="width:100px;" name="name" type="text" class="form-control input-sm" value="${query.name }"/>
                        </div>
                    </div>
                    <div class="form-group col-sm-5" >
                        <label class="col-sm-3"  for="">职位薪水:</label>
                        <div class="col-sm-3">
                            <input style="width:80px;" name="salaryStart" type="number" class="form-control input-sm" value="${query.salaryStart }"/>
                        </div>
                        <span class="col-sm-1">~</span>
                        <div class="col-sm-3">
                            <input style="width:80px;" name="salaryEnd" type="number" class="form-control input-sm" value="${query.salaryEnd}"/>
                        </div>
                        <div class="col-sm-2">
                            <button class="btn btn-primary" type="submit">查询</button>
                        </div>
                    </div>
                    <div class="form-group">
                        <table class="table table-striped table-bordered table-hover table-condensed">
                            <header>
                                <th id="th-id">
                                    编号
                                    <c:if test="${query.order==1}">
                                        <c:if test="${query.sort==1}">
                                            <span class="glyphicon glyphicon-chevron-up"></span>
                                        </c:if>
                                        <c:if test="${query.sort==2}">
                                            <span class="glyphicon glyphicon-chevron-down"></span>
                                        </c:if>
                                    </c:if>
                                </th>
                                <th id="th-name">
                                    职位名称
                                    <c:if test="${query.order==2}">
                                        <c:if test="${query.sort==1}">
                                            <span class="glyphicon glyphicon-chevron-up"></span>
                                        </c:if>
                                        <c:if test="${query.sort==2}">
                                            <span class="glyphicon glyphicon-chevron-down"></span>
                                        </c:if>
                                    </c:if>
                                </th>
                                <th id="th-salary">
                                    职位薪水
                                    <c:if test="${query.order==3}">
                                        <c:if test="${query.sort==1}">
                                            <span class="glyphicon glyphicon-chevron-up"></span>
                                        </c:if>
                                        <c:if test="${query.sort==2}">
                                            <span class="glyphicon glyphicon-chevron-down"></span>
                                        </c:if>
                                    </c:if>
                                </th>
                                <th>职位管理</th>
                            </header>
                            <c:if test="${empty posts}">
                                <tr>
                                    <td colspan="4">
                                        <center style="color:red">搜索结果不存在</center>
                                    </td>
                                </tr>
                            </c:if>
                            <c:forEach var="post" items="${posts }" >
                                <tr>
                                    <td>${post.no}</td>
                                    <td>${post.name}</td>
                                    <td>${post.salary}</td>
                                    <td>
                                        <input class="btn btn-danger btn-sm" type="button" value="删除">
                                        <input class="btn btn-warning btn-sm" type="button" value="修改">
                                    </td>
                                </tr>
                            </c:forEach>
                        </table>
                    </div>
                    <c:if test="${not empty posts}">
                        <div class="form-group col-sm-offset-9">
                            <a onclick="formRefresh()">设置分页大小</a>
                            <input min="2" type="number" name="size" id="size" style="width:80px;" value="${query.size }" />
                        </div>
                        <div class="form-group col-sm-offset-9">
                            <a onclick="indexSub()" >上一页</a>
                             <input  type="number" name="index" id="index" style="width:40px;" value="${query.index}"/>
                              /
                              <span id="count"><fmt:formatNumber type="number" value="${(pageCount-1)/query.size+0.5}" maxFractionDigits="0"/> </span>
                              <input id="oldIndex" type="hidden" value="${query.index}">
                            <a onclick="formRefresh()">跳转</a>
                            <a onclick="indexAdd()" >下一页</a>
                        </div>
                    </c:if>
                </form>
            </div>

            <div class="col-sm-10 col-sm-offset-1 ">

            </div>

        </div>
    </div>
</body>
<script type="text/javascript">
    window.onload = function(){

    }

    /*
        更新表单
    */
    function formRefresh(){
        //检查size是否规范
        var size = $("#size").val();
        if(size<2 || size>50){
            alert("分页大小不规范，应该在2到50之间！");
            return;
        }
        //检查index是否规范
        var index = $("#index").val();
        var count = $("#count").html();
        //console.log(count);
        if(index<1 || index>count){
            alert("页数不规范，第"+index+"页不存在！");
            $("#index").val($("#oldIndex").val());
            return ;
        }
        $("#form1").submit();
    }

    function indexAdd(){
        var index = $("#index").val();
        $("#index").val(++index);
        formRefresh();
    }

    function indexSub(){
        var index = $("#index").val();
        $("#index").val(--index);
        formRefresh();
    }

    $("#th-id").dblclick(function(){
        if($("#order").val()==1)
            $("#sort").val(1);
        else
            $("#sort").val(2);
        $("#order").val(1);
        formRefresh();
    });

    $("#th-name").dblclick(function(){
        if($("#order").val()==2)
            $("#sort").val(1);
        else
            $("#sort").val(2);
        $("#order").val(2);
        formRefresh();
    });

    $("#th-salary").dblclick(function(){
        if($("#order").val()==3)
            $("#sort").val(1);
        else
            $("#sort").val(2);
        $("#order").val(3);
        formRefresh();
    });
</script>
</html>

实现了post的查询，排序，分页。
附上ssm的jar 和服务器项目源码