Oracle分页存储过程-CSDN博客

本文链接：https://blog.csdn.net/zhouzhenxing_0421/article/details/83319591

/*
名称:spAll_ReturnRows
输入:
输出:
调用:
  EXEC spAll_ReturnRows 'SELECT  * FROM 表名', 页号, 返回记录数, '主键', '排序字段'
  spAll_ReturnRows 'SELECT  * FROM all_Categories',2,10,'[ID]','[ID]'
说明:[百万级]通用存储过程.分页存储过程..返回指定返回条数、指定页数的记录

*/

CREATE PROCEDURE dbo.spAll_ReturnRows
(
@SQL nVARCHAR(4000),
@Page int,
@RecsPerPage int,
@ID VARCHAR(255),
@Sort VARCHAR(255)
)
AS

DECLARE @Str nVARCHAR(4000)

SET @Str='SELECT   TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+' NOT IN 
(SELECT   TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort

PRINT @Str

EXEC sp_ExecuteSql @Str
GO

/*

名称:spAll_DeleteNoneUnique
输入:要查询的表名和字段列表
输出:
调用:
说明:实现千万级数据的分页显示!--可以在5秒内获取1448万条记录里的第1200页的100条记录，雄不？
*/

CREATE PROCEDURE GetRecordFromPage
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @IsCount      bit = 0,            -- 返回记录总数, 非 0 值则返回
    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(1000) = ''  -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(100)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型

if @OrderType != 0
begin
    set @strTmp = "<(select min"
    set @strOrder = " order by [" + @fldName +"] desc"
end
else
begin
    set @strTmp = ">(select max"
    set @strOrder = " order by [" + @fldName +"] asc"
end

set @strSQL = "select top " + str(@PageSize) + " * from ["
    + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
    + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
    + @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"
    + @strOrder

if @strWhere != ''
    set @strSQL = "select top " + str(@PageSize) + " * from ["
        + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
        + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
        + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
        + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder

if @PageIndex = 1
begin
    set @strTmp = ""
    if @strWhere != ''
        set @strTmp = " where " + @strWhere

    set @strSQL = "select top " + str(@PageSize) + " * from ["
        + @tblName + "]" + @strTmp + " " + @strOrder
end

if @IsCount != 0
    set @strSQL = "select count(*) as Total from [" + @tblName + "]"

exec (@strSQL)

GO

几乎每一个WEB应用都会用到分页，因此，将其做得通用高效就变得非常重要了，根据自己的想法用存储过程做了一个分页的存储过程，与大家分享，希望能够通过讨论得到更好的解决方案。

　　之所以用存储过程，是因为以后需要修改的话不需要修改程序代码，只需要修改存储过程的代码。但这个例子是在存储过程里动态生成的SQL语句，不知道会不会因此失去存储过程一次编译和快速的特点。代码如下：

--1、首先建立一个包，用户创建一个游标类型
create or replace package pkg_query as
  type cur_query is ref cursor;
end pkg_query;

--2、创建存储过程

CREATE OR REPLACE PROCEDURE prc_query
       (p_tableName        in  varchar2,   --表名
        p_strWhere         in  varchar2,   --查询条件
        p_orderColumn      in  varchar2,   --排序的列
        p_orderStyle       in  varchar2,   --排序方式
        p_curPage          in out Number,  --当前页
        p_pageSize         in out Number,  --每页显示记录条数
        p_totalRecords     out Number,     --总记录数
        p_totalPages       out Number,     --总页数
        v_cur              out pkg_query.cur_query)   --返回的结果集
IS
   v_sql VARCHAR2(1000) := '';      --sql语句
   v_startRecord Number(4);         --开始显示的记录条数
   v_endRecord Number(4);           --结束显示的记录条数
BEGIN
   --记录中总记录条数
   v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || p_tableName || ' WHERE 1=1';
   IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
       v_sql := v_sql || p_strWhere;
   END IF;
   EXECUTE IMMEDIATE v_sql INTO p_totalRecords;
   
   --验证页面记录大小
   IF p_pageSize < 0 THEN
       p_pageSize := 0;
   END IF;
   
   --根据页大小计算总页数[Page]
   IF MOD(p_totalRecords,p_pageSize) = 0 THEN
       p_totalPages := p_totalRecords / p_pageSize;
   ELSE
       p_totalPages := p_totalRecords / p_pageSize + 1;
   END IF;
   
   --验证页号
   IF p_curPage < 1 THEN
       p_curPage := 1;
   END IF;
   IF p_curPage > p_totalPages THEN
       p_curPage := p_totalPages;
   END IF;
   
   --实现分页查询
   v_startRecord := (p_curPage - 1) * p_pageSize + 1;
   v_endRecord := p_curPage * p_pageSize;
   v_sql := 'SELECT * FROM (SELECT A.*, rownum r FROM ' ||
            '(SELECT * FROM ' || p_tableName;
   IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
       v_sql := v_sql || ' WHERE 1=1' || p_strWhere;
   END IF;
   IF p_orderColumn IS NOT NULL or p_orderColumn <> '' THEN
       v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle;
   END IF;
   v_sql := v_sql || ') A WHERE rownum <= ' || v_endRecord || ') B WHERE r >= '
            || v_startRecord;
   DBMS_OUTPUT.put_line(v_sql);
   OPEN v_cur FOR v_sql;
END prc_query;

3、JAVA代码里取出结果集

String sql= "{ call prc_query(?,?,?,?,?,?,?,?,?) }";

　　CallableStatement call = con.prepareCall(sql);

　　// ……中间数据设置及注册省略

　　call.registerOutParameter(9, OracleTypes.CURSOR);

　　// 取出结果集

　　(ResultSet) call.getObject(9);