Exception笔记

异常(exception)
 |
 |_______预定义异常(21种预定义标识符)     ____no_data_found
 |        |                              |____too_many_rows
 |        |__常见预定义标识符——————|____collection_is_null
 |                                       |____invalid_cursor
 |                                       |____cursor_already_open
 |                                       |____others
 |                                       |____ ........
 |
 |
 |_______非预定义异常（在DECLARE部分新增异常标识符并指定标识符和实际错误号之间的关联，
 |            再在EXCEPTION部分捕获该异常，可理解为21之外的预定义异常扩展）
 |
 |
 |_______自定义异常（处理与ORACLE错误无关的异常，在DECLARE部分仅需定义异常标识符，
 |           在BEGIN部分根据条件抛出错误，最后在EXCEPTION部分捕获异常）
 |
 |
 |
 |_______使用异常处理函数（SQLCODE、SQLERRM、RAISE_APPLICATION_ERROR）
 |
 |
 |
 |_______＊＊使用编译警告





＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝<<预定义异常>>＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
declare
        cursor emp_cursor is select * from emp;
        emp_record scott.emp%rowtype;
begin
        --close emp_cursor;
        --open emp_cursor;
        open emp_cursor;
        loop
                fetch emp_cursor into emp_record;
                exit when emp_cursor%notfound;
                dbms_output.put_line(emp_record.ename);
        end loop;
        close emp_cursor;
exception
        when cursor_already_open then
        dbms_output.put_line('The cursor is already open......');
        when invalid_cursor then
        dbms_output.put_line('Invalid cursor......');
end;
/


＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝<<非预定义异常>>＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝


在没有定义异常时删除scott.dept中的行会报错:
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
ORA-06512: at line 6

定义了预定义异常之后：
SYS@uplookin>> ed
Wrote file afiedt.buf

  1  declare
  2  dept_integrity exception;
  3  pragma exception_init(dept_integrity,-02292)
  4  ;
  5  begin
  6  delete from scott.dept where deptno=10;
  7  exception
  8  when dept_integrity then
  9  dbms_output.put_line('eeeeeeeeeeeeee.............');
 10* end;
SYS@uplookin>> /
eeeeeeeeeeeeee.............

PL/SQL procedure successfully completed.


＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝<<自定义异常>>＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝


比如update或者delete没有作用行时，no_data_found异常并不会被触发：

SCOTT@uplookin>> ed
Wrote file afiedt.buf

  1  declare
  2     v_ename scott.emp.ename%type :='&ename';
  3     v_sal scott.emp.sal%type :=&sal;
  4  begin
  5     update scott.emp set sal=v_sal where ename=v_ename;
  6  exception
  7     when no_data_found then
  8     dbms_output.put_line('No such employee......');
  9* end;
SCOTT@uplookin>> /
Enter value for ename: nobody
Enter value for sal: 9999

PL/SQL procedure successfully completed.

自定义异常之后：

SCOTT@uplookin>> ed
Wrote file afiedt.buf

  1  declare
  2     v_ename scott.emp.ename%type :='&ename';
  3     v_sal scott.emp.sal%type :=&sal;
  4     no_update EXCEPTION;
  5  begin
  6     update scott.emp set sal=v_sal where ename=v_ename;
  7     IF SQL%NOTFOUND THEN
  8             RAISE no_update;
  9     END IF;
 10  exception
 11     when no_data_found then
 12     dbms_output.put_line('No such employee......');
 13     when no_update then
 14     dbms_output.put_line('Do nothing......');
 15* end;
SCOTT@uplookin>> /
Enter value for ename: KING
Enter value for sal: 9999

PL/SQL procedure successfully completed.

SCOTT@uplookin>> /
Enter value for ename: nobody
Enter value for sal: 1234
Do nothing......

PL/SQL procedure successfully completed.


＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝异常处理函数<>＝＝＝＝＝＝＝＝＝＝＝＝＝＝


SCOTT@uplookin>> begin
  2  delete from dept where deptno=10;
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
ORA-06512: at line 2


SCOTT@uplookin>> begin
  2  delete from dept where deptno=10;
  3  exception
  4  when others then
  5  dbms_output.put_line('Error_Number...'||SQLCODE);
  6  dbms_output.put_line('Error_Message...'||SQLERRM);
  7  end;
  8  
  9  /
Error_Number...-2292
Error_Message...ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found

PL/SQL procedure successfully completed.


==================<>===================


SCOTT@uplookin>> begin
  2  delete from dept where deptno=&deptno;
  3  if sql%notfound then
  4  RAISE_APPLICATION_ERROR(-20000,'No such department....');
  5  end if;
  6  end;
  7  /
Enter value for deptno: 88
begin
*
ERROR at line 1:
ORA-20000: No such department....
ORA-06512: at line 4


SCOTT@uplookin>>


====================================================================================
练习：

SCOTT@uplookin>> ED
Wrote file afiedt.buf

  1  declare
  2      v_empno scott.emp.empno%type :=&empno;
  3      v_newdept scott.emp.deptno%type :=&newdept;
  4      no_body EXCEPTION;
  5      emp_integrity EXCEPTION;
  6      PRAGMA EXCEPTION_INIT(emp_integrity,-02291);
  7  begin
  8      update scott.emp set deptno=v_newdept where empno=v_empno;
  9      if sql%notfound then
 10          RAISE no_body;
 11      end if;
 12  exception
 13      when no_body then
 14      dbms_output.put_line('No such employee...............');
 15      when emp_integrity then
 16      dbms_output.put_line('No such department...............');
 17* end;
SCOTT@uplookin>> /
Enter value for empno: 7788
Enter value for newdept: 40

PL/SQL procedure successfully completed.

SCOTT@uplookin>> /
Enter value for empno: 6666
Enter value for newdept: 30
No such employee...............

PL/SQL procedure successfully completed.

SCOTT@uplookin>> /
Enter value for empno: 7788
Enter value for newdept: 55
No such department...............

PL/SQL procedure successfully completed.