bbs的資料結構和儲存過程（二） (轉)

bbs的資料結構和儲存過程（二） (轉)[@more@]/*************************************************************************/
/* */
/* procedure : up_GetForumList */
/* */
/* Description: 取得版面列表 */
/* */
/* Parameters: None */
/* */
/* Use table: forum , bbsuser */
/* */
/* Author: bigeagle@163 */
/* */
/* Date: 2000/2/10 */
/* */
/* History: */
/* */
/*************************************************************************/
if exists( * from syss where id = object_id('up_GetForumList'))
drop proc up_GetForumList
go

create proc up_GetForumList
as
select a.id , a.id , a.herid , a.layer , a.title , a.topiccount , a.description ,
'UserID'=b.id , b.UserName , b.E , 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 = @@ntity
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
delete from Forum where id = @a_intForumID
delete from 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: 發貼人*/
/* */
/* 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 @lOrderNum float(53) --排序基數
declare @intLayer int --層
declare @dblNextOrderNum float(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 @intRowCount int
/*關閉計數*/
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. , 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