完整的存储过程create or replace procedure create_people_number(updateCounts out number, cityNumber in varchar2,countyCode in varchar2,townNumber in varchar2,uploadNumber in varchar2) is --定义局部变量
tempPeople t_people_upload_detail%rowtype;
doSql varchar2(2000);
birthday varchar2(20);
birthdayNumber varchar2(20);
sex varchar2(2);
maxNumber varchar2(10);
intMaxNumber number;
newNumber varchar2(10);
newPeopleNumber varchar2(20);
peopleNumberCounts number;
--声明游标
cursor getUploadPeople(cityNumber2 in varchar2,townNumber2 in varchar2,uploadNumber2 in varchar2)
is select a.* from t_people_upload_detail a where a.city_number=cityNumber2 and a.town_number=townNumber2
and a.upload_number=uploadNumber2 and a.state='0' and a.error_type='0';
begin
--打开游标,对其中找到的记录进行遍历
open getUploadPeople(cityNumber,townNumber,uploadNumber);
dbms_output.put_line('行数'||getUploadPeople%rowcount);
while getUploadPeople%found loop --遍历开始
fetch getUploadPeople into tempPeople;
birthday := tempPeople.Birthday;
birthdayNumber := substr(birthday,1,4)+substr(birthday,6,2)+substr(birthday,9,2);
sex := tempPeople.Sex;
if sex='2' or sex='9' then
sex:='2';
end if;
--通过县国家编码、出生日、性别获取最大流水号
begin doSql := 'select max_number from t_sequence_record
where county_code=''' || countyCode || ''' and birthday=''' || birthday || '''
and sex=''' || sex || ''' and rownum=1';
execute immediate doSql into maxNumber;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
end;
if maxNumber is not null then --最大流水号已存在
--循环获取最大编号
loop
intMaxNumber := cast(maxNumber as number)+2;
if intMaxNumber<10 then
newNumber := '00'||cast(intMaxNumber as varchar2);
elsif intMaxNumber<100 then
newNumber := '0'||cast(intMaxNumber as varchar2);
else
newNumber := cast(intMaxNumber as varchar2);
end if;
--组合新个人编号
newPeopleNumber :=countyCode||birthdayNumber||newNumber||'H';
--检测该个人编号是否已存在
select count(1) into peopleNumberCounts from t_people_info
where people_number=newPeopleNumber and city_number=cityNumber;
exit when peopleNumberCounts>0 and cast(newNumber as number)>999;
maxNumber :=newNumber;
end loop;
--更新最大编号
doSql:='update t_sequence_record set max_number=''' || newNumber || ''',modify_time=''' || to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '''
where where county_code=''' || countyCode || ''' and birthday=''' || birthday || '''
and sex=''' || sex || '''';
dbms_output.put_line(doSql);
execute immediate doSql;
doSql := '';
else
if sex='1' then
newNumber := '001';
else
newNumber := '002';
end if;
--组合新个人编号
newPeopleNumber :=countyCode||birthdayNumber||newNumber||'H';
--插入新的流水号记录
doSql:='insert into t_sequence_record(county_code,birthday,sex,max_number,creator_time)
values('''||countyCode||''','''||birthday||''','''||sex||''','''||newNumber||''','''||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')||''')';
dbms_output.put_line(doSql);
execute immediate doSql;
doSql := '';
end if;
if cast(newNumber as number)<=999 then
--更新人口资料导入临时表的个人编号
doSql:='update t_people_upload_detail set people_number='''||newPeopleNumber||'''
where id='||tempPeople.id||' city_number='''||cityNumber||''' ';
dbms_output.put_line(doSql);
execute immediate doSql;
doSql := '';
updateCounts := updateCounts+1;
end if;
end loop; --遍历结束
close getUploadPeople;
end;
用到游标的那段代码,调试时输入参数,这些参数查表是有数据的,但用在这存储过程中,查出来返回的游标rowcount=0
--声明游标
cursor getUploadPeople(cityNumber2 in varchar2,townNumber2 in varchar2,uploadNumber2 in varchar2)
is select a.* from t_people_upload_detail a where a.city_number=cityNumber2 and a.town_number=townNumber2
and a.upload_number=uploadNumber2 and a.state='0' and a.error_type='0';
begin
--打开游标,对其中找到的记录进行遍历
open getUploadPeople(cityNumber,townNumber,uploadNumber);
dbms_output.put_line('行数'||getUploadPeople%rowcount);
各位兄弟帮我看看什么原因造成的?
tempPeople t_people_upload_detail%rowtype;
doSql varchar2(2000);
birthday varchar2(20);
birthdayNumber varchar2(20);
sex varchar2(2);
maxNumber varchar2(10);
intMaxNumber number;
newNumber varchar2(10);
newPeopleNumber varchar2(20);
peopleNumberCounts number;
--声明游标
cursor getUploadPeople(cityNumber2 in varchar2,townNumber2 in varchar2,uploadNumber2 in varchar2)
is select a.* from t_people_upload_detail a where a.city_number=cityNumber2 and a.town_number=townNumber2
and a.upload_number=uploadNumber2 and a.state='0' and a.error_type='0';
begin
--打开游标,对其中找到的记录进行遍历
open getUploadPeople(cityNumber,townNumber,uploadNumber);
dbms_output.put_line('行数'||getUploadPeople%rowcount);
while getUploadPeople%found loop --遍历开始
fetch getUploadPeople into tempPeople;
birthday := tempPeople.Birthday;
birthdayNumber := substr(birthday,1,4)+substr(birthday,6,2)+substr(birthday,9,2);
sex := tempPeople.Sex;
if sex='2' or sex='9' then
sex:='2';
end if;
--通过县国家编码、出生日、性别获取最大流水号
begin doSql := 'select max_number from t_sequence_record
where county_code=''' || countyCode || ''' and birthday=''' || birthday || '''
and sex=''' || sex || ''' and rownum=1';
execute immediate doSql into maxNumber;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
end;
if maxNumber is not null then --最大流水号已存在
--循环获取最大编号
loop
intMaxNumber := cast(maxNumber as number)+2;
if intMaxNumber<10 then
newNumber := '00'||cast(intMaxNumber as varchar2);
elsif intMaxNumber<100 then
newNumber := '0'||cast(intMaxNumber as varchar2);
else
newNumber := cast(intMaxNumber as varchar2);
end if;
--组合新个人编号
newPeopleNumber :=countyCode||birthdayNumber||newNumber||'H';
--检测该个人编号是否已存在
select count(1) into peopleNumberCounts from t_people_info
where people_number=newPeopleNumber and city_number=cityNumber;
exit when peopleNumberCounts>0 and cast(newNumber as number)>999;
maxNumber :=newNumber;
end loop;
--更新最大编号
doSql:='update t_sequence_record set max_number=''' || newNumber || ''',modify_time=''' || to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '''
where where county_code=''' || countyCode || ''' and birthday=''' || birthday || '''
and sex=''' || sex || '''';
dbms_output.put_line(doSql);
execute immediate doSql;
doSql := '';
else
if sex='1' then
newNumber := '001';
else
newNumber := '002';
end if;
--组合新个人编号
newPeopleNumber :=countyCode||birthdayNumber||newNumber||'H';
--插入新的流水号记录
doSql:='insert into t_sequence_record(county_code,birthday,sex,max_number,creator_time)
values('''||countyCode||''','''||birthday||''','''||sex||''','''||newNumber||''','''||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')||''')';
dbms_output.put_line(doSql);
execute immediate doSql;
doSql := '';
end if;
if cast(newNumber as number)<=999 then
--更新人口资料导入临时表的个人编号
doSql:='update t_people_upload_detail set people_number='''||newPeopleNumber||'''
where id='||tempPeople.id||' city_number='''||cityNumber||''' ';
dbms_output.put_line(doSql);
execute immediate doSql;
doSql := '';
updateCounts := updateCounts+1;
end if;
end loop; --遍历结束
close getUploadPeople;
end;
用到游标的那段代码,调试时输入参数,这些参数查表是有数据的,但用在这存储过程中,查出来返回的游标rowcount=0
--声明游标
cursor getUploadPeople(cityNumber2 in varchar2,townNumber2 in varchar2,uploadNumber2 in varchar2)
is select a.* from t_people_upload_detail a where a.city_number=cityNumber2 and a.town_number=townNumber2
and a.upload_number=uploadNumber2 and a.state='0' and a.error_type='0';
begin
--打开游标,对其中找到的记录进行遍历
open getUploadPeople(cityNumber,townNumber,uploadNumber);
dbms_output.put_line('行数'||getUploadPeople%rowcount);
各位兄弟帮我看看什么原因造成的?
在PL/SQL 右键点击该存储过程的“test”菜单,进入测试界面,在下面variable输入参数,按“start debugger”按钮,一步一步地走下去。
dbms_output.put_line('行数'||getUploadPeople%rowcount);
while getUploadPeople%found loop --遍历开始
fetch getUploadPeople into tempPeople;改成
open getUploadPeople(cityNumber,townNumber,uploadNumber);
fetch getUploadPeople into tempPeople;
while getUploadPeople%found loop --遍历开始
fetch getUploadPeople into tempPeople;
dbms_output.put_line('行数'||getUploadPeople%rowcount); --这个放在循环体的最后
太感谢了,每执行一次fetch getUploadPeople into tempPeople;就是迭代一条数据?
%FOUND Attribute: Has a Row Been Fetched?After a cursor or cursor variable is opened but before the first fetch, %FOUND returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch did not return a row.