TRSWCM数据库常见操作

表字段关联关系:


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”进行

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值