📄 bbs的数据结构和存储过程（三）.htm
字号:
<P>create proc up_GetPostedTopicList <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 posted=1 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
posted=1 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 posted=1 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 posted=1 and 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>select id , rootid , fatherid , forumid ,
posted from bbs<BR>up_getpostedtopiclist 3 ,1 ,
20<BR>/*************************************************************************/<BR>/*
*/<BR>/* procedure : up_GetTopic */<BR>/* */<BR>/* Description:
取贴子 */<BR>/* */<BR>/* Parameters: @a_intTopicID : 贴子id */<BR>/*
*/<BR>/* Use table: bbs */<BR>/* */<BR>/* Author: bigeagle@163.net
*/<BR>/* */<BR>/* Date: 2000/2/16 */<BR>/* */<BR>/* History:
*/<BR>/*
*/<BR>/*************************************************************************/<BR>if
exists(select * from sysobjects where id =
object_id('up_GetTopic'))<BR>drop proc up_GetTopic<BR>go</P>
<P>create proc up_GetTopic @a_intTopicID
int<BR>as<BR>/*如果没有这贴子*/<BR>if not exists (select * from bbs where
id = @a_intTopicID)<BR>return (-1)<BR><BR>/*更新该贴的点击数*/<BR>update
bbs set hits = hits + 1 where id = @a_intTopicID</P>
<P>select a.* , 'Bytes' = datalength(a.content) ,<BR>b.UserName ,
b.Email , b.Homepage , b.point , b.Signature<BR>from bbs as a join
BBSUser as b on a.UserID = b.id <BR>where a.id =
@a_intTopicID<BR>go</P>
<P>up_getTopic 11</P>
<P>/*************************************************************************/<BR>/*
*/<BR>/* procedure : up_DeleTopic */<BR>/* */<BR>/* Description:
删除贴子及子贴，更新发贴人信息 */<BR>/* */<BR>/* Parameters: @a_intTopicID : 贴子id
*/<BR>/* */<BR>/* Use table: bbs */<BR>/* */<BR>/* Author:
bigeagle@163.net */<BR>/* */<BR>/* Date: 2000/2/24 */<BR>/*
*/<BR>/* History: */<BR>/*
*/<BR>/*************************************************************************/</P>
<P>if exists(select * from sysobjects where id =
object_id('up_DeleTopic'))<BR>drop proc up_DeleTopic<BR>go</P>
<P>create proc up_DeleTopic @a_intTopicID int <BR>as</P>
<P>/*定义局部变量*/<BR>declare @intRootID int<BR>declare @intLayer
int<BR>declare @floatOrderNum float(53)<BR>declare
@floatNextOrderNum float(53) <BR>declare @intCounts int<BR>declare
@intForumID int<BR><BR>/*取消计数*/<BR>set nocount on</P>
<P>/*首先查找这个贴子的rootid和ordernum，没有则返回*/<BR>select @intRootID =
RootID , <BR>@floatOrderNum = OrderNum ,<BR>@intLayer = layer
,<BR>@intForumID = forumid<BR>from bbs where id = @a_intTopicID
<BR>if @@rowcount = 0 <BR>return (-1) </P>
<P>/*取下一个同层贴子的ordernum*/<BR>select @FloatNextOrderNum =
isnull(max(ordernum) , 0)<BR>from bbs<BR>where RootID=@intRootID
<BR>and layer=@intLayer and ordernum < @floatOrderNum</P>
<P>/*多表操作，用事务*/<BR>begin transaction<BR><BR>/*首先删贴*/<BR><BR>delete
from bbs <BR>where rootid=@intRootID and ordernum >
@floatNextOrderNum<BR>and ordernum <= @floatOrderNum <BR>select
@intCounts = @@rowcount<BR>if (@@error != 0)<BR>goto Error </P>
<P><BR><BR>/*论坛贴子数减少*/<BR>update forum set topiccount = topiccount
- @intCounts where id=@intForumID<BR>if (@@error != 0)<BR>goto
Error</P>
<P>/*完成事务，返回*/<BR>commit transaction<BR>set nocount
off<BR>return(0)</P>
<P>Error:<BR>rollback transaction<BR>set nocount off<BR>return
(-1)<BR>go</P>
<P>select forumid from bbs<BR>update bbs set forumid=4</P>
<P>/*************************************************************************/<BR>/*
*/<BR>/* procedure : up_GetUserInfo */<BR>/* */<BR>/* Description:
取得发贴人信息 */<BR>/* */<BR>/* Parameters: @a_strUserName : 用户笔名
*/<BR>/* */<BR>/* Use table: bbsuser */<BR>/* */<BR>/* Author:
bigeagle@163.net */<BR>/* */<BR>/* Date: 2000/4/16 */<BR>/*
*/<BR>/* History: */<BR>/*
*/<BR>/*************************************************************************/<BR>if
exists(select * from sysobjects where id =
object_id('up_GetUserInfo'))<BR>drop proc up_GetUserInfo<BR>go</P>
<P>create proc up_GetUserInfo @a_strUserName
varchar(20)<BR>as<BR>declare @m_intOrder int --排名<BR>declare
@m_intPoint int --积分</P>
<P>set nocount on</P>
<P>/*如果没有找到该用户，则返回-1*/<BR>select @m_intPoint = point from bbsuser
where username=@a_strUserName<BR>if(@@rowcount =
0)<BR>return(-1)</P>
<P>/*求排名*/<BR>select @m_intOrder = count(*) + 1 from bbsuser where
point > @m_intPoint</P>
<P>select * , 'order' = @m_intOrder from bbsuser where
username=@a_strUserName</P>
<P>set nocount off<BR>go<BR>up_getuserinfo '廖家远'</P>
<P>/*************************************************************************/<BR>/*
*/<BR>/* procedure : up_PostedTopic */<BR>/* */<BR>/* Description:
将贴子转入精华区 */<BR>/* */<BR>/* Parameters: @a_intTopicID 贴子id */<BR>/*
*/<BR>/* Use table: bbs, postedtopic */<BR>/* */<BR>/* Author:
bigeagle@163.net */<BR>/* */<BR>/* Date: 2000/4/17 */<BR>/*
*/<BR>/* History: */<BR>/*
*/<BR>/*************************************************************************/</P>
<P>if exists(select * from sysobjects where id=
object_id('up_postedtopic'))<BR>drop proc up_postedtopic<BR>go</P>
<P>create proc up_PostedTopic @a_intTopicID
int<BR>as<BR>/*定义局部变量*/<BR>declare @m_intUserID int --发贴人ID</P>
<P>/*查找是否有这个贴子*/<BR>select @m_intUserID = userid from bbs where id
= @a_intTopicID<BR>if(@@rowcount != 1)<BR>return -1</P>
<P>/*因为对两个表操作所以用事务*/<BR>begin transaction<BR>update bbs set posted
= 1 where id = @a_intTopicID<BR>if(@@error <> 0)<BR>goto
Error<BR>update bbsuser set point = point + 3 where id =
@m_intUserID<BR>if(@@error <> 0) <BR>goto Error<BR>Commit
transaction<BR>return (0)<BR>Error:<BR>rollback
transaction<BR>go<BR><BR><BR></P></BLOCKQUOTE></TD></TR>
<TR>
<TD class=p4 vAlign=top width="50%">
<BLOCKQUOTE>原作者：bigeagle<BR>来 源：chinaasp<BR>共有2639位读者阅读过此文<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=2313">bbs的数据结构和存储过程（二）</A>
<BR>
<LI><FONT color=#0772b1>下篇文章</FONT>：<A
href="http://www.aspsky.net/article/list.asp?id=2315">W3
Jmail中文使用说明</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 + -