鸿 网 互 联 www.68idc.cn

当前位置 : 服务器租用 > cms安装教程 > discuz > >

Discuz!NT 代码阅读笔记(8)--DNT的几个分页存储过程解析

来源:互联网 作者:佚名 时间:2015-09-28 08:37
DNT 的几个分页 存储 过程 解析 在 DNT 安装时会创建一些 存储 过程 ,用于完成 DNT 的功能。 这些 存储 过程 位于、 upload_files\install\sqlscript\sqlserver 文件夹中的 setup2.2 - 2005.sql 文件中。 其中的分页 存储 过程 如下: 我认识 存储 过程 这

DNT的几个分页存储过程解析

DNT安装时会创建一些存储过程,用于完成DNT的功能。

这些存储过程位于、upload_files\install\sqlscript\sqlserver文件夹中的setup2.2 - 2005.sql文件中。

其中的分页存储过程如下:

我认识存储过程这个东西不久,仔细看了下,注释出来.一直以为存储过程很高深,多看代码感觉也不过如此.就是一些函数而已

 

下边列举的4个分页存储过程,写法风格各不相同.

在算法上只有两种.一个使用了ROW_NUMBER() OVER ( ORDER BY [aid] DESC ) AS ROWID这个函数.另一个手动计算页码,将该页的所有数据以DESC的方式选到一个临时表中.再在这个表中搜索Top数据.然后返回.

 

从风格上讲,前两个使用了很多参数来组织语句,代码美观大方.后两个先组织了Sql字符串,加入搜索条件后才使用EXEC语句执行.代码乱七八糟的,不是很好看,但也实现了功能.

 

  

(1) dnt_getfavoriteslist  获取用户收藏的存储过程

这个存储过程用于查看用户收藏是的分页显示.返回一个包含帖子主题的表供前台调用

CREATE PROCEDURE [dnt_getfavoriteslist]    --分页获取收藏主题表

    @uid INT ,     --用户ID             

    @pagesize INT ,  --页大小

    @pageindex INT   --第几页

AS --SELECT [f].[uid],[f].[tid],[topics].[title],[topics].[poster],[topics].[postdatetime],[topics].[replies],[topics].[views],[topics].[posterid] FROM [dnt_favorites] [f] LEFT JOIN [dnt_topics] [topics]  ON   [f].[tid]=[topics].[tid] WHERE  [f].[typeid]=0 AND [f].[uid]=@uid

    IF @pageindex = 1   --处理第一页

        BEGIN

            SELECT TOP ( @pagesize )   --因为是第一页,选择出前@pagesize就是所需要的内容

                    [uid] ,

                    [tid] ,

                    [title] ,

                    [poster] ,

                    [postdatetime] ,

                    [replies] ,

                    [views] ,

                    [posterid]

            FROM    ( SELECT    [f].[uid] ,     --这个Select用于选出用户用户所有的收藏 [f]代表表[dnt_favorites][topics]代表[dnt_topics]

                                           --两个表连接起来查询出用户收藏的所有主题

                                [f].[tid] ,

                                [topics].[title] ,

                                [topics].[poster] ,

                                [topics].[postdatetime] ,

                                [topics].[replies] ,

                                [topics].[views] ,

                                [topics].[posterid]

                      FROM      [dnt_favorites] [f]    --此处设置f代表[dnt_favorites]

                                LEFT JOIN [dnt_topics] [topics] ON [f].[tid] = [topics].[tid]

                      WHERE     [f].[typeid] = 0

                                AND [f].[uid] = @uid

                    ) favorites   --此处的favorites相当于一个表。From语句后边的SELECT语句选择出来的内容存放着这个表中

            ORDER BY [tid] DESC

        END

    ELSE

        BEGIN   --@pageindex不等于1,处理其他页

            SELECT TOP ( @pagesize )     --不是第一页,需要对选择出来内容的范围加以约束。Where之后未约束

                    [uid] ,

                    [tid] ,

                    [title] ,

                    [poster] ,

                    [postdatetime] ,

                    [replies] ,

                    [views] ,

                    [posterid]

            FROM    ( SELECT    [f].[uid] ,

                                [f].[tid] ,

                                [topics].[title] ,

                                [topics].[poster] ,

                                [topics].[postdatetime] ,

                                [topics].[replies] ,

                                [topics].[views] ,

                                [topics].[posterid]

                      FROM      [dnt_favorites] [f]

                                LEFT JOIN [dnt_topics] [topics] ON [f].[tid] = [topics].[tid]

                      WHERE     [f].[typeid] = 0

                                AND [f].[uid] = @uid

                    ) f1

            WHERE   [tid] < ( SELECT    MIN([tid])   --根据页大小和页序号设置TID的范围

                              FROM      ( SELECT TOP ( ( @pageindex - 1 )

                                                        * @pagesize )

                                                    [tid]

                                          FROM      ( SELECT  [f].[uid] ,   --这里跟处理第一页一样,选择出所有收藏的内容作为一个表,供查询

                                                              [f].[tid] ,

                                                              [topics].[title] ,

                                                              [topics].[poster] ,

                                                              [topics].[postdatetime] ,

                                                              [topics].[replies] ,

                                                              [topics].[views] ,

                                                              [topics].[posterid]

                                                      FROM    [dnt_favorites] [f]

                                                              LEFT JOIN [dnt_topics] [topics] ON [f].[tid] = [topics].[tid]

                                                      WHERE   [f].[typeid] = 0

                                                              AND [f].[uid] = @uid

                                                    ) f2

                                          ORDER BY  [tid] DESC   --因为是DESC所以最上边的内容为需要找的对象

                                        ) AS tblTmp

                            )

            ORDER BY [tid] DESC

        END

GO

 

 

(2) dnt_getmyattachments 获取用户附件列表存储过程

CREATE PROCEDURE [dnt_getmyattachments]  --获取用户附件并分页

    @uid INT ,                     

    @pageindex INT ,    --输入页码

    @pagesize INT       --输入每一页的大小

AS

    DECLARE @startRow INT ,

        @endRow INT

    SET @startRow = ( @pageindex - 1 ) * @pagesize + 1         --计算查找数据的开始和结束行号                

    SET @endRow = @startRow + @pagesize - 1

    SELECT  [ATTACHMENTS].[aid] ,

            [ATTACHMENTS].[uid] ,

            [ATTACHMENTS].[attachment] ,

            [ATTACHMENTS].[description] ,

            [ATTACHMENTS].[downloads] ,

            [ATTACHMENTS].[extname] ,

            [ATTACHMENTS].[filename] ,

            [ATTACHMENTS].[pid] ,

            [ATTACHMENTS].[postdatetime] ,

            [ATTACHMENTS].[tid]

    FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY [aid] DESC ) AS ROWID ,  --这里的Select相当于一个表(后边加上as)

                                                                     --相当于 select * from ATTACHMENTS where

                                                                     --ATTACHMENTSSELECT    ROW_NUMBER() OVER...的结果

                                                                     --ROW_NUMBER()是一个函数,在这里的作用是给选择出来的结果加

                                                                     --一个从1开始的连续增长的行号

                                                                     --因为对于一个用户来讲,附件的aid是不连续的。

                        [dnt_myattachments].[aid] ,                        

                        [dnt_myattachments].[uid] ,                       

                        [dnt_myattachments].[attachment] ,

                        [dnt_myattachments].[description] ,

                        [dnt_myattachments].[downloads] ,

                        [dnt_myattachments].[extname] ,

                        [dnt_myattachments].[filename] ,

                        [dnt_myattachments].[pid] ,

                        [dnt_myattachments].[postdatetime] ,

                        [dnt_myattachments].[tid]

              FROM      [dnt_myattachments]

              WHERE     [dnt_myattachments].[uid] = @uid

            ) AS ATTACHMENTS

    WHERE   ROWID BETWEEN @startRow AND @endRow    --根据ATTACHMENTS起止行序号选择数据输出

GO

 

网友评论
<