实现转贴功能的store procedure

数据库结构
board表--论坛板块表
theme表--论坛主题表
message表--帖子主表
reply表--回帖表

其中
board表记录了messagetable和replytable的表名（说明这两个表是可变的）。
theme表记录了msgnumber,replynumber，即该主题下的帖子数量和回帖数量，newmsgid最新帖id.themeid是主题的编号，它对应一个板块区号(id)，通过themeid可以查找到对应的板块id。
message和reply是动态从board里取出的表名，记录了帖子属于那个主题（themeid）和那个板块(id）。

转贴需求
1 转贴主要的操作是对message和reply表的主题号进行更改，其他的是辅助操作，比如板块会随主体区的改变而改变。
2 theme表的数据可能也会改变，当转变前后板块的message,reply表不同时，就会发生这种情况。
3 board表只用来做查询，转贴操作不对它改动。

具体操作
经过分析，我们需要传进来的参数有三个：
帖子号msgid,帖子原属主题区号srcid,帖子将转向的主题区号thmid

create or replace package body erictest is

procedure chgmeg(
msgid number,
srcid number,
thmid number
)
is
m_msgid varchar2(20);
myid number;--板块区号
rplycount number;--回帖数
messageTable1 varchar2(40);--主帖原表
replyTable1 varchar2(40);--回帖原表
messageTable2 varchar2(40);--主帖新表
replyTable2 varchar2(40);--回帖新表
mynewmsgid number;--最新帖子号
mysql varchar2(1000);--中间变量,用于sql操作
begin
m_msgid:=to_char(msgid);
messageTable1:=getMessageTable(srcid);
replyTable1:=getReplyTable(srcid);
messageTable2:=getMessageTable(thmid);
replyTable2:=getReplyTable(thmid);
select id into myid from home_forumtheme where themeid=thmid;--主题对应的板块号
select count(*) into rplycount from home_forumreply where replyto=msgid;--该帖子有多少条回复

--[[帖子主表]]
if (messageTable1=messageTable2) then--如果原板块和现板块共用一张表,则更新
mysql:='update '||messageTable1||' set themeid='||to_char(thmid)||',id='||to_char(myid)||' where sequenceid='||m_msgid;
EXECUTE IMMEDIATE mysql;
else--如果原板块和现板块用不同的表,则插入新表,删除原表;还要计算主题表里的相关数据
mysql:='insert into '||messageTable2||'(themeid,id) value('||to_char(thmid)||','||to_char(myid)||') where sequenceid='||m_msgid;
EXECUTE IMMEDIATE mysql;
mysql:='delete * from '||messageTable1||' where sequenceid='||m_msgid||';';
EXECUTE IMMEDIATE mysql;
mysql:='select * from (select newmsgid from home_forumtheme t where themeid='||to_char(thmid)||' order by posttime desc) where rownum=1';
EXECUTE IMMEDIATE mysql into mynewmsgid;--取得主题的最新帖子号
if(mynewmsgid=msgid) then --如果操作的帖子是最新的,则取次新的
mysql:='select * from (select newmsgid from home_forumtheme t where themeid='||to_char(thmid)||' order by posttime desc) where rownum=2';
EXECUTE IMMEDIATE mysql into mynewmsgid;
end if;
update home_forumtheme set msgnumber=msgnumber-1,newmsgid=mynewmsgid where srcid=thmid;
update home_forumtheme set msgnumber=msgnumber+1,newmsgid=mynewmsgid where themeid=thmid;
end if;

--[[回帖表]]
if (replyTable1=replyTable2) then--如果原板块和现板块共用一张表,则更新
mysql:='update '||replyTable1||' set themeid='||to_char(thmid)||',id='||to_char(myid)||' where replyto='||m_msgid;
EXECUTE IMMEDIATE mysql;
else
mysql:='insert into '||replyTable2||'(themeid,id) value('||to_char(thmid)||','||to_char(myid)||') where sequenceid='||m_msgid;
EXECUTE IMMEDIATE mysql;
mysql:='delete * from '||replyTable1||' where sequenceid='||m_msgid;
EXECUTE IMMEDIATE mysql;
update home_forumtheme set replynumber=replynumber-rplycount where srcid=thmid;
update home_forumtheme set replynumber=replynumber+rplycount where themeid=thmid;
end if;
end;

-- 取得当前主题主贴表
FUNCTION getMessageTable(p_themeId NUMBER) RETURN VARCHAR2
IS
tableName VARCHAR2(40);
BEGIN
 SELECT a.messageTable INTO tablename FROM home_forumboard a,home_forumtheme b
 WHERE b.themeid=p_themeId AND a.id=b.id;
 RETURN tablename;
END;
-- 取得当前主题回复表
FUNCTION getReplyTable(p_themeId NUMBER) RETURN VARCHAR2
IS
tablename VARCHAR2(40);
BEGIN
 SELECT a.replytable INTO tablename FROM home_forumboard a,home_forumtheme b
 WHERE b.themeid=p_themeId AND a.id=b.id;
 RETURN tablename;
END;
end erictest;