Oracle使用存储过程分页大数据量

一、存储过程代码

create or replace procedure paging_cursor
(
    v_in_pagesize in number,
    v_in_pagenow in number,
    v_mobileWhere nvarchar2,--where条件
    v_statusWhere nvarchar2,--where条件
    v_out_result out pagingPackage.paging_cursor,
    v_out_rows out number,
    v_out_pagecount out number
) is
    --定义需要的变量
    v_sql varchar2(4000);
    v_sql_select varchar2(4000);
    v_start number;
    v_end number;
begin
    --计算v_start和v_end是多少
    --select t2.* from (select t1.*,rownum rn from (select * from msys_black_user ) t1 where rownum<=23 and user_mobile='13169792272' and status=0) t2 where rn>=1 order by create_time desc ;

    v_start:=v_in_pagesize*(v_in_pagenow-1)+1;
    v_end:=v_in_pagesize*v_in_pagenow;
   -- v_sql:='select t2.* from (select t1.*,rownum rn from (select * from msys_black_user ) t1 where rownum<='||v_end||') t2 where rn>='||v_start;
    v_sql:='select t2.* from (select t1.*,rownum rn from (select * from msys_blauser_mobile ck_user ) t1 where rownum<='||v_end;

    --这里的''''转义成'
    if v_mobileWhere is not null or v_mobileWhere<>'' then 
     v_sql:=v_sql||' and user_mobile='''||v_mobileWhere||'''';
    end if; 
    if v_statusWhere is not null or v_statusWhere<>'' then 
     v_sql:=v_sql||' and status='||v_statusWhere;
    end if; 

    v_sql:=v_sql||') t2 where rn>='||v_start|| ' order by create_time desc';

    DBMS_OUTPUT.put_line(v_sql);
    --打开游标，让游标指向结果集
    open v_out_result for v_sql;

    --查询共有多少条记录
    --v_sql_select:='select count(1) from ('||v_sql||')';
    v_sql_select:='select count(1) from msys_black_user where 1=1 ';

    if v_mobileWhere is not null or v_mobileWhere<>'' then 
     v_sql_select:=v_sql_select||' and user_mobile='''||v_mobileWhere||'''';
    end if; 
    if v_statusWhere is not null or v_statusWhere<>'' then 
     v_sql_select:=v_sql_select||' and status='||v_statusWhere;
    end if;
    execute immediate v_sql_select into v_out_rows;

    --统计多少页记录
    if mod(v_out_rows,v_in_pagesize)=0 then
        v_out_pagecount:=v_out_rows/v_in_pagesize;
    else
        v_out_pagecount:=v_out_rows/v_in_pagesize+1;
    end if;

    --关闭游标
    --close v_out_result;
end;

删除储存过程
--drop procedure paging_cursor

二、java代码调用

 @SuppressWarnings("unused")
    public static Map<String, Object> blackUserProcedurePaging(Map<String, Object> reqMap){
        // 定义需要的变量
        Connection connection = null;
        CallableStatement cs = null;
        Map<String,Object> resultMap = null; 
        ResultSet rs = null;
        try {
            String userMobile= reqMap.get("userMobile")!=null?String.valueOf(reqMap.get("userMobile")):"";
            String status= reqMap.get("status")!=null?String.valueOf(reqMap.get("status")):"";
            // 得到连接
            connection = DbCommon.getConnection();
            // 创建CallableStatement接口
            cs = connection.prepareCall("{call paging_cursor(?,?,?,?,?,?,?)}");
            // 给in?赋值
            cs.setInt(1, (int)reqMap.get("pageSize"));// 传入pagesize，每页显示多少条记录
            cs.setInt(2, (int)reqMap.get("currPage"));// 传入pagenow，显示第几页。
            cs.setString(3, userMobile);// 传入手机号码条件
            cs.setString(4, status);// 
            // 给out?注册
            cs.registerOutParameter(5, oracle.jdbc.OracleTypes.CURSOR);
            cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);
            cs.registerOutParameter(7, oracle.jdbc.OracleTypes.INTEGER);

            // 执行
            cs.execute();
            // 这里是关键所在，java没有接收结果集的get方法，所以只能用getObject来接收结果集，接收到后需要使用ResultSet强转才可以
            rs = (ResultSet) cs.getObject(5);
            ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等   
            int columnCount = md.getColumnCount(); //返回此 ResultSet 对象中的列数  
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); 
            // 循环取出
            while (rs.next()) {
                 Map<String,Object> rowData = new HashMap<String,Object>();
                 for (int i = 1; i <= columnCount; i++) {  
                     rowData.put(md.getColumnName(i), rs.getObject(i));  
                 }  
                 list.add(rowData);  
            }
            // 取出总记录数
            int rowCount = cs.getInt(6);
            // 取出总页数
            int pageCount = cs.getInt(7);
            resultMap = new HashMap<String,Object>();
            resultMap.put("total", rowCount);
            resultMap.put("rows", list);
            return resultMap;
        } catch (Exception e) {
            logger.error("通过存储过程分页查询黑名单异常",e);
        } finally {
            // 关闭资源
            try {
                if (cs != null) {
                    cs.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e) {
                logger.error("通过存储过程分页查询黑名单关闭资源异常",e);
            }
            cs = null;
            rs = null;
            connection = null;
        }
        return resultMap;
    }

三、结果如图

为了更快一点，给user_mobile增加索引：

create index msys_black_user_IDX on msys_black_user (USER_MOBILE);