oracle存储过程实现分页功能（超详细）

1.实现分页的基础原理

	declare
 v_size number(2):=5;--每页显示5条
 v_page number(2):=1;--当前的页码
begin
    select * from ( 
    select t.*,rownum r from (
        select * from emp order by empno
    ) t where rownum<=(v_size*v_page)) where r>(v_page-1)*v_size;
end;

2.开始创建存储过程

CREATE OR REPLACE PROCEDURE PROP_QUERY(
    v_tableName in varchar2, --要查询的表名
    v_strwhere in varchar2, --查询条件
    v_orderColumn  in varchar2, --排序条件
    v_orderStyle in varchar2, --排序的方式
    v_currPage in out number, --当前页
    v_pageSize in out number, --每页显示的记录条数
    v_totalResult out number, --总记录数
    v_totalPages out number,--总页数
    v_curr out sys_refcursor, --结果游标
    v_index out number, --首页的页码
    v_last out number, --上一页的页码
    v_next out number, --下一页的页码
    v_end out number --最后一页的页码
) AS 
    v_sql varchar2(1000):='';
    v_startRecord number(6);
    v_endRecord number(6);
BEGIN
  --记录总记录条数
    v_sql:='select count(*) from '||v_tableName || ' where 1=1';
    if v_strwhere is not null then
        v_sql:=v_sql||' and ' || v_strwhere;
    end if;
    dbms_output.put_line('查询总记录数的sql：'||v_sql);
    --动态sql
    execute immediate v_sql into v_totalresult;
        dbms_output.put_line('总记录数：'||v_totalresult);

    --计算总页数
    --(1) 如果数据不小心传入的每页显示的记录数小于0，系统将默认显示每页五条数据
    if v_pagesize<=0 or v_pagesize is null then
        v_pageSize:=5;
    end if;
    --(2)如果页数是3.4页的话就是总页数就是4页
    if mod(v_totalResult,v_pageSize)>0 then
        v_totalPages:=floor(v_totalResult/v_pageSize)+1;
    elsif mod(v_totalResult,v_pageSize)=0 then
         v_totalPages:=(v_totalResult/v_pageSize);
    end if;
        dbms_output.put_line('总页数：'||v_totalpages);
        
    --实现分页查询
    --(1)如果当前页没有传递值，就赋值为1
    if v_currpage<=0 or v_currpage is null then
        v_currpage:=1;
    end if;
    --(2)开始记录数
    v_startrecord:=(v_currpage-1)*v_pagesize;
    --(3)结束记录数
    v_endrecord:=v_currpage*v_pagesize;
    --(4)判断当前分页中有没有where条件
    v_sql:='select * from '||v_tableName || ' where 1=1';
    if v_strwhere is not null then 
        v_sql:=v_sql|| ' and '||v_strwhere;
    end if;
    
    --(5)判断排序条件是否为空
    if v_ordercolumn is not null then 
        v_sql:=v_sql|| ' order by  '||v_ordercolumn;
        --(6)判断排序方式
        if v_orderstyle is not null then 
            v_sql:=v_sql|| ' '||v_orderstyle;
        else
            v_sql:=v_sql||' asc';
        end if;
    end if;
    --(7)开始编写第二层sql语句
    v_sql:='select t.*,rownum r from ('||v_sql||') t where rownum<='||v_endRecord;
    --(8)开始编写第三层sql语句
    v_sql:='select * from ('||v_sql||') where r>'||v_startRecord;
    
    DBMS_OUTPUT.PUT_LINE('最终的sql：'||v_sql);
    --5 计算首页和上一页的页码
    --当前页不能大于总页码
    if  v_currpage<=floor(v_totalPages) then  
    
    if v_currpage =1 then
        --设置首页和上一页为第一页
        v_index:=1;
        v_last:=1;
    else --如果当前页码不为1
        v_index:=1;
        v_last:=v_currpage-1;
    end if;
    --判断下一页和末页
    if v_currpage =v_totalPages then
        --设置首页和上一页为第一页
        v_end:=v_totalPages;
        v_next:=v_totalPages;
    else --如果当前页码不为1
        v_end:=v_totalPages;
        v_next:=v_currpage+1;
    end if;
    else
        RAISE_APPLICATION_ERROR(-20001, '当前页码不能大于总页码'||'当前页码为['||v_currpage||'] 总页码为['||v_totalPages||']');
    end if;
    --(9)打开游标接收
    open v_curr for v_sql;
    
    
END PROP_QUERY;

3.使用idea调用oracle数据库的PROP_QUERY过程

• 不知道怎么通过idea的oracle驱动jar调用oracle的看这个

https://blog.csdn.net/qq_48033003/article/details/115983883

• 代码如下

package com.hkd.test;

import oracle.jdbc.OracleTypes;

import java.sql.*;

public class TestOracle1 {
    public static void main(String[] args) {
        t();

    }

    /**
     * 调用返回游标的存储过程
     */
    public static void t(){
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url="jdbc:oracle:thin:@localhost:1521:orcl1";
            Connection conn= DriverManager.getConnection(url,"c##scott1","www123");
            CallableStatement cs=conn.prepareCall("{call PROP_QUERY(?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            //输入参数的设置
            cs.setString(1,"emp");
            cs.setString(2,null);
            cs.setString(3,null);
            cs.setString(4,null);
            cs.setInt(5,5);
            cs.setInt(6,4);
            //输出参数注册
            cs.registerOutParameter(7,Types.NUMERIC);
            cs.registerOutParameter(8, Types.NUMERIC);
            cs.registerOutParameter(9,OracleTypes.CURSOR);
            cs.registerOutParameter(10,Types.NUMERIC);
            cs.registerOutParameter(11,Types.NUMERIC);
            cs.registerOutParameter(12,Types.NUMERIC);
            cs.registerOutParameter(13,Types.NUMERIC);

            //执行语句
            cs.execute();
            System.out.println("总记录数"+cs.getInt(7));
            System.out.println("总页数"+cs.getInt(8));
            ResultSet rs=(ResultSet) cs.getObject(9);
            while (rs.next()){
                System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3));
            }
            System.out.print("首页页码为"+cs.getInt(10)+"\t");
            System.out.print("上一页页码为"+cs.getInt(11)+"\t");
            System.out.print("下一页页码为"+cs.getInt(12)+"\t");
            System.out.print("最后一页页码为"+cs.getInt(13)+"\t");
            //关闭资源
            cs.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}