oracle 游标中抛出异常的处理方式

在oracle游标的使用中，用for循环是一种较直接open 游标然后关闭游标更好的应用方式。现在写两个存储过程，验证这两种情况下游标中抛出异常后游标是否正常关闭。

现在有一张表emp，表结构如下：

SQL> describe emp;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ------------

 ID                                                 NUMBER(38)
 USER_NAME                                          VARCHAR2(20)
 SALARY                                             NUMBER(38)
 EMP_DEPTNO                                         NUMBER(38)

表中的数据如下：

SQL> select * from emp;

        ID USER_NAME                SALARY EMP_DEPTNO
---------- -------------------- ---------- ----------
         1 Zhangsan                   5200         10
         2 Lisi                      11500         20
         3 Wangwu                    13800         30
         4 Qianliu                   12300         20
         5 Chenqi                    14700         30

现在在表中查找，如果找到Lisi，就抛出一个异常，用来查看游标在异常抛出中是否正确关闭。

用for循环实现如下：

create or replace procedure cursor_exception1 as
       cursor c_emp is select * from emp;
       name_exception exception;
       begin
         for item in c_emp loop
           if(item.user_name='Lisi') then
             raise name_exception;
           end if;
         end loop;
       exception
         when name_exception then
           open c_emp; -- 再次代开游标，看是否报错，如果没有报错，证明进入exception之前已经正确关闭
           close c_emp;
           dbms_output.put_line('find name Lisi');
         when others then
           dbms_output.put_line('Other error occured');
       end cursor_exception1;
/

调用该存储过程并查看输出结果：

SQL> call cursor_exception1();
find name Lisi

 

用普通的loop实现：

create or replace procedure cursor_exception2 is
  cursor cursor_emp is
    select * from emp;
  emp_row emp%rowtype;
  name_exception exception;
begin
  open cursor_emp;
  loop
    fetch cursor_emp
      into emp_row;
    if (emp_row.user_name = 'Lisi') then
      raise name_exception;
    end if;
    if (cursor_emp%notfound) then
      exit;
    end if;
  end loop;
  close cursor_emp;
exception
  when name_exception then
    open cursor_emp;    -- 再次代开游标，看是否报错，如果没有报错，证明进入exception之前已经正确关闭
    close cursor_emp;
    dbms_output.put_line('Find name Lisi');
  when others then
    dbms_output.put_line('Other error occured');
end;
/

调用并查看输出结果：

SQL> call cursor_exception2();
call cursor_exception2()
     *
第 1 行出现错误:
ORA-06511: PL/SQL: 游标已经打开
ORA-06512: 在 "SYS.CURSOR_EXCEPTION2", line 3
ORA-06512: 在 "SYS.CURSOR_EXCEPTION2", line 21
ORA-06510: PL/SQL: 用户定义的异常错误未得到处理