oracle 存储过程分页示例
来源：互联网 发布：汉诺塔算法内部流程 编辑：程序博客网 时间：2024/05/22 08:07
最近在学校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 ROWbegin 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 ) isstrPageSql 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@Transactionalpublic 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;@Controllerpublic 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 和服务器项目源码
阅读全文
0 0
- Oracle存储过程分页示例
- oracle 存储过程分页示例
- 分页存储过程示例
- 整理的oracle数据库分页存储过程及示例
- oracle存储过程分页
- oracle存储过程分页
- Oracle分页存储过程
- Oracle 存储过程分页
- ＯＲＡＣＬＥ分页存储过程
- oracle 分页存储过程
- Oracle分页存储过程
- Oracle分页存储过程
- Oracle存储过程分页
- oracle 分页存储过程
- Oracle存储过程分页
- Oracle分页存储过程
- oracle 分页 存储过程
- Oracle存储过程分页
- HTML字符转义(转载)
- Android屏幕常亮功能其实很简单
- Java 之工厂方法和抽象工厂模式(转)
- 轻松学习 JavaScript——第 4 部分：函数中的 arguments 对象
- 电商冲击下，时下最火的小程序能为实体店铺带来哪些转机
- oracle 存储过程分页示例
- 为什么硬盘总坏？为什么就冠希兄的修好了？
- 机器学习小试（9）使用TensorFlow跑通一个通用增量学习流程-测试与应用
- redis_三种持久化方式
- iOS之基于FreeStreamer的简单音乐播放器
- 合并分支的拟人化
- 利用SwipeRefreshLayout实现类似知乎客户端的一打开界面就自动刷新的效果
- 信号中断 与 慢系统调用
- Spring-boot 配置Aop获取controller里的request中的参数以及其返回值
