为什么数据插不进去呢,帮忙看看,谢谢!

[weichenggao]

现在要初始化数据库中的记录,有两个表的记录是从其他表过来的,写了个程序插入数据库中表的记录,如下方法,编译,运行都没有问题,为什么数据就是插不进去呢?大家帮忙看看,谢谢

方式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;

[hongqi162]

//在你查询有结果的情况下 这样应该是没有问题的，不过你的操作效率是个问题

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;

[weichenggao]

数据不多,是从其他两个基表读取数据初始化第3个表的信息,所以效率是不用担心的.
不过为什么数据插不进去呢

[CathySun118]

执行有错误吗？你的sql应该没有问题

[hongqi162]

把你的异常处理先去掉，看看有没有异常，，再确认一下你的insert 是否执行了dbms_output输出一下看看

[nojave]

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 ...........

[weichenggao]

对,没有提交
哎呀,丢人拉,谢谢大家,马上揭贴

[xiaoxiao1984]

第二个方法没有问题，第一个不是特别正确，关键是两个方式都没有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

[microsoft_fly]

看看表的状态是不是unused

[weichenggao]

select b.description||'-'||a.description, a.region_code

from ips_region_code_tbl a, ips_region_code_tbl b

where length(a.region_code)=6

and a.parent_region_code = b.region_code

connect by PRIOR a.region_code = a.parent_region_code

start with a.region_code='12'