bbs的数据结构和存储过程（三）
-
/*************************************************************************/
/* */
/* procedure : up_GetPostedTopicList */
/* */
/* Description: 精华区贴子列表 */
/* */
/* Parameters: @a_intForumID : 版面id */
/* @a_intPageNo: 页号 */
/* @a_intPageSize: 每页显示数，以根贴为准 */
/* */
/* Use table: bbs , forum */
/* */
/* Author: bigeagle@163.net */
/* */
/* Date: 2000/2/14 */
/* */
/* History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_GetPostedTopicList'))
drop proc up_GetPostedTopicList
go
create proc up_GetPostedTopicList
@a_intForumID int ,
@a_intPageNo int ,
@a_intPageSize int
as
/*定义局部变量*/
declare @intBeginID int
declare @intEndID int
declare @intRootRecordCount int
declare @intPageCount int
declare @intRowCount int
/*关闭计数*/
set nocount on
/*检测是否有这个版面*/
if not exists(select * from forum where id = @a_intForumID)
return (-1)
/*求总共根贴数*/
select @intRootRecordCount = count(*) from bbs where posted=1 and forumid=@a_intForumID
if (@intRootRecordCount = 0) --如果没有贴子，则返回零
return 0
/*判断页数是否正确*/
if (@a_intPageNo - 1) * @a_intPageSize > @intRootRecordCount
return (-1)
/*求开始rootID*/
set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1
/*限制条数*/
set rowcount @intRowCount
select @intBeginID = rootid from bbs where posted=1 and forumid=@a_intForumID
order by id desc
/*结束rootID*/
set @intRowCount = @a_intPageNo * @a_intPageSize
/*限制条数*/
set rowcount @intRowCount
select @intEndID = rootid from bbs where posted=1 and forumid=@a_intForumID
order by id desc
/*恢复系统变量*/
set rowcount 0
set nocount off
select a.id , a.layer , a.forumid , a.subject , a.faceid , a.hits , a.time , a.UserID , a.fatherid , a.rootid ,
'Bytes' = datalength(a.content) , b.UserName , b.Email , b.HomePage , b.Signature , b.Point
from bbs as a join BBSUser as b on a.UserID = b.ID
where posted=1 and Forumid=@a_intForumID and a.rootid between @intEndID and @intBeginID
order by a.rootid desc , a.ordernum desc
return(@@rowcount)
--select @@rowcount
go
select id , rootid , fatherid , forumid , posted from bbs
up_getpostedtopiclist 3 ,1 , 20
/*************************************************************************/
/* */
/* procedure : up_GetTopic */
/* */
/* Description: 取贴子 */
/* */
/* Parameters: @a_intTopicID : 贴子id */
/* */
/* Use table: bbs */
/* */
/* Author: bigeagle@163.net */
/* */
/* Date: 2000/2/16 */
/* */
/* History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_GetTopic'))
drop proc up_GetTopic
go
create proc up_GetTopic @a_intTopicID int
as
/*如果没有这贴子*/
if not exists (select * from bbs where id = @a_intTopicID)
return (-1)
/*更新该贴的点击数*/
update bbs set hits = hits + 1 where id = @a_intTopicID
select a.* , 'Bytes' = datalength(a.content) ,
b.UserName , b.Email , b.Homepage , b.point , b.Signature
from bbs as a join BBSUser as b on a.UserID = b.id
where a.id = @a_intTopicID
go
up_getTopic 11
/*************************************************************************/
/* */
/* procedure : up_DeleTopic */
/* */
/* Description: 删除贴子及子贴，更新发贴人信息 */
/* */
/* Parameters: @a_intTopicID : 贴子id */
/* */
/* Use table: bbs */
/* */
/* Author: bigeagle@163.net */
/* */
/* Date: 2000/2/24 */
/* */
/* History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_DeleTopic'))
drop proc up_DeleTopic
go
create proc up_DeleTopic @a_intTopicID int
as
/*定义局部变量*/
declare @intRootID int
declare @intLayer int
declare @floatOrderNum float(53)
declare @floatNextOrderNum float(53)
declare @intCounts int
declare @intForumID int
/*取消计数*/
set nocount on
/*首先查找这个贴子的rootid和ordernum，没有则返回*/
select @intRootID = RootID ,
@floatOrderNum = OrderNum ,
@intLayer = layer ,
@intForumID = forumid
from bbs where id = @a_intTopicID
if @@rowcount = 0
return (-1)
/*取下一个同层贴子的ordernum*/
select @FloatNextOrderNum = isnull(max(ordernum) , 0)
from bbs
where RootID=@intRootID
and layer=@intLayer and ordernum < @floatOrderNum
/*多表操作，用事务*/
begin transaction
/*首先删贴*/
delete from bbs
where rootid=@intRootID and ordernum > @floatNextOrderNum
and ordernum <= @floatOrderNum
select @intCounts = @@rowcount
if (@@error != 0)
goto Error
/*论坛贴子数减少*/
update forum set topiccount = topiccount - @intCounts where id=@intForumID
if (@@error != 0)
goto Error
/*完成事务，返回*/
commit transaction
set nocount off
return(0)
Error:
rollback transaction
set nocount off
return (-1)
go
select forumid from bbs
update bbs set forumid=4
/*************************************************************************/
/* */
/* procedure : up_GetUserInfo */
/* */
/* Description: 取得发贴人信息 */
/* */
/* Parameters: @a_strUserName : 用户笔名 */
/* */
/* Use table: bbsuser */
/* */
/* Author: bigeagle@163.net */
/* */
/* Date: 2000/4/16 */
/* */
/* History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_GetUserInfo'))
drop proc up_GetUserInfo
go
create proc up_GetUserInfo @a_strUserName varchar(20)
as
declare @m_intOrder int --排名
declare @m_intPoint int --积分
set nocount on
/*如果没有找到该用户，则返回-1*/
select @m_intPoint = point from bbsuser where username=@a_strUserName
if(@@rowcount = 0)
return(-1)
/*求排名*/
select @m_intOrder = count(*) + 1 from bbsuser where point > @m_intPoint
select * , 'order' = @m_intOrder from bbsuser where username=@a_strUserName
set nocount off
go
up_getuserinfo '廖家远'
/*************************************************************************/
/* */
/* procedure : up_PostedTopic */
/* */
/* Description: 将贴子转入精华区 */
/* */
/* Parameters: @a_intTopicID 贴子id */
/* */
/* Use table: bbs, postedtopic */
/* */
/* Author: bigeagle@163.net */
/* */
/* Date: 2000/4/17 */
/* */
/* History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id= object_id('up_postedtopic'))
drop proc up_postedtopic
go
create proc up_PostedTopic @a_intTopicID int
as
/*定义局部变量*/
declare @m_intUserID int --发贴人ID
/*查找是否有这个贴子*/
select @m_intUserID = userid from bbs where id = @a_intTopicID
if(@@rowcount != 1)
return -1
/*因为对两个表操作所以用事务*/
begin transaction
update bbs set posted = 1 where id = @a_intTopicID
if(@@error <> 0)
goto Error
update bbsuser set point = point + 3 where id = @m_intUserID
if(@@error <> 0)
goto Error
Commit transaction
return (0)
Error:
rollback transaction
go
相关视频
相关阅读 Windows错误代码大全 Windows错误代码查询激活windows有什么用Mac QQ和Windows QQ聊天记录怎么合并 Mac QQ和Windows QQ聊天记录Windows 10自动更新怎么关闭 如何关闭Windows 10自动更新windows 10 rs4快速预览版17017下载错误问题Win10秋季创意者更新16291更新了什么 win10 16291更新内容windows10秋季创意者更新时间 windows10秋季创意者更新内容kb3150513补丁更新了什么 Windows 10补丁kb3150513是什么
- 文章评论
-
热门文章 没有查询到任何记录。
最新文章
迅雷新手完全入门手册
asp下面javascript上传图片限制格式大小方法告诉大家网页弹出窗口应用总结ASP常见错误类型大全asp常见错误分析和解决办法
人气排行 总是弹出visual studio 实时调试器 三种解决SQLSERVER存储过程及调用详解Asp获取真实IP地址ASP中连接Mssql的几种方法一个简单好用的UBB编辑器(含代码)如何用Split将字符串转换为数组并获取数组下ASP防止表单重复提交的办法告诉你免费的简单聊天室源代码
查看所有0条评论>>