我现在需要写很多存储过程,但是每个存储过程的执行时间大约在二十几个小时,为了保证不因断电等原因而导致存储过程的执行失败而导致所有数据丢失,我在存储过程中加了一个commit,但是在代码执行时,我并没有从表中查到数据,是我的代码错了吗?如果错了,该怎么写,请大虾们指点一哈^
代码如下:CREATE OR REPLACE PROCEDURE yjp_select1_2(begin_year integer,end_year integer)
IS
cursor my_cursor is
select * from pension.yjp_jbjg;
my_zone pension.yjp_jbjg%rowtype;
year_month varchar(10);
add_person integer;
sub_person integer;
BEGIN
open my_cursor;
fetch my_cursor into my_zone;
while my_cursor%found loop
for i in begin_year..end_year loop
for j in 1..12 loop
IF j<10 THEN
year_month:=to_char(i)||'0'||to_char(j);
ELSE
year_month:=to_char(i)||to_char(j);
END IF;
select count(distinct pension.jf3.AAC001) into add_person
from pension.jf3
where substr(to_char(pension.jf3.AIC162),0,6) = year_month
and pension.jf3.aab301=my_zone.jbjg_bh;
select count(distinct pension.jf3.AAC001) into sub_person
from pension.jf3,pension.ic13
where pension.jf3.aac001=pension.ic13.aac001
and pension.ic13.AIC232=year_month
and pension.jf3.aab301=my_zone.jbjg_bh;
insert into pension.yjp_t1_2 values(my_zone.jbjg_bh,year_month,add_person,sub_person);
end loop;
end loop;
fetch my_cursor into my_zone;
end loop;
close my_cursor;
END yjp_select1_2;
代码如下:CREATE OR REPLACE PROCEDURE yjp_select1_2(begin_year integer,end_year integer)
IS
cursor my_cursor is
select * from pension.yjp_jbjg;
my_zone pension.yjp_jbjg%rowtype;
year_month varchar(10);
add_person integer;
sub_person integer;
BEGIN
open my_cursor;
fetch my_cursor into my_zone;
while my_cursor%found loop
for i in begin_year..end_year loop
for j in 1..12 loop
IF j<10 THEN
year_month:=to_char(i)||'0'||to_char(j);
ELSE
year_month:=to_char(i)||to_char(j);
END IF;
select count(distinct pension.jf3.AAC001) into add_person
from pension.jf3
where substr(to_char(pension.jf3.AIC162),0,6) = year_month
and pension.jf3.aab301=my_zone.jbjg_bh;
select count(distinct pension.jf3.AAC001) into sub_person
from pension.jf3,pension.ic13
where pension.jf3.aac001=pension.ic13.aac001
and pension.ic13.AIC232=year_month
and pension.jf3.aab301=my_zone.jbjg_bh;
insert into pension.yjp_t1_2 values(my_zone.jbjg_bh,year_month,add_person,sub_person);
end loop;
end loop;
fetch my_cursor into my_zone;
end loop;
close my_cursor;
END yjp_select1_2;
解决方案 »
- oracle自增列问题 求高手解答
- 在eclipse中连接oracle怎么老是报“The Network Adapter could not establish the connection”
- oracle数据库出错,请高手,高手,高高手解决!在线等待!
- rownum的效率问题
- 有关数据锁定的问题
- 请教一下:如果将系统时间精确到毫秒,谢谢!
- 我是新手别笑我-给100
- 在配置侦听时,即在listener.ora中,host中具体的含义是指?
- 关于自动导入DMP文件的问题
- DATE问题!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 数据同步
- 在线等 急急 插入7億条测试数据的方案 方法 给高分
insert into pension.yjp_t1_2 values(my_zone.jbjg_bh,year_month,add_person,sub_person);
commit;
end loop;
这样最内层循环执行一次就会提交一次