学习用存储过程实现分页

Sql代码
/*  
名称: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_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条记录，雄不？
*/

Sql代码
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 

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语句，不知道会不会因此失去存储过程一次编译和快速的特点。代码如下：



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; 

--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代码里取出结果集

　　

Java代码
String sql= "{ call prc_query(?,?,?,?,?,?,?,?,?) }";  
 
　　CallableStatement call = con.prepareCall(sql);  
 
　　// ……中间数据设置及注册省略  
 
　　call.registerOutParameter(9, OracleTypes.CURSOR);  
 
　　// 取出结果集  
 
　　(ResultSet) call.getObject(9);