表字段关联关系:
WCMSCHEDULE .OPTYPE=WCMOPER.OPERID
WCMDOCUMENT.DOCID=WCMCHNLDOC.DOCID
WCMDOCUMENT.DOCPUBURL =WCMCHNLDOC.DOCPUBURL
WCMDOCUMENT.DOCRELTIME =WCMCHNLDOC. DOCRELTIME
wcmappendix.APPDOCID=WCMDOCUMENT.DOCID
WCMDOCUMENT.DOCCHANNEL=WCMCHNLDOC.CHNLID=WCMCHANNEL.CHANNELID
WCMWEBSITE.SITEID=WCMDOCUMENT.SITEID =WCMCHANNEL.SITEID
待补充
1.删除引用
--1.删除所有引用
------1.1删除所有引用前,先进行备份
CREATE TABLE WCMCHNLDOCMODALBACK AS SELECT * FROM WCMCHNLDOC WHERE MODAL IN(2, -2, 3, -3);
------1.2在备份后,删除所有引用
DELETE FROM WCMCHNLDOC WHERE MODAL IN(2, -2, 3, -3);
--2.删除指定栏目(XXX,YYY)下的所有引用文档,其中XXX,YYY为栏目ID
------2.1删除引用前,先进行备份
CREATE TABLE WCMCHNLDOCMODALBACK AS SELECT * FROM WCMCHNLDOC WHERE MODAL IN(2, -2, 3, -3) AND CHNLID IN (XXX,-XXX,YYY,-YYY);
------2.2在备份后,删除指定栏目(XXX,YYY)下的所有引用文档
DELETE FROM WCMCHNLDOC WHERE MODAL IN(2, -2, 3, -3) AND CHNLID IN (XXX,-XXX,YYY,-YYY);
2.删除指定的站点
--删除站点XX,其中XX为站点的id
--删除附件
delete from WCMAppendix where exists(
select wcmdocument.DocId from wcmdocument
where DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx))
and wcmdocument.DocId=WCMAppendix.AppDocId);
commit;
--删除相关文档
ALTER TABLE WCMRelation NOLOGGING;
delete from WCMRelation where exists(
select wcmdocument.DocId from wcmdocument
where DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx))
and (WCMRelation.DocId=WCMDocument.DocId or WCMRelation.RelDocId=WCMDocument.DocId));
commit;
--删除文档的关键词
ALTER TABLE WCMDocKeyword NOLOGGING;
delete from WCMDocKeyword where exists(
select wcmdocument.DocId from wcmdocument
where DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx))
and WCMDocKeyword.DocId=WCMDocument.DocId);
commit;
--删除文档工作流流转信息(WCMFlowDoc,WCMFlowDocBak)
ALTER TABLE WCMFlowDoc NOLOGGING;
delete WCMFlowDoc where
exists(
select DocId from WCMDocument where WCMFlowDoc.ObjId = WCMDocument.DocId
and DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx)) );
commit;
ALTER TABLE WCMFlowDocBak NOLOGGING;
delete WCMFlowDocBak where
exists(
select DocId from WCMDocument where WCMFlowDocBak.ObjId = WCMDocument.DocId
and DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx)) );
commit;
--删除文档备份信息(WCMDOCBAK)
ALTER TABLE WCMDOCBAK NOLOGGING;
delete WCMDOCBAK where
exists(
select DocId from WCMDocument where WCMDOCBAK.DocId = WCMDocument.DocId
and DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx)));
commit;
--删除文档实体
ALTER TABLE wcmdocument NOLOGGING;
delete from wcmdocument where DocChannel in(select CHANNELID from WCMWEBSITE where SITEID in (xx));
commit;
--删除文档引用
ALTER TABLE wcmchnldoc NOLOGGING;
delete from WCMCHNLDOC where CHNLID in(select CHANNELID from WCMWEBSITE where SITEID in (xx));
commit;
--删除栏目
ALTER TABLE wcmchannel NOLOGGING;
delete from wcmchannel where siteid in(xx);
commit;
--删除站点
ALTER TABLE wcmwebsite NOLOGGING;
delete from wcmwebsite where siteid in(XX);
commit;
3.WCMChnlDoc,WCMDocument表SiteId为空的历史数据处理
siteid为空可能是历史数据导致,因为后面在添加一篇文档的时候,都会给siteid赋值;
关于历史的siteid为空的问题,请执行以下SQL:
WCMChnlDoc表siteid为空的处理:
update wcmchnldoc set siteid = (select siteid from wcmchannel where wcmchnldoc.chnlid= wcmchannel.channelid)
where exists(select siteid from wcmchannel where wcmchnldoc.chnlid= wcmchannel.channelid);
WCMDOCUMENT表siteid为空的处理:
update WCMDOCUMENT set SITEID=(select SITEID from WCMCHANNEL where channelid = WCMDOCUMENT.DOCCHANNEL)
where exists(select siteid from wcmchannel where WCMDOCUMENT.DOCCHANNEL= wcmchannel.channelid);
4.对指定的表进行归档
4.1按照年份生成归档表
说明:归档表按照年份作为表的后缀名称,不同年份将产生不同的归档表,如:WCMDocument2012;同时归档表的前缀名称也可以指定,如:MyWCMDocument2012
添加了一个对Oracle数据库中的表进行归档的存储过程
--对指定的表“STABLENAME”进行