IDC资讯中心
/*************************************************************************/
/* */
/* 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
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: 发贴人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 @dblordernum 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.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
