争怎路由网/网站教程/内容

NT Fan:你要的bbs的数据结构与存储过程(二)

网站教程2024-06-27 阅读
/*************************************************************************/
/* */
/*procedure : up_GetForumList*/
/* */
/*Description:取得版面列表 */
/* */
/*Parameters: None */
/* */
/*Use table:forum , bbsuser*/
/* */
/*Author: bigeagle@163.net */
/* */
/*Date: 2000/2/10*/
/* */
/*History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_GetForumList'))
 drop proc up_GetForumList
go

create proc up_GetForumList
as
select a.id , a.rootid , a.fatherid , a.layer , a.title , a.topiccount , a.description ,
'UserID'=b.id , b.UserName , b.Email , b.Homepage , b.Signature
 from forum as a join BBSUser as b on a.Masterid=b.ID order by rootid , layer
go
select id , title , rootid from forum
up_getforumlist


/*************************************************************************/
/* */
/*procedure : up_InsertForum */
/* */
/*Description:新建版面 */
/* */
/*Parameters: @a_strName :版面名称 */
/*@a_strDescription: 版面描述*/
/*@a_intFatherID: 分类ID,如果是0说明是大分类*/
/* */
/*Use table:forum*/
/* */
/*Author: bigeagle@163.net */
/* */
/*Date: 2000/4/23*/
/* */
/*History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_InsertForum'))
 drop proc up_InsertForum
go

create proc up_InsertForum @a_strName varchar(50) , @a_strDescription varchar(255) , @a_intFatherID tinyint
 as
 /*定义局部变量*/
 declare @intLayer tinyint
 declare @intRootID tinyint

 /*如果是版面并且没有指定分类,则返回-1*/
 if(@a_intFatherID <> 0 and not exists(select * from forum where id = @a_intFatherID))
return(-1)

 /*根据@a_intFatherID计算layer , rootid*/
 if(@a_intFatherID = 0)
 begin
select @intLayer = 0
select @intRootID = 0
 end
else
 begin
 select @intLayer = 1
 select @intRootID = @a_intFatherID
 end

 Insert into Forum(rootid , layer , fatherid , title , description)
values(@intRootID , @intLayer , @a_intFatherID , @a_strName , @a_strDescription)
 if (@a_intFatherID = 0)
 begin
select @intRootID = @@identity
update Forum set rootid = @intRootID where id = @intRootID
 end
go 

/*************************************************************************/
/* */
/*procedure : up_DeleteForum */
/* */
/*Description:删除版面 */
/* */
/*Parameters: @a_intForumID :版面id*/
/* */
/*Use table:forum*/
/* */
/*Author: bigeagle@163.net */
/* */
/*Date: 2000/4/23*/
/* */
/*History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_DeleteForum'))
 drop proc up_DeleteForum
go

create proc up_DeleteForum @a_intForumID tinyint
as
 deletefrom Forum where id = @a_intForumID
 deletefrom Forum where RootID = @a_intForumID
go

select id , title , rootid , fatherid from forum

/*************************************************************************/
/* */
/*procedure : up_PostTopic */
/* */
/*Description:发贴子 */
/* */
/*Parameters: @a_intForumID : 版面id */
/*@a_intFatherID: 父贴ID,如果是新主题为0*/
/*@a_strSubject:标题 */
/*@a_strContent:内容 */
/*@a_intUserID: 发贴人ID */
/*@a_intFaceID: 表情ID */
/*@a_strIP: 发贴人IP */
/* */
/*Use table:bbs , forum , bbsuser*/
/* */
/*Author: bigeagle@163.net */
/* */
/*Date: 2000/2/13*/
/* */
/*History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_PostTopic'))
 drop proc up_PostTopic
go

create proc up_PostTopic
@a_intForumID int ,
@a_intFatherID int ,
@a_strSubject varchar(255) ,
@a_strContent text ,
@a_intUserID int ,
@a_intFaceID int ,
@a_strIP varchar(255)
as
/*定义局部变量*/
declare @intRootID int--根id
declare @dblOrderNumfloat(53) --排序基数
declare @intLayer int --层
declare @dblNextOrderNumfloat(53) --下一回贴的ordernum

/*判断有没有这个版面*/
if not exists(select * from forum where id = @a_intForumID)
 return(-1)

/*判断新贴子还是回应贴子*/
if (@a_intFatherID = 0) --根贴
 begin
select @intRootID = isnull(max(id) , 0) + 1 from bbs
select @dblOrderNum = 9e+24
select @intLayer = 1
 end
else--回贴
 begin
select @intRootID = rootid , @intLayer = layer + 1 , @dblOrderNum = ordernum
 from bbs where id = @a_intFatherID 

/*如果没找到父贴则返回错误*/
if (@@rowcount = 0) return -1

/*计算ordernum*/
select @dblNextOrderNum = isnull(max(ordernum), 0)
 from bbs where ordernum < @dblOrderNum and rootid=@intRootID
select @dblOrderNum = (@dblOrderNum + @dblNextOrderNum) / 2 
 end

/*由于对两个表操作,用事务*/
Begin transaction
/*插入贴子*/
insert into bbs(RootID , FatherID , Layer , OrderNum , UserID , ForumID ,
Subject , Content , FaceID , IP)
 values(@intRootID , @a_intFatherID , @intLayer , @dblOrderNum ,
@a_intUserID , @a_intForumID ,
@a_strSubject , @a_strContent , @a_intFaceID , @a_strIP)
 /*判断是否成功*/
 if (@@error != 0) goto OnError

 /*更新版面贴子数*/
 update forum set topiccount = topiccount + 1 where id = @a_intForumID
 if (@@error != 0) goto OnError
 
 /*更新用户分数*/
 update BBSUser set point = point + 1 where id = @a_intUserID
 if (@@error !=0) goto OnError
 
 /*执行*/
 commit transaction
 return(0)

 /*错误处理*/
 OnError:
rollback transaction
return(-1)


go
select id from bbs where fatherid=0 order by rootid desc, ordernum desc
up_posttopic 1 , 12 , '哈哈哈,见笑了' , 'hello , world' , 1 , 1 , '203.93.95.10'

/*************************************************************************/
/* */
/*procedure : up_GetTopicList*/
/* */
/*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_GetTopicList'))
 drop proc up_GetTopicList
go

create proc up_GetTopicList
 @a_intForumID int ,
 @a_intPageNo int,
 @a_intPageSize int
as
 /*定义局部变量*/
 declare @intBeginID int
 declare @intEndID int
 declare @intRootRecordCount int
 declare @intPageCount int
 declare @intRowCountint
 /*关闭计数*/
 set nocount on
 
 /*检测是否有这个版面*/
 if not exists(select * from forum where id = @a_intForumID)
return (-1)

 /*求总共根贴数*/
 select @intRootRecordCount = count(*) from bbs where fatherid=0 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 fatherid=0 and forumid=@a_intForumID
order by id desc

 /*结束rootID*/
 set @intRowCount = @a_intPageNo * @a_intPageSize
 /*限制条数*/
 set rowcount @intRowCount
 select @intEndID = rootid from bbs where fatherid=0 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 Forumid=@a_intForumID and a.rootid between @intEndID and @intBeginID
 order by a.rootid desc , a.ordernum desc
 return(@@rowcount)
 --select @@rowcount
go
up_getTopiclist 3 , 1 , 20
select * from bbs where fatherid=0 order by id desc
select * from bbsuser

……

相关阅读