exit when cursor%notfound 放在fetch的前面会比放在fetch后面多loop一个次

when the named cursor is opened, the pointer will be positioned to the first row of the result set, if exists. The cursor%notfound will return NULL, which has no affect to statement "exit when cursor%notfound" for the loop will not exit until cursor%notfound is evaluted as TRUE. The FETCH statemetn will retrieve the current row and move the pointer to the next row.  The cursor%notfound will return FALSE if the most recent fetch from the named cursor returned a row. So the best position for statement "exit when cursor%notfound" is immediated follow a FETCH cursor.  If  "exit when cursor%notfound" is put before FETCH, the last loop will process the row after the last row of the result set, which is NULL. PL/SQL will not raise an exception when fetch statement returns no rows.

 测试：

a.建表

create table tb_student(
student_id number,--学号
student_name varchar2(30),
student_sex char(1),--性别 F/M
student_age number,--年龄
student_birthday date,--学生出生日期
student_address varchar2(50),--住址
student_desc varchar2(200),--备注
constraint pk_student primary key(student_id)
);

 

 b.插入数据

insert into tb_student(student_id, student_name, student_sex, student_age, student_birthday) 
  values (seq_student.nextval, '高红成', 'F', '26', to_date('1971-02-12', 'yyyy-MM-dd') );
insert into tb_student(student_id, student_name, student_sex, student_age, student_birthday) 
 values (seq_student.nextval, '李艳', 'F','25', to_date('1981-05-09', 'yyyy-MM-dd') );
insert into tb_student(student_id, student_name, student_sex, student_age, student_birthday) 
 values (seq_student.nextval, '高雄', 'M','21', to_date('1981-05-09', 'yyyy-MM-dd') );
insert into tb_student(student_id, student_name, student_sex, student_age, student_birthday) 
 values (seq_student.nextval, '彭传志', 'M','22', to_date('1981-05-09', 'yyyy-MM-dd') );

 c.语句块

--判断是不是null，要用is 而不能是"="
--判断是不是true,用"=",而不是is 
declare
cursor cur_name is select student_name from tb_student t;
tempName tb_student.student_name%type;
begin
  open cur_name;
     loop
         if cur_name%notfound is null then --第一执行到这里的时候 cur_name%notfound为null
             dbms_output.put_line('null');
         end if;
         if cur_name%notfound = true then
             dbms_output.put_line('true');
         end if;
        if cur_name%notfound = false then
             dbms_output.put_line('false');
        end if;
     -- 1.当游标指针移动到最后一条记录时，就不会再移动了。
     -- 2.因为%notfound是根据最近的一次fetch来判断的，因此当输出最后一条记录后,还会在输出最一条记录，即最后一条记录将输出2遍。
     -- 3.If  "exit when cursor%notfound" is put before FETCH, the last loop will process the row after the last row of the result set, which is NULL. 
     --   PL/SQL will not raise an exception when fetch statement returns no rows.
     --   最后一次循环将处理在"结果集最后一行"的下一行,该行为NULL,当fetch的时候PL/SQL将不会抛出异常，并且不会覆盖最后一次循环中赋予tempName的值，即tempName会保留最后一次被赋予的值
     
     exit when cur_name%notfound;--当cur_name%notfound为null的时候，并不会退出。直到为true才会退出
        fetch cur_name into tempName; --当游标指针移动到 最后一条 记录后，就不会再移动了，还是指向最后一条记录，因此最后一条记录才会输出2遍
        if cur_name%notfound is null then
           dbms_output.put_line('null...');
        end if;
        if cur_name%notfound = true then
           dbms_output.put_line('true...');
        end if;
        if cur_name%notfound = false then
           dbms_output.put_line('false...');
        end if;
        dbms_output.put_line(tempName);
   end loop;
  close cur_name;
end;

 

2.为什么"exit when cur_name%notfound;" 需要与fetch 配合使用呢？

因为如果不使用fetch语句的话 "cur_name%notfound"将始终为null，将进入死循环

declare
cursor cur_name is select student_name from tb_student t;
tempName tb_student.student_name%type;
begin
open cur_name;
 loop
  exit when cur_name%notfound;
  tempName := 'gaoweigang';
  dbms_output.put_line(tempName);
  end loop;
close cur_name;--修改后
end;