现在要初始化数据库中的记录,有两个表的记录是从其他表过来的,写了个程序插入数据库中表的记录,如下方法,编译,运行都没有问题,为什么数据就是插不进去呢?大家帮忙看看,谢谢方式1: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_task_time.nextval,'1',v_regionCode,v_investigateType,15,'admin',current_date); END LOOP;
END LOOP;
CLOSE c1;
CLOSE c2;
COMMIT;EXCEPTION WHEN OTHERS THEN ROLLBACK;END; 方法2:create or replace procedure test asbeginfor 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;
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_task_time.nextval,'1',v_regionCode,v_investigateType,15,'admin',current_date); END LOOP;
END LOOP;
CLOSE c1;
CLOSE c2;
COMMIT;EXCEPTION WHEN OTHERS THEN ROLLBACK;END; 方法2:create or replace procedure test asbeginfor 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;
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
savepoint s1;//////////////
OPEN c1;
LOOP
FETCH c1 INTO v_regionCode;
EXIT WHEN c1%NOTFOUND;
open c2;
LOOPFETCH 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_task_time.nextval,'1',v_regionCode,v_investigateType,15,'admin',current_date);END LOOP;
END LOOP;
CLOSE c1;
CLOSE c2;
COMMIT;return;/////////EXCEPTIONWHEN OTHERS THENROLLBACK s1;///////END;
SQL> select * from t;TABLE_NAME UDATE
-------------------- -----------SQL>
SQL> begin
2 for p in (select object_name from user_objects where object_type = 'TABLE' )
3 loop
4 for q in(select table_name from user_tables where table_name like 'T%' and length(table_name) <= 20)
5 loop
6 insert into t (table_name, uDate) values(q.table_name, sysdate);
7 commit;
8 end loop;
9 end loop;
10 end;
11 /PL/SQL procedure successfully completedSQL> select * from t;TABLE_NAME UDATE
-------------------- -----------
TEMP_RISKTYPETONAME 2007-4-18 1
TEMP_RISKTYPETORISK 2007-4-18 1
TESTZHANG1 2007-4-18 1
step2, check the result of select...
step3, try to commit immediately after insert
step4, check whether the table was locked ...........