光标属性％notfound不起作用

Question

我的功能似乎应该返回正确的数据：

create or replace function baninst1.ab6_student_race(pidm number)
  return varchar2
is
  race varchar2(1);
  ethn varchar2(1);
  cursor c_student_race_codes is
    select prac1.gorprac_race_cde
    from general.gorprac prac1
    where prac1.gorprac_pidm = pidm;
begin
  select pers1.spbpers_ethn_cde
  into ethn
  from saturn.spbpers pers1
  where pers1.spbpers_pidm = pidm;
  if ethn = '2' then
    race := 'H';
  elsif (ethn <> 2 or ethn is null) then
    for r_student_race_codes in c_student_race_codes loop
      if c_student_race_codes%notfound then
        race := 'U';
      elsif c_student_race_codes%rowcount > 1 then
        race := 'M';
      else race := r_student_race_codes.gorprac_race_cde;
      end if;
    end loop;
  end if;
  return race;
  exception
    when others then
      dbms_output.put_line(sqlerrm);
end;

但是，当未找到记录时，它不是返回'u'，而是返回null。

我有一个使用：

的功能的工作版本

create or replace function baninst1.ab6_student_race(pidm number)
  return varchar2
is
  race varchar2(1);
  r_count number(1);
  ethn varchar2(1);
  cursor c_student_race_codes is
    select prac1.gorprac_race_cde as race_code
          ,count(prac1.gorprac_race_cde) as race_count
    from general.gorprac prac1
    where prac1.gorprac_pidm = pidm
    group by prac1.gorprac_race_cde;
begin
  select pers1.spbpers_ethn_cde
  into ethn
  from saturn.spbpers pers1
  where pers1.spbpers_pidm = pidm;
  if ethn = '2' then
    race := 'H';
  elsif (ethn <> 2 or ethn is null) then
    open c_student_race_codes;
    fetch c_student_race_codes into race, r_count;
    if c_student_race_codes%notfound then
      race := 'U';
    elsif r_count > 1 then
      race := 'M';
    end if;
    close c_student_race_codes;
  end if;
  return race;
  exception
    when others then
      dbms_output.put_line(sqlerrm);
end;

和另一个工作版本（尽管该版本似乎比上面的版本要慢）：

create or replace function baninst1.ab6_student_race(pidm number)
  return varchar2
is
  race varchar2(1);
  r_count number(1);
  ethn varchar2(1);
begin
  select pers1.spbpers_ethn_cde
  into ethn
  from saturn.spbpers pers1
  where pers1.spbpers_pidm = pidm;
  select count(prac1.gorprac_race_cde)
  into r_count
  from general.gorprac prac1
  where prac1.gorprac_pidm = pidm;
  if ethn = '2' then
    race := 'H';
  elsif (ethn <> 2 or ethn is null) then
    if r_count = 0 then
      race := 'U';
    elsif r_count > 1 then
      race := 'M';
    else
      select prac2.gorprac_race_cde
      into race
      from general.gorprac prac2
      where prac2.gorprac_pidm = pidm;
    end if;
  end if;
  return race;
  exception
    when others then
      dbms_output.put_line(sqlerrm);
end;

有人可以解释为什么％notund无法正常工作，因为我期望的是for循环？我正在使用Oracle 11g。

Answer 1

如果找不到记录，for循环永远不会执行，因此%notfound的检查永远不会执行。归功于GriffeyDog。