背景介绍:
邓白氏是一家做商业信息服务的公司,其中一项业务是做企业的资信调查服务,说的明白一点就是监测企业的形象,比如耐克公司就是他们的客户,耐克公司通过这个系统,了解有没有人在线上、线下发表有损耐克形象的文章。除此以外,还有其他功能,比如耐克公司也监测他的竞争对手,通过此系统查看竞争对手的动态,比如线上或线下的投放广告量等。
数据来源:
为了给客户提供大量的线上线下的资讯,邓白氏通过信息采集团队,在线上和线下获取了大量信息,线上信息采集是通过爬虫技术抓取网络信息,再进行筛选。线下信息采集是通过在北京昌平的一个基地,由人工方式采集主要杂志、报纸的资讯信息。所以信息来源分为【网络】和【平面】,有效的信息量,一天大约十几万条。
数据分发:
这个系统为一百多个企业提供服务,因为数据量大、业务复杂,所以系统设计为每一个企业建立了一个数据表。当有新的数据被采集到中心库后,需要将企业需要的数据分发到各自的企业表中,这个数据分发,是使用两个存储过程完成的。一个存储过程负责网络数据分发,一个存储过程负责平面数据分发。
在Oracle数据库中,我们使用job完成定时执行功能,对网络数据,要求每15分钟分发一次,对于平面数据要求每60分钟分发一次。创建job的sql脚本如下:
declare
job number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => job, /*自动生成JOB_ID*/
WHAT => 'eimsweb_web_new;', /*需要执行的存储过程名称或SQL语句*/
NEXT_DATE => sysdate+3/(24*60), /*初次执行时间-下一个3分钟*/
INTERVAL => 'trunc(sysdate,''mi'')+15/(24*60)' /*每隔15分钟执行一次*/
);
commit;
end;
存储过程:
分发网络数据的存储过程如下:
CREATE OR REPLACE PROCEDURE eimsweb_web_new IS
--declare
--startdate VARCHAR2(60);
--enddate VARCHAR2(20);
sql_stmt VARCHAR2(4000);
sql_stmt1 VARCHAR2(4000);
sql_stmt2 VARCHAR2(4000);
sql_stmt3 VARCHAR2(4000);
sql_stmt4 VARCHAR2(4000);
sql_stmt5 VARCHAR2(4000);
sql_stmt6 VARCHAR2(4000);
sql_stmt7 VARCHAR2(4000);
sql_stmt8 VARCHAR2(4000);
sql_stmt9 VARCHAR2(4000);
sourcetype NUMBER;
eid NUMBER;
etype NUMBER;
sqlstring VARCHAR2(4000);
i NUMBER;
num NUMBER;
TYPE v_cur1 IS REF CURSOR;
v_cur v_cur1;
v_rst co_mod@eimsfore%ROWTYPE;
v_sql VARCHAR2(4000);
v_insertsql VARCHAR2(4000);
midsqlstr VARCHAR2(4000);
cosqlstr VARCHAR2(4000);
corstrsql VARCHAR2(4000);
--7月3日 start
presql VARCHAR2(4000);
--得到企业条件表中订阅网络新闻的企业的条件
CURSOR c_enterprise IS
SELECT ID,
eid,
ename,
etype,
etranslate,
edatasource,
esql,
esql1,
esql2,
esql3,
esql4,
esql5,
esql6,
esql7,
esql8,
esql9
FROM eimsenterprise
where edatasource = 5;
--and eid =4856;
--在企业关系表
CURSOR c_replace(enterid NUMBER, datasource NUMBER) IS
SELECT *
FROM eimsreplace
WHERE eid = enterid
AND rdatasource = datasource;
BEGIN
sqlstring := '';
--7月3日 start
--将co_web_edited表中要处理的数据先保存到准备表中,中间表到准备表中取数据;
delete from eimsweb_per;
commit;
insert into eimsweb_per (select * from CO_WEB_EDITED where istate = 0 and mtrq > to_date('2008-08-22', 'yyyy-mm-dd'));
commit;
--and lrrq > to_date('2008-08-23', 'yyyy-mm-dd')
--2008-8-22 信息插入到企业时,修改录入时间为当时系统的时间
--update eimsweb_per set lrrq=sysdate;
--7月3日 end
--对于每一个数据源和相应的企业
FOR r_enterprise IN c_enterprise LOOP
--打印企业信息测试使用
--DBMS_OUTPUT.PUT_LINE('Enterprise:' || r_enterprise.id || ',' || r_enterprise.eid || ',' || r_enterprise.ename || ',' || r_enterprise.etype || ',' || r_enterprise.etranslate || ',' || r_enterprise.edatasource || ',' || r_enterprise.esql);
DBMS_OUTPUT.PUT_LINE('Enterprise:' || r_enterprise.id || ',' ||
r_enterprise.eid);
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into presql from dual;
DBMS_OUTPUT.PUT_LINE('start:' || presql);
insert into log_fu(eid,istate) values(r_enterprise.eid,'begin');
commit;
--获取数据源类型和对应的sql条件
sourcetype := r_enterprise.edatasource;
sql_stmt := r_enterprise.esql;
sql_stmt1 := r_enterprise.esql1;
sql_stmt2 := r_enterprise.esql2;
sql_stmt3 := r_enterprise.esql3;
sql_stmt4 := r_enterprise.esql4;
sql_stmt5 := r_enterprise.esql5;
sql_stmt6 := r_enterprise.esql6;
sql_stmt7 := r_enterprise.esql7;
sql_stmt8 := r_enterprise.esql8;
sql_stmt9 := r_enterprise.esql9;
eid := r_enterprise.eid;
--DBMS_OUTPUT.put_line(eid);
etype := r_enterprise.etype;
sqlstring := '';
--dbms_output.put_line(sql_stmt);
IF (sql_stmt1 IS NOT NULL) THEN
sql_stmt := sql_stmt || sql_stmt1;
END IF;
IF (sql_stmt2 IS NOT NULL) THEN
sql_stmt := sql_stmt || sql_stmt2;
END IF;
if (sql_stmt3 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt3;
end if;
if (sql_stmt4 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt4;
end if;
if (sql_stmt5 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt5;
end if;
if (sql_stmt6 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt6;
end if;
if (sql_stmt7 IS NOT NULL) then
sql_stmt := sql_stmt || Sql_stmt7;
end if;
if (sql_stmt8 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt8;
end if;
if (sql_stmt9 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt9;
end if;
--7月3日
--当查询条件为1=0,2=1时,不需要进行下面的循环,故跳出,进行下一个企业的条件
IF (sql_stmt IS NOT NULL and sql_stmt != '1=0' and sql_stmt != '2=1') THEN
sqlstring := sqlstring || ' and ' || sql_stmt || ')';
--DBMS_OUTPUT.PUT_LINE('sqlString============>' || sqlString);
--如果是常规企业
IF (etype = 1) THEN
--如果是网络新闻
IF (sourcetype = 5) THEN
--=========================new add======2008-6-25======================
--删除mid表中的记录
--DBMS_OUTPUT.put_line('delete from EIMSWEB_MID');
delete from EIMSWEB_MID;
commit;
--========================================================
--execute immediate 'commit';
--将需要处理的数据放入中间表
--DBMS_OUTPUT.PUT_LINE('insert into EIMSWEB_MID (select a.*,0,'||to_char(eid)||' from CO_WEB_EDITED a where a.istate=0 and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')>'''||startDate||''' and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')<='''||endDate||''' '||sqlString);
midsqlstr := 'insert into EIMSWEB_MID (select RGGT_ID,MTMC,CS,MTRQ,' ||
'PAGE,BMMC,AUTHOR,ZKMC,WZXSFL,FONTSIZE,ZMJ,ZZS,MTBZ_1,MTBZ_2,WZZT, ' ||
'ZTXZ,PPMC,CPMC,GGXH,WZNRFL,TITLE,GG_ID,CFID,BRIEF,XXTS,BZ,WZH,WZ, ' ||
'YMGG,SJ,ZYMC,PIC_ID,KD,GD,LRRQ,KGB,SSQ,INFO_1,DQ,INFO_2,BMGG,INFO_3, ' ||
'GGZMC,INFO_SIGN,GGZXZ,ZFY,XQS,CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5, ' ||
'substr(CONTENT,1,1800),substr(CONTENT,1800,1800),substr(CONTENT,3600,1800),substr(CONTENT,5400,1800), ' ||
'substr(CONTENT,7200,1800),substr(CONTENT,9000,1800),substr(CONTENT,10800,1800), ' ||
'substr(CONTENT,12600,1800),substr(CONTENT,14400,1800),substr(CONTENT,16200,1800), ' ||
'KEYW,SENDTIME,ACTIONSIGN, ' || 'ISTATE,' || ' 0,' ||
TO_CHAR(eid) ||
--7月3日 begin
' from eimsweb_per where 1=1 ' || sqlstring;
--7月3日 end
--DBMS_OUTPUT.PUT_LINE('midsqlstr');
EXECUTE IMMEDIATE midsqlstr;
commit;
--判断eimsweb_mid是否有数据
select count(*) into num from EIMSWEB_MID;
if(num>0)then
--如果是删除或修改:则将相关的信息先从摘要表和企业表里删除;
--DBMS_OUTPUT.PUT_LINE('delete from co'||to_char(eid)||'_smy@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || TO_CHAR(eid) ||
'_smy@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_MID where (actionsign=2 or actionsign=1))';
commit;
--DBMS_OUTPUT.PUT_LINE( 'delete from co'||to_char(eid)||'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || TO_CHAR(eid) ||
'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_MID where (actionsign=2 or actionsign=1))';
commit;
--***************************7月2日 begin
--不删除中间id表中的不处理的内容
--DBMS_OUTPUT.put_line('delete from EIMSWEB_MID where actionsign=2');
--delete from EIMSWEB_MID where actionsign = 2;
--commit;
--***************************7月2日 end
--因为在常规企业的执行过程中,先进行企业表的更新(此过程是只更新一个企业表),再进行企业模块表的更新(此过程是循环每一个表更新)。
--如果在此处将中间表中actionsign = 2的数据删除,那么在后面的"企业模块表的更新"的过程中,将无法找到要删除的数据。
--解决办法:不删除中间表中actionsign = 2的数据,在把中间表的数据拷贝到企业表或企业模块表的过程中,添加条件,达到不拷贝ctionsign = 2数据的目的。
--对于需要增加和修改的记录,按照对照关系查找并替换相关的字段
FOR r_replace IN c_replace(eid, sourcetype) LOOP
--DBMS_OUTPUT.put_line('REPLACE:' || r_replace.rsql);
IF (r_replace.rsql IS NOT NULL) THEN
-- DBMS_OUTPUT.put_line('REPLACE:' || r_replace.id);
EXECUTE IMMEDIATE 'UPDATE EIMSWEB_MID ' || r_replace.rsql;
commit;
END IF;
END LOOP;
commit;
--将做过对照关系处理的记录插入coxxx_data表,
cosqlstr := 'insert into co' || TO_CHAR(eid) || '_data@eimsfore ' ||
'(RGGT_ID,MTMC,CS,MTRQ,PAGE,BMMC,AUTHOR,ZKMC,WZXSFL,FONTSIZE,ZMJ,ZZS, ' ||
'MTBZ_1,MTBZ_2,WZZT,ZTXZ,PPMC,CPMC,GGXH,WZNRFL,TITLE,GG_ID,CFID, ' ||
'BRIEF,XXTS,BZ,WZH,WZ,YMGG,SJ,ZYMC,PIC_ID,KD,GD,LRRQ,KGB,SSQ, ' ||
'INFO_1,DQ,INFO_2,BMGG,INFO_3,GGZMC,INFO_SIGN,GGZXZ,ZFY,XQS, ' ||
'CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5) ' ||
'(select a.RGGT_ID,a.MTMC,b.CS,a.MTRQ,a.PAGE,a.BMMC,a.AUTHOR,'''',' ||
'a.WZXSFL,a.FONTSIZE,a.ZMJ,a.ZZS,b.two_class,a.mtbz_2,a.WZZT,' ||
'a.ZTXZ,a.PPMC,a.CPMC,a.GGXH,a.WZNRFL,a.TITLE,a.GG_ID,a.CFID,' ||
'a.BRIEF,a.XXTS,a.bz,a.WZH,a.WZ,a.YMGG,' ||
'a.SJ,a.ZYMC,a.PIC_ID,a.KD,a.GD,a.LRRQ,a.KGB,b.ssq,' ||
'b.FXL, b.dq,a.INFO_2,a.BMGG,a.INFO_3,'''',''网络新闻'',' ||
''''','''',to_char(a.MTRQ-1,''D''),' ||
'a.CPJG,a.LRRY,a.JCZB1,a.JCZB2,a.JCZB3,a.JCZB4,a.JCZB5 ' ||
'from EIMSWEB_MID a,EIMSNEWMTJBZLK b ' ||
--7月2日
--在如下sql语句中添加了"a.actionsign!=2"。
--原因:不拷贝已经删除的数据
'where a.actionsign!=2 and b.eid(+)=' || TO_CHAR(eid) ||
' and a.MTMC=b.mtmc(+) and not exists (select null from co' ||
TO_CHAR(eid) || '_data@eimsfore c where c.info_sign=' || '''网络新闻''' || ' and a.rggt_id = c.rggt_id ))';
--DBMS_OUTPUT.PUT_LINE(cosqlstr);
--DBMS_OUTPUT.PUT_LINE('insert into co' || TO_CHAR(eid) || '_data@eimsfore ' || '(RGGT_ID,MTMC,CS,MTRQ,PAGE,BMMC,AUTHOR,ZKMC,WZXSFL,FONTSIZE,ZMJ,ZZS, ' || 'MTBZ_1,MTBZ_2,WZZT,ZTXZ,PPMC,CPMC,GGXH,WZNRFL,TITLE,GG_ID,CFID, ');
--DBMS_OUTPUT.PUT_LINE( 'BRIEF,XXTS,BZ,WZH,WZ,YMGG,SJ,ZYMC,PIC_ID,KD,GD,LRRQ,KGB,SSQ, ' || 'INFO_1,DQ,INFO_2,BMGG,INFO_3,GGZMC,INFO_SIGN,GGZXZ,ZFY,XQS, ' || 'CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5) ' );
--DBMS_OUTPUT.PUT_LINE('(select a.RGGT_ID,a.MTMC,b.CS,a.MTRQ,a.PAGE,a.BMMC,a.AUTHOR,'''',' || 'a.WZXSFL,a.FONTSIZE,a.ZMJ,a.ZZS,b.two_class,a.mtbz_2,a.WZZT,' || 'a.ZTXZ,a.PPMC,a.CPMC,a.GGXH,a.WZNRFL,a.TITLE,a.GG_ID,a.CFID,' || 'a.BRIEF,a.XXTS,b.bkzzpd,a.WZH,a.WZ,a.YMGG,' );
--DBMS_OUTPUT.PUT_LINE('a.SJ,a.ZYMC,a.PIC_ID,a.KD,a.GD,a.LRRQ,a.KGB,b.ssq,' || 'b.FXL, b.dq,a.INFO_2,a.BMGG,a.INFO_3,'''',''网络新闻'',' || ''''','''',to_char(a.MTRQ-1,''D''),' || 'a.CPJG,a.LRRY,a.JCZB1,a.JCZB2,a.JCZB3,a.JCZB4,a.JCZB5 ' || 'from EIMSWEB_MID a,EIMSNEWMTJBZLK b ' );
--DBMS_OUTPUT.PUT_LINE( 'where a.actionsign!=2 and b.eid(+)=' || TO_CHAR(eid) || ' and a.MTMC=b.mtmc(+) and not exists (select null from co' || TO_CHAR(eid) || '_data@eimsfore c where c.info_sign=' || '''网络新闻''' || ' and a.rggt_id = c.rggt_id ))');
EXECUTE IMMEDIATE cosqlstr;
commit;
--splitinfonew@eimsfore(eid);
--将做过对照关系处理的记录插入coXXX——smy表
--DBMS_OUTPUT.PUT_LINE('insert into co'||to_char(eid)||'_smy@eimsfore '
--||'(select RGGT_ID,SMY1,SMY2,SMY3,SMY4,SMY5,SMY6,SMY7,SMY8,SMY9,SMY0 from EIMSWEB_MID)');
--7月2日
--在如下sql语句中添加了"a.actionsign!=2 and "。
--原因:不拷贝已经删除的数据
corstrsql := 'insert into co' || TO_CHAR(eid) || '_smy@eimsfore ' ||
'(select RGGT_ID,SMY1,SMY2,SMY3,SMY4,SMY5,SMY6,SMY7,SMY8,SMY9,SMY0 from EIMSWEB_MID a where a.actionsign!=2 and not exists(select null from co' ||
TO_CHAR(eid) ||
'_smy@eimsfore b where a.RGGT_ID=b.RGGT_ID))';
-- DBMS_OUTPUT.put_line(corstrsql);
EXECUTE IMMEDIATE corstrsql;
commit;
--循环每个企业的模块信息表
--select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into presql from dual;
--DBMS_OUTPUT.PUT_LINE('mol--start:' || presql);
-- splitinfonew@eimsfore(eid);
v_sql := 'select * from co_mod@eimsfore where trim(MOD_TYPE)=''1'' and co_id=' || eid;
OPEN v_cur FOR v_sql;
i := 0;
LOOP
FETCH v_cur
INTO v_rst;
EXIT WHEN v_cur%NOTFOUND;
--7月2日
--如果是删除或修改:则将相关的信息 先从企业模块表里删除;
--DBMS_OUTPUT.PUT_LINE( 'delete from co'|| eid || '_' || v_rst.mod_id ||'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || eid || '_' ||
v_rst.mod_id ||
'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_MID where (actionsign=2 or actionsign=1))';
commit;
--将信息插入到企业模块表中
--7月2日
--添加判断mod_sql字段是否为空,如果是空,则不执行
--在执行语句中添加 where actionsign!=2
if v_rst.mod_sql || 'a' != 'a' then
--DBMS_OUTPUT.PUT_LINE('模块的sql:' || v_rst.mod_sql || ',' || v_rst.mod_id);
DBMS_OUTPUT.PUT_LINE('molsql:' || v_rst.mod_id);
v_insertsql := 'insert into CO' || eid || '_' || v_rst.mod_id ||
'_data@eimsfore ' ||
'(RGGT_ID,MTMC,CS,MTRQ,PAGE,BMMC,AUTHOR,ZKMC,WZXSFL,FONTSIZE,ZMJ,ZZS, ' ||
'MTBZ_1,MTBZ_2,WZZT,ZTXZ,PPMC,CPMC,GGXH,WZNRFL,TITLE,GG_ID,CFID, ' ||
'BRIEF,XXTS,BZ,WZH,WZ,YMGG,SJ,ZYMC,PIC_ID,KD,GD,LRRQ,KGB,SSQ, ' ||
'INFO_1,DQ,INFO_2,BMGG,INFO_3,GGZMC,INFO_SIGN,GGZXZ,ZFY,XQS, ' ||
'CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5) ' ||
'(select a.RGGT_ID,a.MTMC,b.CS,a.MTRQ,a.PAGE,a.BMMC,a.AUTHOR,'''',' ||
'a.WZXSFL,a.FONTSIZE,a.ZMJ,a.ZZS,b.two_class,a.mtbz_2,a.WZZT,' ||
'a.ZTXZ,a.PPMC,a.CPMC,a.GGXH,a.WZNRFL,a.TITLE,a.GG_ID,a.CFID,' ||
'a.BRIEF,a.XXTS,a.bz,a.WZH,a.WZ,a.YMGG,' ||
'a.SJ,a.ZYMC,a.PIC_ID,a.KD,a.GD,a.LRRQ,a.KGB,b.ssq,' ||
'b.FXL, b.dq,a.INFO_2,a.BMGG,a.INFO_3,'''',a.INFO_SIGN,' ||
''''','''',to_char(a.MTRQ-1,''D''),' ||
'a.CPJG,a.LRRY,a.JCZB1,a.JCZB2,a.JCZB3,a.JCZB4,a.JCZB5 ' ||
'from (select * from EIMSWEB_MID where '|| v_rst.mod_sql || ' ) a,EIMSNEWMTJBZLK b ' ||
--7月2日
--在如下sql语句中添加了"a.actionsign!=2"
--原因:不拷贝已经删除的数据
'where a.actionsign!=2 and b.eid(+)=' ||
TO_CHAR(eid) ||
' and a.MTMC=b.mtmc(+) and not exists (select null from CO' || eid || '_' ||
v_rst.mod_id ||
'_data@eimsfore c where c.info_sign=' || '''网络新闻''' || ' and a.rggt_id = c.rggt_id ) ) ';
/* v_insertsql := 'insert into CO' || eid || '_' || v_rst.mod_id ||
'_data@eimsfore select * from CO' || eid ||
'_data@eimsfore a where (' || v_rst.mod_sql || ') ' ||
' and rggt_id in (select rggt_id from EIMSWEB_MID where actionsign!=2) and not exists (select null from co' ||
TO_CHAR(eid) || '_' || v_rst.mod_id ||
'_data@eimsfore b where a.rggt_id = b.rggt_id) '; */
--DBMS_OUTPUT.PUT_LINE(v_insertsql);
--DBMS_OUTPUT.PUT_LINE('insert into CO' || eid || '_' || v_rst.mod_id || '_data@eimsfore ' || '(RGGT_ID,MTMC,CS,MTRQ,PAGE,BMMC,AUTHOR,ZKMC,WZXSFL,FONTSIZE,ZMJ,ZZS, ' || 'MTBZ_1,MTBZ_2,WZZT,ZTXZ,PPMC,CPMC,GGXH,WZNRFL,TITLE,GG_ID,CFID, ');
-- DBMS_OUTPUT.PUT_LINE('BRIEF,XXTS,BZ,WZH,WZ,YMGG,SJ,ZYMC,PIC_ID,KD,GD,LRRQ,KGB,SSQ, ' || 'INFO_1,DQ,INFO_2,BMGG,INFO_3,GGZMC,INFO_SIGN,GGZXZ,ZFY,XQS, ' || 'CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5) ' || '(select a.RGGT_ID,a.MTMC,b.CS,a.MTRQ,a.PAGE,a.BMMC,a.AUTHOR,'''',' || 'a.WZXSFL,a.FONTSIZE,a.ZMJ,a.ZZS,b.two_class,a.mtbz_2,a.WZZT,' );
--DBMS_OUTPUT.PUT_LINE('a.ZTXZ,a.PPMC,a.CPMC,a.GGXH,a.WZNRFL,a.TITLE,a.GG_ID,a.CFID,' || 'a.BRIEF,a.XXTS,b.bkzzpd,a.WZH,a.WZ,a.YMGG,' || 'a.SJ,a.ZYMC,a.PIC_ID,a.KD,a.GD,a.LRRQ,a.KGB,b.ssq,' || 'b.FXL, b.dq,a.INFO_2,a.BMGG,a.INFO_3,'''',a.INFO_SIGN,' || ''''','''',to_char(a.MTRQ-1,''D''),' );
--DBMS_OUTPUT.PUT_LINE('a.CPJG,a.LRRY,a.JCZB1,a.JCZB2,a.JCZB3,a.JCZB4,a.JCZB5 ' || 'from (select * from EIMSWEB_MID where '|| v_rst.mod_sql || ' ) a,EIMSNEWMTJBZLK b ');
--DBMS_OUTPUT.PUT_LINE('where a.actionsign!=2 and b.eid(+)=' || TO_CHAR(eid) || ' and a.MTMC=b.mtmc(+) and not exists (select null from CO' || eid || '_' || v_rst.mod_id || '_data@eimsfore c where c.info_sign=' || '''网络新闻''' || ' and a.rggt_id = c.rggt_id ) ) ');
EXECUTE IMMEDIATE v_insertsql;
commit;
end if;
END LOOP;
CLOSE v_cur;
--select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into presql from dual;
--DBMS_OUTPUT.PUT_LINE('mod--end:' || presql);
--7月7日 不需要添加日志记录
--删除mid表中的记录
-- DBMS_OUTPUT.put_line ('delete from EIMSWEB_MID');
--delete from EIMSWEB_MID;
--commit;
end if;
END IF;
END IF;
--如果是个性化企业
IF (etype = 2) THEN
IF (sourcetype = 5) THEN
--将网络已编辑新闻查询后放入中间表
--DBMS_OUTPUT.PUT_LINE('insert into EIMSWEB_EDIT_MID (select a.* from EIMSWEB_EDIT a where eid='||to_char(eid)||' and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')>'''||startDate||''' and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')<='''||endDate||''' '||sqlString);
/*EXECUTE IMMEDIATE 'insert into EIMSWEB_EDIT_MID (select a.* from EIMSWEB_EDIT a where eid=' ||
TO_CHAR(eid) ||
' and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')>''' ||
startdate ||
''' and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')<=''' ||
enddate || ''' ' || sqlstring;
commit;*/
--如果是删除或修改:则将相关的信息 先从摘要表和企业表里删除;
--DBMS_OUTPUT.PUT_LINE( 'delete from co'||to_char(eid)||'_smy@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_EDIT_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || TO_CHAR(eid) ||
'_smy@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_EDIT_MID where (actionsign=2 or actionsign=1))';
commit;
--DBMS_OUTPUT.PUT_LINE( 'delete from co'||to_char(eid)||'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_EDIT_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || TO_CHAR(eid) ||
'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_EDIT_MID where (actionsign=2 or actionsign=1))';
commit;
--***************************7月2日 begin
--删除中间表中不再处理的记录
--DBMS_OUTPUT.PUT_LINE('delete from EIMSWEB_EDIT_MID where actionsign=2');
--delete from EIMSWEB_EDIT_MID where actionsign = 2;
--commit;
--***************************7月2日 end
--因为在个性化企业的执行过程中,先进行企业表的更新(此过程是只更新一个企业表),再进行企业模块表的更新(此过程是循环每一个表更新)。
--如果在此处将中间表中actionsign = 2的数据删除,那么在后面的"企业模块表的更新"的过程中,将无法找到要删除的数据。
--解决办法:不删除中间表中actionsign = 2的数据,在把中间表的数据拷贝到企业表或企业模块表的过程中,添加条件,达到不拷贝ctionsign = 2数据的目的。
--按照对照关系查找并替换相关的字段
FOR r_replace IN c_replace(eid, sourcetype) LOOP
--DBMS_OUTPUT.put_line('REPLACE:' || r_replace.rsql);
IF (r_replace.rsql IS NOT NULL) THEN
EXECUTE IMMEDIATE 'UPDATE EIMSWEB_EDIT_MID ' ||
r_replace.rsql;
commit;
END IF;
END LOOP;
--将做过对照关系处理的记录插入coxxx_data表,
/*DBMS_OUTPUT.put_line
( 'insert into co'
|| TO_CHAR (eid)
|| '_data@eimsfore '
|| '(select a.RGGT_ID,a.MTMC,a.CS,a.MTRQ,a.PAGE,a.BMMC,a.AUTHOR,'''','
);
DBMS_OUTPUT.put_line
('a.WZXSFL,a.FONTSIZE,a.ZMJ,a.ZZS,b.two_class,b.mtbz_2,a.WZZT,'
);
DBMS_OUTPUT.put_line
( 'a.ZTXZ,a.PPMC,a.CPMC,a.GGXH,a.WZNRFL,a.TITLE,a.GG_ID,a.CFID,'
|| 'a.BRIEF,a.XXTS,b.bkzzpd,a.WZH,a.WZ,a.YMGG,'
);
DBMS_OUTPUT.put_line
( 'a.SJ,a.ZYMC,a.PIC_ID,a.KD,a.GD,a.LRRQ,a.KGB,b.ssq,'
|| 'a.INFO_1, b.dq,a.INFO_2,a.BMGG,a.INFO_3,'''',''品牌新闻'','
);
DBMS_OUTPUT.put_line
( ''''','''',to_char(a.MTRQ-1,''D''),'
|| 'a.CPJG,a.LRRY,a.JCZB1,a.JCZB2,a.JCZB3,a.JCZB4,a.JCZB5 '
);
DBMS_OUTPUT.put_line
( 'from EIMSWEB_EDIT_MID a,EIMSNEWMTJBZLK b '
|| 'where b.eid(+)='
|| TO_CHAR (eid)
|| ' and a.MTMC=b.mtmc(+) )'
);*/
EXECUTE IMMEDIATE 'insert into co' || TO_CHAR(eid) ||
'_data@eimsfore ' || '(RGGT_ID,MTMC,CS,MTRQ,PAGE,BMMC,AUTHOR,ZKMC,WZXSFL,FONTSIZE,ZMJ,' ||
'ZZS,MTBZ_1,MTBZ_2,WZZT,ZTXZ,PPMC,CPMC,WZNRFL,TITLE,GG_ID,CFID,BRIEF,XXTS,BZ,WZH,WZ,' ||
'YMGG,SJ,ZYMC,PIC_ID,KD,GD,LRRQ,SSQ,INFO_1,DQ,INFO_2,BMGG,INFO_3,GGZMC,INFO_SIGN,' ||
'GGZXZ,ZFY,XQS,CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5' ||
')(select a.RGGT_ID,a.MTMC,b.CS,a.MTRQ,a.PAGE,a.BMMC,a.AUTHOR,'''',' ||
'a.WZXSFL,a.FONTSIZE,a.ZMJ,a.ZZS,b.two_class,b.mtbz_2,a.WZZT,' ||
'a.ZTXZ,a.PPMC,a.CPMC,a.GGXH,a.WZNRFL,a.TITLE,a.GG_ID,a.CFID,' ||
'a.BRIEF,a.XXTS,b.bkzzpd,a.WZH,a.WZ,a.YMGG,' ||
'a.SJ,a.ZYMC,a.PIC_ID,a.KD,a.GD,a.LRRQ,a.KGB,b.ssq,' ||
'b.FXL, b.dq,a.INFO_2,a.BMGG,a.INFO_3,'''',''网络新闻'',' ||
''''','''',to_char(a.MTRQ-1,''D''),' ||
'a.CPJG,a.LRRY,a.JCZB1,a.JCZB2,a.JCZB3,a.JCZB4,a.JCZB5 ' ||
'from EIMSWEB_EDIT_MID a,EIMSNEWMTJBZLK b ' ||
--7月2日
--在如下sql语句中添加了"a.actionsign!=2"。
--原因:不拷贝已经删除的数据
'where a.actionsign!=2 and b.eid(+)=' ||
TO_CHAR(eid) || ' and a.MTMC=b.mtmc(+) )';
commit;
--将做过对照关系处理的记录插入coXXX——smy表
--DBMS_OUTPUT.PUT_LINE('insert into co'||to_char(eid)||'_smy@eimsfore '
--||'(select RGGT_ID,SMY1,SMY2,SMY3,SMY4,SMY5,SMY6,SMY7,SMY8,SMY9,SMY0 from EIMSWEB_EDIT_MID)');
--7月2日
--在如下sql语句中添加了"where actionsign!=2"。
--原因:不拷贝已经删除的数据
EXECUTE IMMEDIATE 'insert into co' || TO_CHAR(eid) ||
'_smy@eimsfore ' ||
'(select RGGT_ID,SMY1,SMY2,SMY3,SMY4,SMY5,SMY6,SMY7,SMY8,SMY9,SMY0 from EIMSWEB_EDIT_MID where actionsign!=2)';
commit;
--循环每个企业的模块信息表
v_sql := 'select * from co_mod@eimsfore where trim(MOD_TYPE)=''1'' and co_id=' || eid;
OPEN v_cur FOR v_sql;
LOOP
FETCH v_cur
INTO v_rst;
EXIT WHEN v_cur%NOTFOUND;
--7月2日
--如果是删除或修改:则将相关的信息 先从企业模块表里删除;
--DBMS_OUTPUT.PUT_LINE( 'delete from co'|| eid || '_' || v_rst.mod_id ||'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_EDIT_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || eid || '_' ||
v_rst.mod_id ||
'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSWEB_EDIT_MID where (actionsign=2 or actionsign=1))';
commit;
--将信息插入到企业模块表中
--7月2日
--添加判断mod_sql字段是否为空,如果是空,则不执行
if v_rst.mod_sql || 'a' != 'a' then
v_insertsql := 'insert into CO' || eid || '_' || v_rst.mod_id ||
'_data@eimsfore select * from CO' || eid ||
'_data@eimsfore where (' || v_rst.mod_sql || ') ' ||
' and rggt_id in (select RGGT_ID from EIMSWEB_EDIT_MID where actionsign!=2)';
EXECUTE IMMEDIATE v_insertsql;
commit;
end if;
END LOOP;
CLOSE v_cur;
--不需要添加日志记录
--删除mid表中的记录
-- delete from EIMSWEB_EDIT_MID;
sql_stmt1 := '';
--commit;
END IF;
END IF;
--
END IF;
insert into log_fu(eid,istate) values(r_enterprise.eid,'end');
commit;
END LOOP;
--7月3日
--修改CO_WEB_EDITED中eid在eimsweb_per中,并且ISTATE为0的记录
update CO_WEB_EDITED
set ISTATE = 1
where ISTATE <> 1
and rggt_id in (select rggt_id from eimsweb_per);
commit;
--删除准备表中的数据
--delete from eimsweb_per;
--commit;
--DBMS_OUTPUT.put_line('over load');
--7月8日
--添加异常处理
exception
when no_data_found then
rollback;
when others then
rollback;
delete from eimsweb_per;
commit;
delete from EIMSWEB_MID;
commit;
END eimsweb_web_new;
分发平面数据的存储过程如下:
CREATE OR REPLACE PROCEDURE pmxw_pp_new IS
--declare
sql_stmt VARCHAR2(4000);
sql_stmt1 VARCHAR2(4000);
sql_stmt2 VARCHAR2(4000);
sql_stmt3 VARCHAR2(4000);
sql_stmt4 VARCHAR2(4000);
sql_stmt5 VARCHAR2(4000);
sql_stmt6 VARCHAR2(4000);
sql_stmt7 VARCHAR2(4000);
sql_stmt8 VARCHAR2(4000);
sql_stmt9 VARCHAR2(4000);
sourcetype NUMBER;
eid NUMBER;
etype NUMBER;
sqlstring VARCHAR2(4000);
i NUMBER;
num number;
TYPE v_cur1 IS REF CURSOR;
v_cur v_cur1;
v_rst co_mod@eimsfore%ROWTYPE;
v_sql VARCHAR2(4000);
v_insertsql VARCHAR2(4000);
midsqlstr VARCHAR2(4000);
cosqlstr VARCHAR2(4000);
corstrsql VARCHAR2(4000);
--presql VARCHAR2(4000);
--logsqlstr VARCHAR2(3000);
CURSOR c_enterprise IS
SELECT ID,
eid,
ename,
etype,
etranslate,
edatasource,
esql,
esql1,
esql2,
esql3,
esql4,
esql5,
esql6,
esql7,
esql8,
esql9
FROM eimsenterprise
where edatasource = 1;
--and eid='1921';
-- ORDER BY edatasource;
CURSOR c_replace(enterid NUMBER, datasource NUMBER) IS
SELECT *
FROM eimsreplace
WHERE eid = enterid
AND rdatasource = datasource;
BEGIN
sqlstring := '';
--7月4日 start
--将eimspp表中要处理的数据先保存到准备表中,中间表到准备表中取数据;
delete from eimspp_per;
commit;
insert into eimspp_per( PP_ID,AUTHOR,BRIEF,CFID,CPJG,CPMC,CS, FONTSIZE,
GD,GG_ID,GGXH,INFO_1,INFO_2,INFO_3,JCZB1,JCZB2,
JCZB3,JCZB4,JCZB5,KD,KGB,LRRQ,LRRY,MTMC,MTRQ,
PAGE,PPMC,RGGT_ID,SJ,TITLE,WZ,WZH,WZNRFL,WZXSFL,
WZZT,XXTS,YMGG,ZMJ,ZTXZ,ZYMC,ZZS,PIC_ID,BMGG,BMMC,
SMY0,SMY1,SMY2,SMY3,SMY4,SMY5,SMY6,SMY7,SMY8,
SMY9,SENDTIME,ACTIONSIGN,ISTATE)
(select PP_ID,AUTHOR,BRIEF,CFID,CPJG,CPMC,CS, FONTSIZE,
GD,GG_ID,GGXH,INFO_1,INFO_2,INFO_3,JCZB1,JCZB2,
JCZB3,JCZB4,JCZB5,KD,KGB,LRRQ,LRRY,MTMC,MTRQ,
PAGE,PPMC,RGGT_ID,SJ,TITLE,WZ,WZH,WZNRFL,WZXSFL,
WZZT,XXTS,YMGG,ZMJ,ZTXZ,ZYMC,ZZS,PIC_ID,BMGG,BMMC,
SMY0,SMY1,SMY2,SMY3,SMY4,SMY5,SMY6,SMY7,SMY8,
SMY9,SENDTIME,ACTIONSIGN,ISTATE
from eimspp
where istate = 0
and rownum<2000 );
commit;
--2008-8-22 信息插入到企业时,修改录入时间为当时系统的时间
--update eimspp_per set lrrq=sysdate where actionsign!=1;
--commit;
--从准备表中取中要修改和删除的数据
delete from eimspp_per_del;
commit;
insert into eimspp_per_del (select * from eimspp_per where actionsign=2 or actionsign=1);
commit;
--将要删除的数据从准备表中清除
delete from eimspp_per where actionsign=2;
commit;
--and sendtime >= to_date(startdate, 'yyyy-mm-dd hh24:mi:ss')
-- and sendtime < to_date(enddate, 'yyyy-mm-dd hh24:mi:ss'));
--7月4日 end
--pp表在日志中的记录删除
--DBMS_OUTPUT.PUT_LINE( 'delete from eimspp_log where PP_ID in (select PP_ID from EIMSPP where (actionsign=2 or actionsign=1) and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')>'''||startDate||''' and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')<='''||endDate||''')');
/*delete from eimspp_log
where PP_ID in
(select PP_ID
from eimspp_per
);*/
--对于每一个数据源和相应的企业
FOR r_enterprise IN c_enterprise LOOP
--打印企业信息测试使用
--DBMS_OUTPUT.PUT_LINE('Enterprise:'|| r_enterprise.id||','||r_enterprise.eid||','||r_enterprise.ename||','||r_enterprise.etype||','||r_enterprise.etranslate||','||r_enterprise.edatasource||','||r_enterprise.esql);
DBMS_OUTPUT.PUT_LINE('Enterprise:' || r_enterprise.id || ',' ||
r_enterprise.eid);
--select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into presql from dual;
--DBMS_OUTPUT.PUT_LINE('start:' || presql);
--insert into log_fu(eid,istate) values(r_enterprise.eid,'begin');
--commit;
--获取数据源类型和对应的sql条件
sourcetype := r_enterprise.edatasource;
sql_stmt := r_enterprise.esql;
sql_stmt1 := r_enterprise.esql1;
sql_stmt2 := r_enterprise.esql2;
sql_stmt3 := r_enterprise.esql3;
sql_stmt4 := r_enterprise.esql4;
sql_stmt5 := r_enterprise.esql5;
sql_stmt6 := r_enterprise.esql6;
sql_stmt7 := r_enterprise.esql7;
sql_stmt8 := r_enterprise.esql8;
sql_stmt9 := r_enterprise.esql9;
eid := r_enterprise.eid;
--DBMS_OUTPUT.put_line(eid);
etype := r_enterprise.etype;
sqlstring := '';
--dbms_output.put_line(sql_stmt);
/* IF (sql_stmt1 IS NOT NULL) THEN
sql_stmt := sql_stmt || sql_stmt1;
END IF;*/
IF (sql_stmt1 IS NOT NULL) THEN
sql_stmt := sql_stmt || sql_stmt1;
END IF;
IF (sql_stmt2 IS NOT NULL) THEN
sql_stmt := sql_stmt || sql_stmt2;
END IF;
if (sql_stmt3 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt3;
end if;
if (sql_stmt4 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt4;
end if;
if (sql_stmt5 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt5;
end if;
if (sql_stmt6 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt6;
end if;
if (sql_stmt7 IS NOT NULL) then
sql_stmt := sql_stmt || Sql_stmt7;
end if;
if (sql_stmt8 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt8;
end if;
if (sql_stmt9 IS NOT NULL) then
sql_stmt := sql_stmt || sql_stmt9;
end if;
--7月4日
--当查询条件为1=0,2=1时,不需要进行下面的循环,故跳出,进行下一个企业的条件
IF (sql_stmt IS NOT NULL and sql_stmt != '1=0' and sql_stmt != '2=1') THEN
sqlstring := sqlstring || ' and ' || sql_stmt || ')';
--如果是常规企业
IF (etype = 1) THEN
--如果是平面新闻
IF (sourcetype = 1) THEN
--=========================new add======2008-6-3======================
--删除mid表中的记录
--DBMS_OUTPUT.put_line('delete from EIMSPP_MID');
delete from EIMSPP_MID;
commit;
--========================================================
--execute immediate 'commit';
--将需要处理的数据放入中间表
midsqlstr := 'insert into EIMSPP_MID (select distinct a.*,0,' ||
TO_CHAR(eid) || ',''品牌新闻'''||
--7月4日 begin
' from eimspp_per a where 1=1 ' || sqlstring;
--DBMS_OUTPUT.PUT_LINE(midsqlstr);
EXECUTE IMMEDIATE midsqlstr;
commit;
--判断eimsweb_mid是否有数据
select count(*) into num from EIMSPP_MID;
if(num>0)then
--***************************7月3日 begin
--删除中间id表中的不处理的内容
--DBMS_OUTPUT.put_line('delete from EIMSPP_MID where actionsign=2');
--delete from EIMSPP_MID where actionsign = 2;
--commit;
--***************************7月2日 end
--因为在常规企业的执行过程中,先进行企业表的更新(此过程是只更新一个企业表),再进行企业模块表的更新(此过程是循环每一个表更新)。
--如果在此处将中间表中actionsign = 2的数据删除,那么在后面的"企业模块表的更新"的过程中,将无法找到要删除的数据。
--解决办法:不删除中间表中actionsign = 2的数据,在把中间表的数据拷贝到企业表或企业模块表的过程中,添加条件,达到不拷贝ctionsign = 2数据的目的。
--对于需要增加和修改的记录,按照对照关系查找并替换相关的字段
FOR r_replace IN c_replace(eid, sourcetype) LOOP
-- DBMS_OUTPUT.put_line('REPLACE:' || r_replace.rsql);
IF (r_replace.rsql IS NOT NULL) THEN
--DBMS_OUTPUT.put_line('REPLACE:' || r_replace.id);
EXECUTE IMMEDIATE 'UPDATE EIMSPP_MID ' || r_replace.rsql;
commit;
END IF;
END LOOP;
commit;
--如果是删除或修改:则将相关的信息先从企业表和日志表里删除;
--DBMS_OUTPUT.PUT_LINE('delete from co'||to_char(eid)||'_smy@eimsfore where RGGT_ID in (select RGGT_ID from EIMSPP_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || TO_CHAR(eid) ||
'_smy@eimsfore where RGGT_ID in (select RGGT_ID from eimspp_per_del )';
commit;
--DBMS_OUTPUT.PUT_LINE( 'delete from co'||to_char(eid)||'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSPP_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || TO_CHAR(eid) ||
'_data@eimsfore where RGGT_ID in (select RGGT_ID from eimspp_per_del )';
commit;
--清空eimsco_data中的数据
delete from eimsco_data;
commit;
--将做过对照关系处理的记录插入eimsco_data表,
cosqlstr := 'insert into eimsco_data ' ||
'(RGGT_ID,MTMC,CS,MTRQ,PAGE,BMMC,AUTHOR, ' ||
'ZKMC,WZXSFL,FONTSIZE,ZMJ,ZZS,MTBZ_1,MTBZ_2,WZZT,ZTXZ,PPMC,CPMC, ' ||
'GGXH,WZNRFL,TITLE,GG_ID,CFID,BRIEF,XXTS,BZ,WZH,WZ,YMGG,SJ,ZYMC, ' ||
'PIC_ID,KD,GD,LRRQ,KGB,SSQ,INFO_1,DQ,INFO_2,BMGG,INFO_3,GGZMC,' ||
'INFO_SIGN,GGZXZ,ZFY,XQS,CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5 ' ||
')(select a.RGGT_ID,a.MTMC,b.CS,a.MTRQ,a.PAGE,a.BMMC,a.AUTHOR,'''',' ||
'a.WZXSFL,a.FONTSIZE,a.ZMJ,a.ZZS,b.two_class,b.mtbz_2,a.WZZT,' ||
'a.ZTXZ,a.PPMC,a.CPMC,a.GGXH,a.WZNRFL,a.TITLE,a.GG_ID,a.CFID,' ||
'a.BRIEF,a.XXTS,b.bkzzpd,a.WZH,a.WZ,a.YMGG,' ||
'a.SJ,a.ZYMC,a.PIC_ID,a.KD,a.GD,a.LRRQ,a.KGB,b.ssq,' ||
'b.FXL, b.dq,a.INFO_2,a.BMGG,a.INFO_3,'''',''品牌新闻'',' ||
''''','''',to_char(a.MTRQ-1,''D''),' ||
'a.CPJG,a.LRRY,a.JCZB1,a.JCZB2,a.JCZB3,a.JCZB4,a.JCZB5 ' ||
'from EIMSPP_MID a,EIMSNEWMTJBZLK b ' ||
'where b.eid(+)=' || TO_CHAR(eid) ||
' and a.MTMC=b.mtmc(+) and not exists (select null from co' ||
TO_CHAR(eid) ||
'_data@eimsfore c where a.rggt_id = c.rggt_id ))';
EXECUTE IMMEDIATE cosqlstr;
commit;
--将做过对照关系处理的记录插入coxxx_data表,
cosqlstr := 'insert into co' || TO_CHAR(eid) || '_data@eimsfore ' ||
'(RGGT_ID,MTMC,CS,MTRQ,PAGE,BMMC,AUTHOR, ' ||
'ZKMC,WZXSFL,FONTSIZE,ZMJ,ZZS,MTBZ_1,MTBZ_2,WZZT,ZTXZ,PPMC,CPMC, ' ||
'GGXH,WZNRFL,TITLE,GG_ID,CFID,BRIEF,XXTS,BZ,WZH,WZ,YMGG,SJ,ZYMC, ' ||
'PIC_ID,KD,GD,LRRQ,KGB,SSQ,INFO_1,DQ,INFO_2,BMGG,INFO_3,GGZMC,' ||
'INFO_SIGN,GGZXZ,ZFY,XQS,CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5) ' ||
'(select RGGT_ID,MTMC,CS,MTRQ,PAGE,BMMC,AUTHOR, ' ||
'ZKMC,WZXSFL,FONTSIZE,ZMJ,ZZS,MTBZ_1,MTBZ_2,WZZT,ZTXZ,PPMC,CPMC, ' ||
'GGXH,WZNRFL,TITLE,GG_ID,CFID,BRIEF,XXTS,BZ,WZH,WZ,YMGG,SJ,ZYMC, ' ||
'PIC_ID,KD,GD,LRRQ,KGB,SSQ,INFO_1,DQ,INFO_2,BMGG,INFO_3,GGZMC,' ||
'INFO_SIGN,GGZXZ,ZFY,XQS,CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5 ' ||
' from eimsco_data)';
-- DBMS_OUTPUT.PUT_LINE(cosqlstr);
EXECUTE IMMEDIATE cosqlstr;
commit;
--splitinfonew@eimsfore(eid);
--将做过对照关系处理的记录插入coXXX——smy表
--DBMS_OUTPUT.PUT_LINE('insert into co'||to_char(eid)||'_smy@eimsfore '
--||'(select RGGT_ID,SMY1,SMY2,SMY3,SMY4,SMY5,SMY6,SMY7,SMY8,SMY9,SMY0 from eimspp_mid)');
--7月3日
--在如下sql语句中添加了"a.actionsign!=2 and "。
--原因:不拷贝已经删除的数据
corstrsql := 'insert into co' || TO_CHAR(eid) || '_smy@eimsfore ' ||
'(select RGGT_ID,SMY1,SMY2,SMY3,SMY4,SMY5,SMY6,SMY7,SMY8,SMY9,SMY0 from eimspp_mid a where not exists(select null from co' ||
TO_CHAR(eid) ||
'_smy@eimsfore b where a.RGGT_ID=b.RGGT_ID))';
-- DBMS_OUTPUT.put_line(corstrsql);
EXECUTE IMMEDIATE corstrsql;
commit;
--循环每个企业的模块信息表
-- splitinfonew@eimsfore(eid);
v_sql := 'select * from co_mod@eimsfore where trim(MOD_TYPE)=''1'' and co_id=' || eid;
OPEN v_cur FOR v_sql;
i := 0;
LOOP
FETCH v_cur
INTO v_rst;
EXIT WHEN v_cur%NOTFOUND;
--7月3日
--如果是删除或修改:则将相关的信息 先从企业模块表里删除;
--7月3日
--如果是删除或修改:则将相关的信息 先从企业模块表里删除;
--DBMS_OUTPUT.PUT_LINE( 'delete from co'|| eid || '_' || v_rst.mod_id ||'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSPP_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || eid || '_' ||
v_rst.mod_id ||
'_data@eimsfore where RGGT_ID in (select RGGT_ID from eimspp_per_del)';
commit;
--将信息插入到企业模块表中
--7月3日
--添加判断mod_sql字段是否为空,如果是空,则不执行
--在执行语句中添加 where actionsign!=2
if v_rst.mod_sql || 'a' != 'a' then
DBMS_OUTPUT.PUT_LINE('mod_id' || v_rst.mod_id );
v_insertsql := 'insert into CO' || eid || '_' || v_rst.mod_id ||'_data@eimsfore '||
'(RGGT_ID,MTMC,CS,MTRQ,PAGE,BMMC,AUTHOR, ' ||
'ZKMC,WZXSFL,FONTSIZE,ZMJ,ZZS,MTBZ_1,MTBZ_2,WZZT,ZTXZ,PPMC,CPMC, ' ||
'GGXH,WZNRFL,TITLE,GG_ID,CFID,BRIEF,XXTS,BZ,WZH,WZ,YMGG,SJ,ZYMC, ' ||
'PIC_ID,KD,GD,LRRQ,KGB,SSQ,INFO_1,DQ,INFO_2,BMGG,INFO_3,GGZMC,' ||
'INFO_SIGN,GGZXZ,ZFY,XQS,CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5) ' ||
' select RGGT_ID,MTMC,CS,MTRQ,PAGE,BMMC,AUTHOR, ' ||
'ZKMC,WZXSFL,FONTSIZE,ZMJ,ZZS,MTBZ_1,MTBZ_2,WZZT,ZTXZ,PPMC,CPMC, ' ||
'GGXH,WZNRFL,TITLE,GG_ID,CFID,BRIEF,XXTS,BZ,WZH,WZ,YMGG,SJ,ZYMC, ' ||
'PIC_ID,KD,GD,LRRQ,KGB,SSQ,INFO_1,DQ,INFO_2,BMGG,INFO_3,GGZMC, ' ||
'INFO_SIGN,GGZXZ,ZFY,XQS,CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5 ' ||
'from eimsco_data a where (' || v_rst.mod_sql || ') ' ||
'and rggt_id in (select rggt_id from eimspp_mid ) and not exists (select null from co' ||
TO_CHAR(eid) || '_' || v_rst.mod_id ||
'_data@eimsfore b where a.rggt_id = b.rggt_id) ';
EXECUTE IMMEDIATE v_insertsql;
commit;
end if;
END LOOP;
CLOSE v_cur;
--删除mid表中的记录
--delete from EIMSPP_MID;
--commit;
END IF;
END IF;
END IF;
-- select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into presql from dual;
--DBMS_OUTPUT.PUT_LINE('end:' || presql);
--如果是个性化企业
IF (etype = 2) THEN
IF (sourcetype = 1) THEN
--将平面已编辑新闻查询后放入中间表
--DBMS_OUTPUT.PUT_LINE('insert into EIMSPP_EDIT_MID (select a.* from eimspp_edit a where eid='||to_char(eid)||' and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')>'''||startDate||''' and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')<='''||endDate||''' '||sqlString);
--7月7日
--存储过程不需要从eimspp_edit取数据,直接从EIMSPP_EDIT_MID中取数据操作就可以了
/* EXECUTE IMMEDIATE 'insert into EIMSPP_EDIT_MID (select a.* from eimspp_edit a where eid=' ||
TO_CHAR(eid) ||
' and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')>''' ||
startdate ||
''' and to_char(sendtime,''yyyy-mm-dd hh24:mi:ss'')<=''' ||
enddate || ''' ' || sqlstring;
commit;*/
--如果是删除或修改:则将相关的信息 先从企业表和日志表里删除;
--DBMS_OUTPUT.PUT_LINE( 'delete from co'||to_char(eid)||'_smy@eimsfore where RGGT_ID in (select RGGT_ID from EIMSPP_EDIT_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || TO_CHAR(eid) ||
'_smy@eimsfore where RGGT_ID in (select RGGT_ID from eimspp_per_del)';
commit;
--DBMS_OUTPUT.PUT_LINE( 'delete from co'||to_char(eid)||'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSPP_EDIT_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || TO_CHAR(eid) ||
'_data@eimsfore where RGGT_ID in (select RGGT_ID from eimspp_per_del)';
commit;
--***************************7月3日 begin
--删除中间表中不再处理的记录
--DBMS_OUTPUT.PUT_LINE('delete from EIMSPP_EDIT_MID where actionsign=2');
--delete from EIMSPP_EDIT_MID where actionsign = 2;
--commit;
--***************************7月3日 end
--因为在个性化企业的执行过程中,先进行企业表的更新(此过程是只更新一个企业表),再进行企业模块表的更新(此过程是循环每一个表更新)。
--如果在此处将中间表中actionsign = 2的数据删除,那么在后面的"企业模块表的更新"的过程中,将无法找到要删除的数据。
--解决办法:不删除中间表中actionsign = 2的数据,在把中间表的数据拷贝到企业表或企业模块表的过程中,添加条件,达到不拷贝ctionsign = 2数据的目的。
--按照对照关系查找并替换相关的字段
FOR r_replace IN c_replace(eid, sourcetype) LOOP
--DBMS_OUTPUT.put_line('REPLACE:' || r_replace.rsql);
IF (r_replace.rsql IS NOT NULL) THEN
EXECUTE IMMEDIATE 'UPDATE EIMSPP_EDIT_MID ' || r_replace.rsql;
commit;
END IF;
END LOOP;
--将做过对照关系处理的记录插入coxxx_data表,
/*DBMS_OUTPUT.put_line
( 'insert into co'
|| TO_CHAR (eid)
|| '_data@eimsfore '
|| '(select a.RGGT_ID,a.MTMC,a.CS,a.MTRQ,a.PAGE,a.BMMC,a.AUTHOR,'''','
);
DBMS_OUTPUT.put_line
('a.WZXSFL,a.FONTSIZE,a.ZMJ,a.ZZS,b.two_class,b.mtbz_2,a.WZZT,'
);
DBMS_OUTPUT.put_line
( 'a.ZTXZ,a.PPMC,a.CPMC,a.GGXH,a.WZNRFL,a.TITLE,a.GG_ID,a.CFID,'
|| 'a.BRIEF,a.XXTS,b.bkzzpd,a.WZH,a.WZ,a.YMGG,'
);
DBMS_OUTPUT.put_line
( 'a.SJ,a.ZYMC,a.PIC_ID,a.KD,a.GD,a.LRRQ,a.KGB,b.ssq,'
|| 'a.INFO_1, b.dq,a.INFO_2,a.BMGG,a.INFO_3,'''',''品牌新闻'','
);
DBMS_OUTPUT.put_line
( ''''','''',to_char(a.MTRQ-1,''D''),'
|| 'a.CPJG,a.LRRY,a.JCZB1,a.JCZB2,a.JCZB3,a.JCZB4,a.JCZB5 '
);
DBMS_OUTPUT.put_line
( 'from EIMSPP_EDIT_MID a,EIMSNEWMTJBZLK b '
|| 'where b.eid(+)='
|| TO_CHAR (eid)
|| ' and a.MTMC=b.mtmc(+) )'
);*/
EXECUTE IMMEDIATE 'insert into co' || TO_CHAR(eid) ||
'_data@eimsfore ' || '(RGGT_ID,MTMC,CS,MTRQ,BMMC,AUTHOR,ZKMC,WZXSFL,FONTSIZE,ZMJ,' ||
'ZZS,MTBZ_1,MTBZ_2,ZTXZ,PPMC,CPMC,GGXH,WZNRFL,TITLE,GG_ID,CFID,BRIEF,XXTS,BZ,WZH,' ||
'WZ,YMGG,SJ,ZYMC,PIC_ID,KD,GD,LRRQ,KGB,SSQ,INFO_1,DQ,INFO_2,BMGG,INFO_3,' ||
'GGZMC,INFO_SIGN,GGZXZ,ZFY,XQS,CPJG,LRRY,JCZB1,JCZB2,JCZB3,JCZB4,JCZB5' ||
')(select a.RGGT_ID,a.MTMC,b.CS,a.MTRQ,a.PAGE,a.BMMC,a.AUTHOR,'''',' ||
'a.WZXSFL,a.FONTSIZE,a.ZMJ,a.ZZS,b.two_class,b.mtbz_2,a.WZZT,' ||
'a.ZTXZ,a.PPMC,a.CPMC,a.GGXH,a.WZNRFL,a.TITLE,a.GG_ID,a.CFID,' ||
'a.BRIEF,a.XXTS,b.bkzzpd,a.WZH,a.WZ,a.YMGG,' ||
'a.SJ,a.ZYMC,a.PIC_ID,a.KD,a.GD,a.LRRQ,a.KGB,b.ssq,' ||
'b.FXL, b.dq,a.INFO_2,a.BMGG,a.INFO_3,'''',''品牌新闻'',' ||
''''','''',to_char(a.MTRQ-1,''D''),' ||
'a.CPJG,a.LRRY,a.JCZB1,a.JCZB2,a.JCZB3,a.JCZB4,a.JCZB5 ' ||
'from EIMSPP_EDIT_MID a,EIMSNEWMTJBZLK b ' ||
'where b.eid(+)=' ||
TO_CHAR(eid) || ' and a.MTMC=b.mtmc(+) )';
commit;
--将做过对照关系处理的记录插入coXXX——smy表
--DBMS_OUTPUT.PUT_LINE('insert into co'||to_char(eid)||'_smy@eimsfore '
--||'(select RGGT_ID,SMY1,SMY2,SMY3,SMY4,SMY5,SMY6,SMY7,SMY8,SMY9,SMY0 from EIMSPP_EDIT_MID)');
--7月2日
--在如下sql语句中添加了"where actionsign!=2"。
--原因:不拷贝已经删除的数据
EXECUTE IMMEDIATE 'insert into co' || TO_CHAR(eid) ||
'_smy@eimsfore ' ||
'(select RGGT_ID,SMY1,SMY2,SMY3,SMY4,SMY5,SMY6,SMY7,SMY8,SMY9,SMY0 from EIMSPP_EDIT_MID)';
commit;
--循环每个企业的模块信息表
v_sql := 'select * from co_mod@eimsfore where trim(MOD_TYPE)=''1'' and co_id=' || eid;
OPEN v_cur FOR v_sql;
LOOP
FETCH v_cur
INTO v_rst;
EXIT WHEN v_cur%NOTFOUND;
--7月3日
--如果是删除或修改:则将相关的信息 先从企业模块表里删除;
--DBMS_OUTPUT.PUT_LINE( 'delete from co'|| eid || '_' || v_rst.mod_id ||'_data@eimsfore where RGGT_ID in (select RGGT_ID from EIMSPP_EDIT_MID where (actionsign=2 or actionsign=1))');
EXECUTE IMMEDIATE 'delete from co' || eid || '_' ||
v_rst.mod_id ||
'_data@eimsfore where RGGT_ID in (select RGGT_ID from eimspp_per_del)';
commit;
--将信息插入到企业模块表中
--7月3日
--添加判断mod_sql字段是否为空,如果是空,则不执行
--在执行语句中添加 where actionsign!=2
v_insertsql := 'insert into CO' || eid || '_' || v_rst.mod_id ||
'_data@eimsfore select * from CO' || eid ||
'_data@eimsfore where (' || v_rst.mod_sql || ') ' ||
' and rggt_id in (select RGGT_ID from EIMSPP_EDIT_MID)';
EXECUTE IMMEDIATE v_insertsql;
commit;
END LOOP;
CLOSE v_cur;
--7月7日 不需要添加日志记录
--7月7日
--不是将中间表中的数据全部删除,而是删除中间表中eid 等于当前循环企业id,并且pp_id已经在企业信息表中的数据。
EXECUTE IMMEDIATE ' delete from EIMSPP_EDIT_MID a' ||
' where a.eid=' || eid ||
' and a.pp_id in (select pp_id from CO' || eid || '_data@eimsfore)';
sql_stmt1 := '';
commit;
END IF;
END IF;
END IF;
--insert into log_fu(eid,istate) values(r_enterprise.eid,'end');
--commit;
END LOOP;
--7月4日
--修改EIMSPP中pp_id在eimspp_per中,并且ISTATE为0的记录
--update EIMSPP set ISTATE = 1 where ISTATE <> 1;
update EIMSPP
set ISTATE = 1
where ISTATE <> 1
and pp_id in (select pp_id from eimspp_per);
commit;
update EIMSPP
set ISTATE = 1
where ISTATE <> 1
and pp_id in (select pp_id from eimspp_per_del);
commit;
END pmxw_pp_new;
|