有这样一个情况,当我在单独执行一个存储过程的时候,存储过程可以正常执行,但我把这个放在JOB里定时执行就出问题了,因为执行数据量大,我分成几个不同JOB执行不同存储过程,刚开始还行,但是过了几天,JOB查看状态是提示正在执行,但是执行结果却是空的,纯粹是占着茅坑不拉屎,我手动执行能正常走,但放在JOB里运行,放了一晚上动都没动一下,请问各位大侠是什么原因引起的啊,因为是做统计,很多存储过程要通过JOB定时执行,都是数据量较大的。
解决方案 »
- 有谁能看懂这段代码
- 如何从excel里读取sql,并将执行结果又写入该excel
- 从Oracle 8i 中将数据导入 9i中,会出什么问题
- Oracle 10g(32位)下备份的数据库能否还原到Oracle 10g(64位)的数据库中.
- 北京外企高薪聘有ERP经验的Oracle开发人员
- 关于绿盟安全扫描对oracle的检查报告
- 调用Oracle存储过程(返回记录),请高手指点!
- 求救!OracleOraHome90TNSister服务启动不了
- oracle 在什么情况下开始增加表空间大小
- oracle中的session中查不到正在执行的job会话
- OCI与oracle times ten 选择问题
- C#如何连接Oracle RAC集群数据库
create or replace procedure execute_report_tj_xjdc03 is
type base_cursor is ref cursor;
--CURSOR cur_areacode IS select * from bas_areacode ba where ba.area_code_real like '64%' and ba.area_level != '4';
areacode_info bas_areacode%rowtype;
tjview_info TJ_VIEW%rowtype; cur_tjview base_cursor;
cur_areacode base_cursor;
areaCodeReal varchar2(250);
areaCodeRealR varchar2(250);
areaNull number(20,2) :=0;
areaNullPer number(20,2) :=0; areaWomen number(20,2) :=0;
areaWomenwz number(20,2) :=0; -- areaTjCzrk varchar2(550) := 'insert into TJ_CZRK (AREA,HURELATION,NAME,SEX,BRITH,JZD,HJD,HUXZ,CARD,MZ,WH,ZZ,HY,WORK,LKDATE,LRDATE,HJZT,WZL,ZS,CODE,AID,FACODE) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22)';
begin
DBMS_OUTPUT.put_line('start.....');
delete tj_czrk;
commit;
for x in 3..4 loop
open cur_areacode for select * from bas_areacode ba where ba.area_code_real like '6403%' and ba.area_level = ''||x||'';
loop
fetch cur_areacode into areacode_info;
exit when cur_areacode%notfound;
if (to_number(areacode_info.area_level)=4) then
areaCodeReal:=areacode_info.area_code_real;
areaCodeRealR := areacode_info.area_code;
elsif (to_number(areacode_info.area_level)=3) then
areaCodeReal:= substr(areacode_info.area_code_real,0,9)||'%';
areaCodeRealR := areacode_info.area_code;
elsif (to_number(areacode_info.area_level)=2) then
areaCodeReal:= substr(areacode_info.area_code_real,0,6)||'%';
areaCodeRealR := areacode_info.area_code;
elsif (to_number(areacode_info.area_level)=1) then
areaCodeReal:= substr(areacode_info.area_code_real,0,4)||'%';
areaCodeRealR := areacode_info.area_code;
elsif (to_number(areacode_info.area_level)=0) then
areaCodeReal:= substr(areacode_info.area_code_real,0,2)||'%';
areaCodeRealR := areacode_info.area_code;
end if; areaMemberCount :=0;
areaCard :=0;
areaName:=0;
areaRelation:=0;
areaMemberbirthday:=0;
areaAddresscode:=0;
areaWomenwzl:=0;
DBMS_OUTPUT.put_line('area=='||areaCodeReal); select count(*) into areaMemberCount from wis_member t
where t.canceldate is null and t.movestate in( 'n' , 'm','wf','mw', 'b' , 't' , 'tm' , 'tf' , 'w' , 'gys','fh','gy')
and exists (select 1 from bas_areacode ba where ba.area_code = t.addresscode and ba.area_code_real like ''||areaCodeReal||'' ); -- areaWomenLogic 育龄妇女逻辑关系有错误的数据总数
select count(*) into areaWLogic from wis_women wm2 where wm2.wmarriage != '10' and wm2.wmarriage != '90' and wm2.wexit != 't' and
(
( (wm2.wmarriage in ('20','21','22','23')) and (wm2.hname is null or wm2.hcard is null) and wm2.hemployment != '70')
or
( (wm2.wmarriage in ('20','21','22','23')) and wm2.hname is null and wm2.hemployment = '70')
or
(wm2.wmarriage in ('20','21','22','23','30','40') and wm2.wmardate is null )
or
(wm2.wmarriage != '10' and wm2.wmarriage != '90' and wm2.wmardate is not null and wm2.wmardate < wm2.wbirthday)
or
exists(select 1 from wis_gestation wg0 where wg0.womenid = wm2.wid
and exists(select 1 from wis_gestation wg1 where wg1.womenid = wm2.wid and wg1.gid != wg0.gid and wg1.fetusnum = wg0.fetusnum and wg1.stopdate = wg0.stopdate))
or
exists(select 1 from wis_foster wf0 where wf0.womanid = wm2.wid and wf0.chideadate is not null and wf0.chibirth is not null and wf0.chideadate < wf0.chibirth)
--or
--exists(select 1 from wis_contraception wc0 where wc0.womanid = wm2.wid and wc0.constatus in ('700','710','720','730','790','800') and wc0.conmachine is null)
or
exists(select wf1.chinum from wis_foster wf1 where wf1.womanid = wm2.wid and
exists( select ges.fetusnum from wis_gestation ges
where ges.womenid = wm2.wid and ges.fetusnum = wf1.chinum and ges.gesresult ='10' and wf1.chibirth != ges.stopdate ))
)
and exists(select wm.memberid from wis_member wm where wm.memberid = wm2.memberid
and exists (select bac.area_code from bas_areacode bac where bac.area_code=wm.addresscode and bac.area_code_real like ''||areaCodeReal||'' )
and wm.canceldate is null and wm.movestate in( 'n' , 'm','wf','mw', 'b' , 't' , 'tm' , 'tf' , 'w' , 'gys','fh','gy')
);
.......... if (to_number(areaMemberCount) > 0) then
areaNullPer := round((areaMemberCount - areaNull) / areaMemberCount,4)*100;
areaCard :=round((areaMemberCount-areaCard)/areaMemberCount,4)*100;
areaName :=round((areaMemberCount-areaName)/areaMemberCount,4)*100;
areaRelation:=round((areaMemberCount-areaRelation)/areaMemberCount,4)*100;
areaMemberbirthday:=round((areaMemberCount-areaMemberbirthday)/areaMemberCount,4)*100;
areaAddresscode:=round((areaMemberCount-areaAddresscode)/areaMemberCount,4)*100;
areaFamliyaddress:=round((areaMemberCount-areaFamliyaddress)/areaMemberCount,4)*100;
areaMemproperty :=round((areaMemberCount-areaMemproperty)/areaMemberCount,4)*100;
areaNation:=round((areaMemberCount-areaNation)/areaMemberCount,4)*100;
areaMembersex:=round((areaMemberCount-areaMembersex)/areaMemberCount,4)*100;
areaCulturedegree:=round((areaMemberCount-areaCulturedegree)/areaMemberCount,4)*100;
areaMarry:=round((areaMemberCount-areaMarry)/areaMemberCount,4)*100;
areaFamilystate:=round((areaMemberCount-areaFamilystate)/areaMemberCount,4)*100;
end if;
open cur_tjview for select * from TJ_VIEW tj where rownum = 1 and tj.code = areacode_info.area_code_real;
loop
fetch cur_tjview into tjview_info;
exit when cur_tjview%notfound;
--,ZEROCHILDCNT,ONECHILDCNT,TWOCHILDCNT,THREECHILDCNT,MORECHILDCNT
update TJ_VIEW tv set
tv.zerochildcount=to_char(decode(zeroChildall,0,100,round(decode(zeroChildall,null,0,zeroChildCnt/zeroChildall),4)*100),'9990.99'),
tv.onechildcount=to_char(decode(oneChildall,0,100,round(decode(oneChildall,null,0,oneChildCnt/oneChildall),4)*100),'9990.99'),
tv.twochildcount=to_char(decode(twoChildall,0,100,round(decode(twoChildall,null,0,twoChildCnt/twoChildall),4)*100),'9990.99'),
tv.threechildcount=to_char(decode(threeChildall,0,100,round(decode(threeChildall,null,0,threeChildCnt/threeChildall),4)*100),'9990.99'),
tv.morechildcount=to_char(decode(morechildall,0,100,round(decode(morechildall,null,0,moreChildCnt/morechildall),4)*100),'9990.99'),
tv.areawomenlogic=to_char(decode(areaWomen,0,100,round(decode(areaWomen,null,0,(areaWomen-areaWLogic)/areaWomen),4)*100),'9990.99'),
--execute immediate areaTjView using areaNullPer,areaCard,tjview_info.vid;
commit;
--DBMS_OUTPUT.put_line(spedxj);
--DBMS_OUTPUT.put_line(spedbj);
end loop;
close cur_tjview;
insert into TJ_CZRK (AREA,HURELATION,NAME,SEX,BRITH,JZD,HJD,HUXZ,CARD,MZ,WH,ZZ,HY,WORK,LKDATE,LRDATE,HJZT,WZL,ZS,CODE,AID,FACODE) values (areacode_info.area_name,areaRelation,areaName,areaMembersex,areaMemberbirthday,areaAddresscode,areaFamliyaddress,areaMemproperty,areaCard,areaNation,areaCulturedegree,'',areaMarry,'','','',areaFamilystate,'','',areacode_info.area_code_real,sys_guid(),areacode_info.area_parentcode);
--execute immediate areaTjCzrk using areacode_info.area_name,areaRelation,areaName,areaMembersex,areaMemberbirthday,areaAddresscode,areaFamliyaddress,areaMemproperty,areaCard,areaNation,areaCulturedegree,'',areaMarry,'','','',areaFamilystate,'','',areacode_info.area_code_real,sys_guid(),'';
commit;
end loop;
close cur_areacode;
end loop;
DBMS_OUTPUT.put_line('end...');
end execute_report_tj_xjdc03;
job BINARY_INTEGER;
v_begin DATE;
v_interval VARCHAR2 (50);
BEGIN
v_begin := sysdate;
v_interval := 'TRUNC(SYSDATE + 1) + (21*60)/(24*60)';
DBMS_JOB.submit (job,
'execute_report_tj_xjd;',
v_begin,
v_interval,
FALSE,
0,
FALSE
);
DBMS_OUTPUT.put_line ('JOB(任务)编号:' || job);
COMMIT;
END;
就是从第2天开始,每天21点执行罢。那就设置成sysdate + 21/24得了罢。再有将后3个参数去掉,试一下。