Board logo

Subject: 使用存储过程实现ADO分页 [Print This Page]

Author: kendling    Time: 2007-11-22 23:24     Subject: 使用存储过程实现ADO分页

就一个存储过程代码,别的不说了,大家说说吧。

[Copy to clipboard]
CODE:
CREATE  PROCEDURE [dbo].[sp_TablePaging]
(
@ATable VARCHAR(100),
@AFilter VARCHAR(1000),
@APage INT
)
AS
SET NOCOUNT ON
DECLARE @p1 INT, @PageSize INT, @sql VARCHAR(4000), @PageCount INT, @RowCount INT, @RecordCount INT
SET @PageSize = 100
SET @sql = 'SELECT * FROM ' + @ATable
IF @AFilter <> ''
  SET @sql = @sql + ' WHERE ' + @AFilter
EXEC sp_CursorOpen @p1 OUTPUT, @sql, @ScrollOpt = 1, @CcOpt = 1, @RowCount = @PageCount OUTPUT
-- 限制页数不超出范围
SET @PageCount = Ceiling(1.0 * @PageCount / @PageSize)
IF @APage > @PageCount
  SET @APage = @PageCount
IF @APage < 1
  SET @APage = 1
SET @APage = (@APage - 1) * @PageSize + 1
-- 将页总数、记录总数添加到输出
SET @sql = 'SELECT *, ' + CONVERT(VARCHAR, @PageCount) + ', (SELECT COUNT(*) FROM ' + @ATable + ') FROM ' + @ATable
IF @AFilter <> ''
  SET @sql = @sql + ' WHERE ' + @AFilter
EXEC sp_CursorOpen @p1 OUTPUT, @sql, @ScrollOpt=1, @CcOpt=1, @RowCount = @PageCount OUTPUT
EXEC sp_CursorFetch @p1, 16, @APage, @PageSize
EXEC sp_CursorClose @p1
GO


Author: Passion    Time: 2007-11-22 23:26

调用方法也写出来?
Author: 小夏    Time: 2007-11-22 23:37

沙发被抢,
Author: kendling    Time: 2007-11-22 23:58

代码比较简单,其实看一下代码就知道:
SQL.Text := 'EXEC sp_TablePaging ''TableA'', ''FieldA = ''''ValueA'''' '',2 ';
// SQL语句是EXEC sp_TablePaging 'TableA', 'FieldA = ''ValueA'' ', 2
Author: zzzl    Time: 2007-11-23 08:42

建议楼主试试sqlserver2005的NTILE和ROW_NUMER
Author: kendling    Time: 2007-11-23 16:49

哈,刚想说说,这存储过程在SQL2005里不能,因为该存储过程在“SQL查询分析器”里会返回三个数据集,前两个为空集。
而在SQL2000里会被最后一个数据集复盖,所以不会显得有问题。
但是在SQL2005里却返回了第一个数据集,原因我还不知道,解决方法也没有研究出来,有哪位兄弟知道的说说。

zzzl,你说的NTILE和ROW_NUMER是怎么一回事,我今天才装的2005,因为客户要跑这个,没有办法不搞。咋都还没学
Author: 小夏    Time: 2007-11-23 22:47

//-----下面是一个支持任意表的 SQL SERVER2000分页存储过程

//----分页存储过程开始创建-----------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'PageShowOne' AND type = 'P')
   DROP PROCEDURE PageShowOne
GO

CREATE Proc PageShowOne
    @PageSize int=10 ,--每页显示的记录数
    @PageCurrent int=1 ,--当前要显示的页号
    @FdName varchar(100)='' ,--主键名或者标识列名
    @SelectStr varchar(2000)='', --select子句,不包含select关键字,如:*或者Id,UserId,UserName等。
    @FromStr varchar(1000)='', --from子句,不包含from关键子,如:myTable或者myTable,yourTable
    @WhereStr varchar(2000)='', --Where子句,不包含where关键字,如空的,或者 id>2 等
    @OrderByStr varchar(1000)='',--order by 子句,不包含order by 子句 ,如id desc,UserId asc 等
    @CountRows int=0 output, --返回记录总数
    @CountPage int=0 output --返回总页数
as
--------定义局部变量---------
declare @Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
declare @OrderBySqls varchar(1000) --order by 子句
declare @WhereSqls varchar(2000) --where 子句
declare @Sqls nvarchar(4000) --最终组合成的Sqls语句
declare @TmpStr varchar(2000) --临时
----------------------------
if @OrderByStr <> ''
    set @OrderBySqls = ' order by '+@OrderByStr
else
    set @OrderBySqls = ''
--------
if @WhereStr <> ''
    set @WhereSqls = ' where ('+@WhereStr+')'
else
    set @WhereSqls = ''
--------
set @TmpStr = @WhereSqls
--如果显示第一页,可以直接用top来完成
if @PageCurrent<=1
begin
    select @Id1=cast(@PageSize as varchar(20))
    exec('select top '+@Id1+' '+@SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls)
    goto LabelRes
end
---------------------------
select @Id1=cast(@PageSize as varchar(20))
       ,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
----------
if @WhereSqls <> ''
    set @WhereSqls = @WhereSqls + ' and (' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+'))'
else
    set @WhereSqls = ' where ' + @FdName+' not in(select top '+@Id2+' '+@FdName+' from '+@FromStr+@WhereSqls+@OrderBySqls+')'
----------
set @Sqls = 'select top '+@Id1+ ' '+ @SelectStr+' from '+@FromStr+@WhereSqls+@OrderBySqls
exec (@Sqls)
-----------
LabelRes:
-----返回总记录数
set @Sqls = 'select @a=count(1)  from '+@FromStr+@TmpStr
exec sp_executesql @sqls,N'@a int output',@CountRows output
-----返回总页数
if @CountRows <= @PageSize
    set @CountPage = 1
else
begin
    set @CountPage = @CountRows/@PageSize
    if (@CountRows%@PageSize) > 0
       set @CountPage = @CountPage + 1
end
return
GO
//----分页存储过程结束---------------------------------------------------------------------------------------

//----Delphi7调用过程开始----------------------------------------------------------------------------------------

首先在 public 中定义 i, RsCount: integer;
在frmMain中放置一个dbgrid , datasource, AdoStoredProc [name->sp], 和4个 Button, 设置好相关连接!

procedure TfrmMain.btnFirstClick(Sender: TObject);
begin
  i := 1;
  sp.ProcedureName := 'PageShowOne;1';
  sp.Close;
  sp.Parameters.ParamByName('@PageSize').Value := 50;
  sp.Parameters.ParamByName('@PageCurrent').Value := i;
  sp.Parameters.ParamByName('@FdName').Value := '图纸编号';
  sp.Parameters.ParamByName('@SelectStr').Value := '图纸编号,模具形状一,模具形状二,模具形状三,最大高度,最大宽度,重量,有无波面,螺丝孔数';
  sp.Parameters.ParamByName('@FromStr').Value := 'tuzhi';
  sp.Parameters.ParamByName('@WhereStr').Value := '';
  sp.Parameters.ParamByName('@OrderByStr').Value := '日期';
  sp.Open;
  Label1.Caption := '记录总数:'+IntToStr(sp.Parameters.ParamValues['@CountRows']);
  Label2.Caption := '总页数:'+IntToStr(sp.Parameters.ParamValues['@CountPage']);
  RsCount := sp.Parameters.ParamValues['@CountPage'];
  Label3.Caption := '第 '+IntToStr(i)+ ' 页';
end;

procedure TfrmMain.btnNextClick(Sender: TObject);
begin
  if i >= RsCount then
    ShowMessage('已经是最后一页!')
  else
    i := i + 1;
  sp.ProcedureName := 'PageShowOne;1';
  sp.Close;
  sp.Parameters.ParamByName('@PageSize').Value := 50;
  sp.Parameters.ParamByName('@PageCurrent').Value := i;
  sp.Parameters.ParamByName('@FdName').Value := '图纸编号';
  sp.Parameters.ParamByName('@SelectStr').Value := '图纸编号,模具形状一,模具形状二,模具形状三,最大高度,最大宽度,重量,有无波面,螺丝孔数';
  sp.Parameters.ParamByName('@FromStr').Value := 'tuzhi';
  sp.Parameters.ParamByName('@WhereStr').Value := '';
  sp.Parameters.ParamByName('@OrderByStr').Value := '日期';
  sp.Open;
  Label3.Caption := '第 '+IntToStr(i)+ ' 页';
end;

procedure TfrmMain.btnPriorClick(Sender: TObject);
begin
  if i > 1 then
    i := i - 1
  else ShowMessage('已经是第一页了!');
  sp.ProcedureName := 'PageShowOne;1';
  sp.Close;
  sp.Parameters.ParamByName('@PageSize').Value := 50;
  sp.Parameters.ParamByName('@PageCurrent').Value := i;
  sp.Parameters.ParamByName('@FdName').Value := '图纸编号';
  sp.Parameters.ParamByName('@SelectStr').Value := '图纸编号,模具形状一,模具形状二,模具形状三,最大高度,最大宽度,重量,有无波面,螺丝孔数';
  sp.Parameters.ParamByName('@FromStr').Value := 'tuzhi';
  sp.Parameters.ParamByName('@WhereStr').Value := '';
  sp.Parameters.ParamByName('@OrderByStr').Value := '日期';
  sp.Open;
  Label3.Caption := '第 '+IntToStr(i)+ ' 页';
end;

procedure TfrmMain.btnLastClick(Sender: TObject);
begin
  i := RsCount;
  sp.ProcedureName := 'PageShowOne;1';
  sp.Close;
  sp.Parameters.ParamByName('@PageSize').Value := 50;
  sp.Parameters.ParamByName('@PageCurrent').Value := i;
  sp.Parameters.ParamByName('@FdName').Value := '图纸编号';
  sp.Parameters.ParamByName('@SelectStr').Value := '图纸编号,模具形状一,模具形状二,模具形状三,最大高度,最大宽度,重量,有无波面,螺丝孔数';
  sp.Parameters.ParamByName('@FromStr').Value := 'tuzhi';
  sp.Parameters.ParamByName('@WhereStr').Value := '';
  sp.Parameters.ParamByName('@OrderByStr').Value := '日期';
  sp.Open;
  Label3.Caption := '第 '+IntToStr(i)+ ' 页';
end;
//--------------调用结束--------------------------------------------------------------------------------------------------------------------
Author: Passion    Time: 2007-11-23 23:33

终于看见小夏灌水了。
Author: zzzl    Time: 2007-11-24 09:32

是2005新增的评价函数,可以方便的做出分页效果
Author: niaoge    Time: 2007-11-24 11:03

--灌一下,效率如何没有测试,
--不过纯的SQL语句比EXEC效率要好,而且测容易找出SQL Text内的错误
CREATE  PROCEDURE dbo.procedurename
@ROWCOUNT INT OUTPUT ,--总行数
@PAGESIZE INT=NULL ,--每页数
@NOWPAGE INT=NULL  --从第几页开始
as

--先做一个带自动增加ID的临时空表

SELECT IDENTITY(int, 1,1) AS ID,
    CAST(NULL AS NVARCHAR(255)) AS 主建1,
    CAST(NULL AS NVARCHAR(255)) AS 主建2,
      ...--有多少主建做几个,类型根据表自已定

INTO #TB  
    WHERE 1=0

--下面将主建塞到上面的空表内,ID将自动增加
--这一步主建不多的话,执行起来应不会慢
INSERT #TB(
             主建1,
             主建2,
             ...
           )
    select
            主建1,
            主建2...
              from 表
                where......--这个条件和原来的条件一样
      
SET @ROWCOUNT=@@ROWCOUNT --这里得出影响总行数,
                         --这个总行数不是所有表内的所有数据,而是跟据条件得出的行数


--最后选出数据
select A.*
    from 表 A  
         WHERE
           EXISTS (SELECT -- EXISTS效率优于其它
             B.*
               FROM #TB B WHERE
                  A.主建1=B.主建1,
                  A.主建1=B.主建2,
                    ...
                  and
                  (B.ID+@PAGESIZE-1)/@PAGESIZE=@NOWPAGE               
             )

[ 本帖最后由 niaoge 于 2007-11-24 11:05 编辑 ]
Author: kendling    Time: 2007-11-25 20:14

测试了一下小夏的代码,如果@PageCurrent>PageCount的话会没有结果输出。
Author: kendling    Time: 2007-11-26 14:47

还有一个问题,无法处理多主键的表。
Author: skyjacker    Time: 2007-11-29 08:27

学习了
Author: kendling    Time: 2007-12-5 11:16

补一下SQL2005的分页存储过程:

USE [PODB]
GO
/****** Object:  StoredProcedure [dbo].[sp_TablePaging]    Script Date: 12/05/2007 11:08:48 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER  PROCEDURE [dbo].[sp_TablePaging]
(
        @ATable VARCHAR(100),
        @AFilter VARCHAR(1000),
        @AOrderBy VARCHAR(1000),
        @APage INT
)
AS
        SET NOCOUNT ON
        DECLARE @p1 INT, @PageSize INT, @sql NVARCHAR(4000), @PageCount INT, @RowCount INT, @RecordCount INT

        -- 取记录总数
        SET @sql = 'SELECT @cc = COUNT(*) FROM ' + @ATable
        IF @AFilter <> ''
                SET @sql = @sql + ' WHERE ' + @AFilter

        EXEC sp_ExecuteSQL @sql, N'@cc INT OUTPUT', @RowCount OUTPUT

        SET @PageSize = 100
        -- 限制页数不超出范围
        SET @PageCount = Ceiling(1.0 * @RowCount / @PageSize)
        IF @APage > @PageCount
                SET @APage = @PageCount
        IF @APage < 1
                SET @APage = 1

        -- 将页总数、记录总数添加到输出
        IF @Apage = 1
        BEGIN
                SET @sql = 'SELECT TOP ' + CONVERT(VARCHAR, @PageSize) + ' *, ' +
                        CONVERT(VARCHAR, @PageCount) + ' AS PageCount, ' +
                        CONVERT(VARCHAR, @RowCount) + ' AS RecordCount FROM ' + @ATable
                IF @AFilter <> ''
                        SET @sql = @sql + ' WHERE ' + @AFilter
                IF @AOrderBy <> ''
                        SET @sql = @sql + ' ORDER BY ' + @AOrderBy
        END
        ELSE
        BEGIN
                SET @sql = 'SELECT TOP ' + CONVERT(VARCHAR, @PageSize) + ' *, ' +
                        CONVERT(VARCHAR, @PageCount) + ' AS PageCount, ' +
                        CONVERT(VARCHAR, @RowCount) + ' AS RecordCount FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY' +
                        @AOrderBy + ') AS RowNumber FROM ' + @ATable
                IF @AFilter <> ''
                        SET @sql = @sql + ' WHERE ' + @AFilter
                SET @sql = @sql + ') PagingTable WHERE RowNumber > ' + CONVERT(VARCHAR, (@APage - 1) * @PageSize)
        END
        EXEC (@sql)




Welcome to CnPack Forum (http://bbs.cnpack.org/) Powered by Discuz! 5.0.0