📄 bbs的数据结构和存储过程（二）.htm
字号:
新建版面 */<BR>/* */<BR>/* Parameters: @a_strName : 版面名称 */<BR>/*
@a_strDescription: 版面描述 */<BR>/* @a_intFatherID: 分类ID，如果是0说明是大分类
*/<BR>/* */<BR>/* Use table: forum */<BR>/* */<BR>/* Author:
bigeagle@163.net */<BR>/* */<BR>/* Date: 2000/4/23 */<BR>/*
*/<BR>/* History: */<BR>/*
*/<BR>/*************************************************************************/<BR>if
exists(select * from sysobjects where id =
object_id('up_InsertForum'))<BR>drop proc up_InsertForum<BR>go</P>
<P>create proc up_InsertForum @a_strName varchar(50) ,
@a_strDescription varchar(255) , @a_intFatherID
tinyint<BR>as<BR>/*定义局部变量*/<BR>declare @intLayer
tinyint<BR>declare @intRootID
tinyint<BR><BR>/*如果是版面并且没有指定分类，则返回-1*/<BR>if(@a_intFatherID
<> 0 and not exists(select * from forum where id =
@a_intFatherID))<BR>return(-1)</P>
<P>/*根据@a_intFatherID计算layer , rootid*/<BR>if(@a_intFatherID =
0)<BR>begin<BR>select @intLayer = 0<BR>select @intRootID =
0<BR>end<BR>else<BR>begin<BR>select @intLayer = 1<BR>select
@intRootID = @a_intFatherID<BR>end</P>
<P>Insert into Forum(rootid , layer , fatherid , title ,
description) <BR>values(@intRootID , @intLayer , @a_intFatherID ,
@a_strName , @a_strDescription)<BR>if (@a_intFatherID =
0)<BR>begin<BR>select @intRootID = @@identity<BR>update Forum set
rootid = @intRootID where id = @intRootID<BR>end<BR>go </P>
<P>/*************************************************************************/<BR>/*
*/<BR>/* procedure : up_DeleteForum */<BR>/* */<BR>/* Description:
删除版面 */<BR>/* */<BR>/* Parameters: @a_intForumID : 版面id */<BR>/*
*/<BR>/* Use table: forum */<BR>/* */<BR>/* Author:
bigeagle@163.net */<BR>/* */<BR>/* Date: 2000/4/23 */<BR>/*
*/<BR>/* History: */<BR>/*
*/<BR>/*************************************************************************/<BR>if
exists(select * from sysobjects where id =
object_id('up_DeleteForum'))<BR>drop proc up_DeleteForum<BR>go</P>
<P>create proc up_DeleteForum @a_intForumID
tinyint<BR>as<BR>delete from Forum where id =
@a_intForumID<BR>delete from Forum where RootID =
@a_intForumID<BR>go</P>
<P>select id , title , rootid , fatherid from forum</P>
<P>/*************************************************************************/<BR>/*
*/<BR>/* procedure : up_PostTopic */<BR>/* */<BR>/* Description:
发贴子 */<BR>/* */<BR>/* Parameters: @a_intForumID : 版面id */<BR>/*
@a_intFatherID: 父贴ID，如果是新主题为0 */<BR>/* @a_strSubject: 标题 */<BR>/*
@a_strContent: 内容 */<BR>/* @a_intUserID: 发贴人ID */<BR>/*
@a_intFaceID: 表情ID */<BR>/* @a_strIP: 发贴人IP */<BR>/* */<BR>/* Use
table: bbs , forum , bbsuser */<BR>/* */<BR>/* Author:
bigeagle@163.net */<BR>/* */<BR>/* Date: 2000/2/13 */<BR>/*
*/<BR>/* History: */<BR>/*
*/<BR>/*************************************************************************/<BR>if
exists(select * from sysobjects where id =
object_id('up_PostTopic')) <BR>drop proc up_PostTopic<BR>go</P>
<P>create proc up_PostTopic <BR>@a_intForumID int ,
<BR>@a_intFatherID int ,<BR>@a_strSubject varchar(255)
,<BR>@a_strContent text ,<BR>@a_intUserID int ,<BR>@a_intFaceID
int ,<BR>@a_strIP varchar(255)<BR>as<BR>/*定义局部变量*/<BR>declare
@intRootID int --根id<BR>declare @dblOrderNum float(53)
--排序基数<BR>declare @intLayer int --层<BR>declare @dblNextOrderNum
float(53) --下一回贴的ordernum</P>
<P>/*判断有没有这个版面*/<BR>if not exists(select * from forum where id =
@a_intForumID)<BR>return(-1) </P>
<P>/*判断新贴子还是回应贴子*/<BR>if (@a_intFatherID = 0) --根贴<BR>begin
<BR>select @intRootID = isnull(max(id) , 0) + 1 from bbs<BR>select
@dblOrderNum = 9e+24<BR>select @intLayer = 1<BR>end<BR>else
--回贴<BR>begin<BR>select @intRootID = rootid , @intLayer = layer +
1 , @dblOrderNum = ordernum<BR>from bbs where id = @a_intFatherID
</P>
<P>/*如果没找到父贴则返回错误*/<BR>if (@@rowcount = 0) return
-1<BR><BR>/*计算ordernum*/<BR>select @dblNextOrderNum =
isnull(max(ordernum), 0) <BR>from bbs where ordernum <
@dblOrderNum and rootid=@intRootID<BR>select @dblOrderNum =
(@dblOrderNum + @dblNextOrderNum) / 2 <BR>end</P>
<P>/*由于对两个表操作，用事务*/<BR>Begin transaction<BR>/*插入贴子*/<BR>insert
into bbs(RootID , FatherID , Layer , OrderNum , UserID , ForumID ,
<BR>Subject , Content , FaceID , IP)<BR>values(@intRootID ,
@a_intFatherID , @intLayer , @dblOrderNum , <BR>@a_intUserID ,
@a_intForumID ,<BR>@a_strSubject , @a_strContent , @a_intFaceID ,
@a_strIP)<BR>/*判断是否成功*/<BR>if (@@error != 0) goto OnError</P>
<P>/*更新版面贴子数*/<BR>update forum set topiccount = topiccount + 1
where id = @a_intForumID<BR>if (@@error != 0) goto
OnError<BR><BR>/*更新用户分数*/<BR>update BBSUser set point = point + 1
where id = @a_intUserID<BR>if (@@error !=0) goto
OnError<BR><BR>/*执行*/<BR>commit transaction<BR>return(0)</P>
<P>/*错误处理*/<BR>OnError:<BR>rollback transaction<BR>return(-1)</P>
<P><BR>go <BR>select id from bbs where fatherid=0 order by rootid
desc, ordernum desc <BR>up_posttopic 1 , 12 , '哈哈哈，见笑了' , 'hello ,
world' , 1 , 1 , '203.93.95.10'</P>
<P>/*************************************************************************/<BR>/*
*/<BR>/* procedure : up_GetTopicList */<BR>/* */<BR>/*
Description: 贴子列表 */<BR>/* */<BR>/* Parameters: @a_intForumID :
版面id */<BR>/* @a_intPageNo: 页号 */<BR>/* @a_intPageSize:
每页显示数，以根贴为准 */<BR>/* */<BR>/* Use table: bbs , forum */<BR>/*
*/<BR>/* Author: bigeagle@163.net */<BR>/* */<BR>/* Date:
2000/2/14 */<BR>/* */<BR>/* History: */<BR>/*
*/<BR>/*************************************************************************/<BR>if
exists(select * from sysobjects where id =
object_id('up_GetTopicList'))<BR>drop proc
up_GetTopicList<BR>go</P>
<P>create proc up_GetTopicList <BR>@a_intForumID int ,
<BR>@a_intPageNo int ,<BR>@a_intPageSize int
<BR>as<BR>/*定义局部变量*/<BR>declare @intBeginID int<BR>declare
@intEndID int<BR>declare @intRootRecordCount int<BR>declare
@intPageCount int<BR>declare @intRowCount int<BR>/*关闭计数*/<BR>set
nocount on<BR><BR>/*检测是否有这个版面*/<BR>if not exists(select * from
forum where id = @a_intForumID)<BR>return
(-1)<BR><BR>/*求总共根贴数*/<BR>select @intRootRecordCount = count(*)
from bbs where fatherid=0 and forumid=@a_intForumID<BR>if
(@intRootRecordCount = 0) --如果没有贴子，则返回零<BR>return
0<BR><BR>/*判断页数是否正确*/<BR>if (@a_intPageNo - 1) * @a_intPageSize
> @intRootRecordCount<BR>return (-1)</P>
<P>/*求开始rootID*/<BR>set @intRowCount = (@a_intPageNo - 1) *
@a_intPageSize + 1<BR>/*限制条数*/<BR>set rowcount
@intRowCount<BR>select @intBeginID = rootid from bbs where
fatherid=0 and forumid=@a_intForumID <BR>order by id desc</P>
<P>/*结束rootID*/<BR>set @intRowCount = @a_intPageNo *
@a_intPageSize<BR>/*限制条数*/<BR>set rowcount @intRowCount<BR>select
@intEndID = rootid from bbs where fatherid=0 and
forumid=@a_intForumID <BR>order by id desc</P>
<P>/*恢复系统变量*/<BR>set rowcount 0<BR>set nocount off </P>
<P>select a.id , a.layer , a.forumid , a.subject , a.faceid ,
a.hits , a.time , a.UserID , a.fatherid , a.rootid ,<BR>'Bytes' =
datalength(a.content) , b.UserName , b.Email , b.HomePage ,
b.Signature , b.Point<BR>from bbs as a join BBSUser as b on
a.UserID = b.ID<BR>where Forumid=@a_intForumID and a.rootid
between @intEndID and @intBeginID<BR>order by a.rootid desc ,
a.ordernum desc <BR>return(@@rowcount)<BR>--select
@@rowcount<BR>go <BR>up_getTopiclist 3 , 1 , 20<BR>select * from
bbs where fatherid=0 order by id desc<BR>select * from
bbsuser<BR><BR><BR></P></BLOCKQUOTE></TD></TR>
<TR>
<TD class=p4 vAlign=top width="50%">
<BLOCKQUOTE>原作者：bigeagle<BR>来 源：chinaasp<BR>共有2683位读者阅读过此文<BR>【<A
href="http://bbs.aspsky.net/list.asp?boardid=1">发表评论</A>】
</BLOCKQUOTE></TD>
<TD class=p4 vAlign=top width="50%">
<P>
<LI><FONT color=#0772b1>上篇文章</FONT>：<A
href="http://www.aspsky.net/article/list.asp?id=2312">bbs的数据结构和存储过程（一）</A>
<BR>
<LI><FONT color=#0772b1>下篇文章</FONT>：<A
href="http://www.aspsky.net/article/list.asp?id=2314">bbs的数据结构和存储过程（三）</A>
</LI></TD></TR>
<TR>
<TD bgColor=#297dff class=p4 height=20 width="50%"><FONT
color=#ceffff> → 本周热门</FONT></TD>
<TD bgColor=#297dff class=p4 width="50%"><FONT color=#ceffff> →
相关文章</FONT></TD></TR>
<TR>
<TD bgColor=#586011 colSpan=2 height=1><SPACER type="block"
width="1"></TD></TR>
<TR>
<TD colSpan=2 height=7></TD></TR>
<TR>
<TD class=p4 vAlign=top width="50%">
<LI><A href="http://www.aspsky.net/article/list.asp?id=1510"
target=_top title="SQL Server 7.0 入门（一）">SQL Server 7.0
入门（...</A>[<FONT color=red>7239</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1540"
target=_top title=PHP4实际应用经验篇(1)>PHP4实际应用经验篇(1)</A>[<FONT
color=red>7135</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1536"
target=_top
title=无组件文件上传代码实例（支持多文件上传及文件和input域混合上传）>无组件文件上传代码实例（支持多文件上...</A>[<FONT
color=red>6029</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=2557"
target=_top title=树型结构在ASP中的简单解决>树型结构在ASP中的简单解决</A>[<FONT
color=red>5757</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1545"
target=_top title=PHP4实际应用经验篇(6)>PHP4实际应用经验篇(6)</A>[<FONT
color=red>5599</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=2563"
target=_top title=一个老个写的无组件上传>一个老个写的无组件上传</A>[<FONT
color=red>5014</FONT>]<BR>
<LI><A href="http://www.aspsky.net/article/list.asp?id=1542"
target=_top title=PHP4实际应用经验篇(3)>PHP4实际应用经验篇(3)</A>[<FONT
color=red>4731</FONT>]<BR></LI></TD>
<TD class=p4 vAlign=top width="50%">
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2655">金额阿拉伯数字转换为中文的存储过程</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2609">存储过程使用大全</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2537">利用sql的存储过程实现dos命令的asp程序</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2401">显示数据库中的存储过程</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2314">bbs的数据结构和存储过程（三）</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2313">bbs的数据结构和存储过程（二）</A><BR>
<LI><A
href="http://www.aspsky.net/article/list.asp?id=2312">bbs的数据结构和存储过程（一）</A><BR></LI></TD></TR>
<TR>
<TD colSpan=2 height=7></TD></TR></TBODY></TABLE>
<TD bgColor=#297dff width=1> </TD></TR></TBODY></TABLE>
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD bgColor=#297dff height=1><SPACER type="block"
width="1"></TD></TR></TBODY></TABLE>
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD align=middle height=30></TD></TR></TBODY></TABLE>
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD align=middle class=p2 width="100%">
<TABLE border=0 cellPadding=0 cellSpacing=0 width=755>
<TBODY>
<TR>
<TD align=middle class=p2 width="100%">
<P align=center><A
href="http://www.aspsky.net/produce/index.asp">客户服务</A> -- <A
href="http://www.aspsky.net/aspads.asp">广告合作</A> -- <A
href="http://www.aspsky.net/about.asp">关于本站</A> -- <A
href="http://www.aspsky.net/tell.asp">联系方法</A><BR><BR>动网先锋版权所有 <FONT
face=Verdana, size=1 Arial, Helvetica, sans-serif>Copyright ©
2000-2001 <B>AspSky<FONT color=#cc0000>.Net</FONT></B>, All Rights
Reserved .</FONT>
</P></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></CENTER></CENTER></BODY></HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -