create or replace procedure test_import
(
yearChar in varchar2
) is
countNum integer :=0; ---计数变量
returnNum integer :=0;---存放出错返回代码
coun integer :=0; --循环计数
CURSOR school_cursor is select distinct(info.id)
from t_report_info reportinfo
left join t_school_info info on info.id = reportinfo.t_s_id
where reportinfo.BASEINFO_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and reportinfo.FINANCE_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and reportinfo.STUDENT_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and info.record_status = 1
and info.id not in (select t.t_s_id from t_student_school t)
and not exists (select 1 from t_student_school t where info.id = t.t_s_id)
and reportinfo.year = yearChar;
schooltype varchar2(36);
auditingSchoolNo integer :=0; --审核通过的学校的数量
school_count integer :=0; --已经在表中的数据的个数
student_cont integer :=0;
temp_num integer :=1;
begin
--查询是否有通过审核的学校
begin
select count(1)
into countNum
from t_student_school_info schstuinfo
left join t_school_info schoolinfo on schstuinfo.t_s_id = schoolinfo.id
left join t_report_info reportinfo on reportinfo.t_s_id = schoolinfo.id
where reportinfo.BASEINFO_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and reportinfo.FINANCE_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and reportinfo.STUDENT_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and schoolinfo.record_status = 1
and schstuinfo.year = yearChar;
--如若没有数据则会抛出异常
exception WHEN NO_DATA_FOUND THEN
begin
dbms_output.put_line(returnNum);
end;
end;
--统计通过审核的学校的数量
begin
select count(1)
into auditingSchoolNo
from t_report_info
where year = yearChar
and BASEINFO_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and FINANCE_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and STUDENT_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404';
select count(distinct(t_s_id))
into school_count
from t_student_school
where year = yearChar ;
if(auditingSchoolNo = school_count) then
begin
return;
end;
/*else
--EXECUTE IMMEDIATE 'truncate table t_student_school where year = yearChar';
begin
\*delete_temp(yearChar);
exception
when others then
return;*\
delete from t_student_school where year = yearChar or year is null;
end; */
end if;
end;
begin
/* select count(1) into coun from t_school_info;
if(coun > 0) then
return;
end if; */
--循环插入记录
for schoolid in school_cursor loop
begin
select scinfo.t_s_id into schooltype from t_school_info scinfo where scinfo.id = schoolid.id;
--首先插入学校记录
select count(1) into student_cont from t_student_school_info where t_s_id = schoolid.id and year = yearChar;
temp_num := 1;
.........
end loop;
for schoolid in school_cursor loop
.....
end loop
for schoolid in school_cursor loop
.....
end loop
第一个for循环可以正常执行
但是第二个和第三个都不执行,我单步进去以后,根本不进第二个和第三个for循环,请问是怎么回事啊
(
yearChar in varchar2
) is
countNum integer :=0; ---计数变量
returnNum integer :=0;---存放出错返回代码
coun integer :=0; --循环计数
CURSOR school_cursor is select distinct(info.id)
from t_report_info reportinfo
left join t_school_info info on info.id = reportinfo.t_s_id
where reportinfo.BASEINFO_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and reportinfo.FINANCE_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and reportinfo.STUDENT_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and info.record_status = 1
and info.id not in (select t.t_s_id from t_student_school t)
and not exists (select 1 from t_student_school t where info.id = t.t_s_id)
and reportinfo.year = yearChar;
schooltype varchar2(36);
auditingSchoolNo integer :=0; --审核通过的学校的数量
school_count integer :=0; --已经在表中的数据的个数
student_cont integer :=0;
temp_num integer :=1;
begin
--查询是否有通过审核的学校
begin
select count(1)
into countNum
from t_student_school_info schstuinfo
left join t_school_info schoolinfo on schstuinfo.t_s_id = schoolinfo.id
left join t_report_info reportinfo on reportinfo.t_s_id = schoolinfo.id
where reportinfo.BASEINFO_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and reportinfo.FINANCE_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and reportinfo.STUDENT_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and schoolinfo.record_status = 1
and schstuinfo.year = yearChar;
--如若没有数据则会抛出异常
exception WHEN NO_DATA_FOUND THEN
begin
dbms_output.put_line(returnNum);
end;
end;
--统计通过审核的学校的数量
begin
select count(1)
into auditingSchoolNo
from t_report_info
where year = yearChar
and BASEINFO_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and FINANCE_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404'
and STUDENT_AUDITING ='ab153f99-162f-44c7-bae7-39329ce51404';
select count(distinct(t_s_id))
into school_count
from t_student_school
where year = yearChar ;
if(auditingSchoolNo = school_count) then
begin
return;
end;
/*else
--EXECUTE IMMEDIATE 'truncate table t_student_school where year = yearChar';
begin
\*delete_temp(yearChar);
exception
when others then
return;*\
delete from t_student_school where year = yearChar or year is null;
end; */
end if;
end;
begin
/* select count(1) into coun from t_school_info;
if(coun > 0) then
return;
end if; */
--循环插入记录
for schoolid in school_cursor loop
begin
select scinfo.t_s_id into schooltype from t_school_info scinfo where scinfo.id = schoolid.id;
--首先插入学校记录
select count(1) into student_cont from t_student_school_info where t_s_id = schoolid.id and year = yearChar;
temp_num := 1;
.........
end loop;
for schoolid in school_cursor loop
.....
end loop
for schoolid in school_cursor loop
.....
end loop
第一个for循环可以正常执行
但是第二个和第三个都不执行,我单步进去以后,根本不进第二个和第三个for循环,请问是怎么回事啊
and not exists (select 1 from t_student_school t where info.id = t.t_s_id)
这个去掉就可以进去了