CREATE OR REPLACE PROCEDURE create_ips_task_time_control as
cursor v_cursor is select REGION_CODE from ips_region_code_tbl where region_type='01' and region_grade=2;
v_id varchar2(20);
begin
open v_cursor
loop
FETCH v_cursor INTO v_id;
IF (v_cursor%NOTFOUND)
THEN
DBMS_OUTPUT.PUT_LINE('last'||v_id);
EXIT ;
END IF;
insert into tmp_ips_task_time_control set_type values(v_id);
end loop;
close v_cursor;
end;这段脚本有什么错误呢,报下面的错误,帮我看看,谢谢
Compilation errors for PROCEDURE CHSREP.CREATE_IPS_TASK_TIME_CONTROLError: PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
. ( % ; for
The symbol "; was inserted before "LOOP" to continue.
Line: 6
Text: loopError: PLS-00103: Encountered the symbol "CLOSE"
Line: 15
Text: close v_cursor;
cursor v_cursor is select REGION_CODE from ips_region_code_tbl where region_type='01' and region_grade=2;
v_id varchar2(20);
begin
open v_cursor
loop
FETCH v_cursor INTO v_id;
IF (v_cursor%NOTFOUND)
THEN
DBMS_OUTPUT.PUT_LINE('last'||v_id);
EXIT ;
END IF;
insert into tmp_ips_task_time_control set_type values(v_id);
end loop;
close v_cursor;
end;这段脚本有什么错误呢,报下面的错误,帮我看看,谢谢
Compilation errors for PROCEDURE CHSREP.CREATE_IPS_TASK_TIME_CONTROLError: PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
. ( % ; for
The symbol "; was inserted before "LOOP" to continue.
Line: 6
Text: loopError: PLS-00103: Encountered the symbol "CLOSE"
Line: 15
Text: close v_cursor;
象我下面这样写.
下面的脚本执行没有反映,不知道是那里出错拉!--初始化任务时效表(ips_task_time_control)
DECLARE
v_regionCode ips_region_code_tbl.REGION_CODE%TYPE;
v_investigateType ips_task_time_control.INVESTIGATE_TYPE%TYPE;
CURSOR c1 IS select REGION_CODE from ips_region_code_tbl where region_type='01' and region_grade=2;
CURSOR c2 IS select id from investigate_type_tbl;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_regionCode;
EXIT WHEN c1%NOTFOUND;
open c2;
LOOP
FETCH c2 into v_investigateType;
EXIT WHEN c2%NOTFOUND;
insert into ips_task_time_control (no,set_type,branch_code,investigate_type,value,update_user,update_date) values(SEQ_IPS_OPERATE_REGION.NEXTVAL,'1',v_regionCode,v_investigateType,15,'admin',current_date);
END LOOP;
END LOOP;
CLOSE c1;
CLSOE c2;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
--可以的
不会是数据太多了,时间很长吧
begin
for p in (select REGION_CODE from ips_region_code_tbl where region_type='01' and region_grade=2)
loop
for q in(select id from investigate_type_tbl)
loop
insert into ips_task_time_control (no,set_type,branch_code,investigate_type,value,update_user,update_date) values(SEQ_IPS_OPERATE_REGION.NEXTVAL,'1',p.region_code,q.id,15,'admin',current_date);
end loop;
end loop;
end;
在网上看到有这样的写法,这样有没有问题呀,为什么执行后,没有反映,没有插进数据呀
begin
for p in (select REGION_CODE from ips_region_code_tbl where region_type='01' and region_grade=2)
loop
for q in(select id from investigate_type_tbl)
loop
insert into ips_task_time_control (no,set_type,branch_code,investigate_type,value,update_user,update_date) values(SEQ_IPS_OPERATE_REGION.NEXTVAL,'1',p.region_code,q.id,15,'admin',current_date);
end loop;
end loop;
end;
--看上去没有问题,不会使你调用的地方没有提交吧
为什么在csdn上看不到自己的回复呢,奇怪
加上commit