为什么数据插不进去呢,帮忙看看,谢谢!
现在要初始化数据库中的记录,有两个表的记录是从其他表过来的,写了个程序插入数据库中表的记录,如下方法,编译,运行都没有问题,为什么数据就是插不进去呢?大家帮忙看看,谢谢
方式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 as
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;
[解决办法]
//在你查询有结果的情况下 这样应该是没有问题的，不过你的操作效率是个问题
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
savepoint s1;//////////////
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;
return;/////////
EXCEPTION
WHEN OTHERS THEN
ROLLBACK s1;///////
END;
[解决办法]
执行有错误吗？你的sql应该没有问题
[解决办法]
把你的异常处理先去掉，看看有没有异常，，再确认一下你的insert 是否执行了dbms_output输出一下看看
[解决办法]
第二个方法没有问题，第一个不是特别正确，关键是两个方式都没有commit数据，所以插入了楼主也看不到
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 completed
SQL> select * from t;
TABLE_NAME UDATE
-------------------- -----------
TEMP_RISKTYPETONAME 2007-4-18 1
TEMP_RISKTYPETORISK 2007-4-18 1
TESTZHANG1 2007-4-18 1
[解决办法]
看看表的状态是不是unused
[解决办法]
step1, remove the try...catch because if there is an exception occured,routine will jump to exception handle module.
step2, check the result of select...
step3, try to commit immediately after insert
step4, check whether the table was locked ...........