Oracle数据库之异常

--异常处理机制
--一、异常处理简介   
--1 编译时异常；  
declare
 v_count number;
 begin
  select count(*) into v_count from emps;
    dbms_output.put_line(v_count);
 end;
--2 运行时异常；  这里的 &x  &y  是什么意思?
declare
 x number:=&x;
 y number:=&y;
 z number;
 begin
   z:=x+y;
   dbms_output.put_line(z);
   z:==x/y;
   dbms_output.put_line(z);
 end;
--由于x/y触发了异常，语句会立即跳转到异常处理事务部分，不会向下执行；如果不使用异常处理机制，就需要开发人员在代码中添加检查语句；
declare
 x number:=&x;
 y number:=&y;
 z number;
 begin
   z:=x+y;
   dbms_output.put_line(z);
   if y<>0 then
   z:=x/y;
   dbms_output.put_line(z);
 end;
--这是常见的异常处理方式，但是对于一个复杂的语句块来说，在代码中使用过多的if else语句会使整个代码块的可见性变差，难以维护；
declare
 x number:=9;
 y number:=0;
 z number;
 begin
   z:=x+y;
   dbms_output.put_line(z);
   z:=x/y;
   dbms_output.put_line(z);
   exception
     when zero_divide then
       dbms_output.put_line('令别特么往里面写');
 end;
--上面的代码将异常处理逻辑写在了exeption语句块中，将异常处理与程序逻辑分离，提供了更加清晰的代码，而且在exception语句块中捕捉各种各样的异常，程序健壮性大大提高；
--二、异常处理语句  上一节使用了ZERO_DIVIDE 异常，这是个预定义异常，在此语句块中，还可以自定义异常
--异常处理结构：定义区-声明异常；执行区-触发异常；异常处理区-捕获并处理异常；  根据此异常，解决掉之前查询数据库表为空的异常处理；
declare
 e_duplicate_name exception;
 v_ename emp.ename%type;
 v_newename emp.enewname%type :='JJ';
 begin
   --查询员工编号为1的姓名
   select emp.ename into v_ename from emp where emp.empno='1';
   if v_ename = v_newename
     then
       raise e_duplicate_name;
       end if;
       insert into emp values('2','JJ',sysdate,'JJ+');
       exception
         when e_duplicate_name
           then
             dbms_output.put_line('不能插入重复的员工名称！');
          -- 通过when others then 来提供一个统一的异常处理；
            -- when others then
             -- dbms_output.put_line('异常编码：'||SQLCODE||'异常信息：'||SQLERRM);
 end;

-- 预定义异常  常见的预定义异常

Oracle在预定义包STANDARD包中提供了一系列的预定义异常。他们是调试Oracle PL/SQL程序的有用工具。大部分错误用负号作为错误号。可以使用SQLCODE内置函数看到错误代码，使用SQLERRM得到异常的内置消息。

异常	错误	何时出现
ACCESS_INTO_NULL	ORA-06530	试图访问未初始化对象的时候出现
CASE_NOT_FOUND	ORA-06592	如果定义了一个没有ELSE子句的CASE语句，而且没有CASE语句满足运行时条件时出现该异常
COLLECTION_IS_NULL	ORA-06531	当程序去访问一个没有进行初始化的NESTED TABLE或者是VARRAY的时候，会出现该异常
CURSOR_ALREADY_OPEN	ORA-06511	游标已经被OPEN，如果再次尝试打开该游标的时候，会出现该异常
DUP_VAL_ON_INDEX	ORA-00001	如果插入一列被唯一索引约束的重复值的时候，就会引发该异常（该值被INDEX认定为冲突的）
INVALID_CURSOR	ORA-01001	不允许的游标操作，比如关闭一个已经被关闭的游标，就会引发
INVALID_NUMBER	ORA-01722	给数字值赋非数字值的时候，该异常就会发生，这个异常也会发生在批读取时候LIMIT子句返回非正数的时候
LOGIN_DENIED	ORA-01017	程序中，使用错误的用户名和密码登录的时候，就会抛出这个异常
NO_DATA_FOUND	ORA_06548	在使用SELECT INTO 结构，并且语句返回NULL值的时候；访问嵌套表中已经删除的表或者是访问INDEX BY表（联合数组）中的未初始化元素就会出现该异常
NOT_LOGGED_ON	ORA-01012	当程序发出数据库调用，但是没有连接的时候（通常，在实际与会话断开连接之后）
PROGRAM_ERROR	ORA-06501	当Oracle还未正式捕获的错误发生时常会发生，这是因为数据库大量的Object功能而发生
ROWTYPE_MISMATCH	ORA-06504	如果游标结构不适合PL/SQL游标变量或者是实际的游标参数不同于游标形参的时候发生该异常
SELF_IS_NULL	ORA-30625	调用一个对象类型非静态成员方法（其中没有初始化对象类型实例）的时候发生该异常
STORAGE_ERROR	ORA-06500	当内存不够分配SGA的足够配额或者是被破坏的时候，引发该异常
SUBSCRIPT_BEYOND_COUNT	ORA-06533	当分配给NESTED TABLE或者VARRAY的空间小于使用的下标的时候，发生该异常（类似于java的ArrayIndexOutOfBoundsException）
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	使用非法的索引值来访问NESTED TABLE或者VARRAY的时候引发
SYS_INVALID_ROWID	ORA-01410	将无效的字符串转化为ROWID的时候引发
TIMEOUT_ON_RESOURCE	ORA-00051	当数据库不能安全锁定资源的时候引发
TOO_MANY_ROWS	ORA-01422	常见错误，在使用SELECT INTO 并且查询返回多个行时引发。如果子查询返回多行，而比较运算符为相等的时候也会引发该异常。
USERENV_COMMITSCN_ERROR	ORA-01725	只可使用函数USERENV('COMMITSCN')作为INSERT语句的VALUES子句中的顶级表达式或者作为UPDATE语句的SET子句中的右操作数
VALUE_ERROR	ORA-06502	将一个变量赋给另一个不能容纳该变量的变量时引发
ZERO_DIVIDE	ORA-01476	将某个数字除以0的时候，会发生该异常

可以很方便的在SQL块中使用EXCEPTION来捕捉异常并且进行处理（当然，编译异常与在声明块中被抛出的异常除外，一个好的PL/SQL编程习惯是规避在声明块中进行变量的赋值操作）。如果上面的预定义异常能够满足要求，就可以使用他们。如果不满足，则可以像JAVA程序一样，定义自己的异常，并且可以使用RAISE EXCEPTION来抛出异常。

--三、自定义异常  预定义异常很多时候不能满足异常处理需求，此时可选择自定义异常；
--1、声明异常
declare
  e_nodeptno exception;
  begin
    null;
  end;
--异常是一种错误的表现形式，而非真正的变量，因此不能在SQL语句中使用异常，但其和变量的范围和规则是相同的。
--2、作用域范围  A同一个块中不能声明一个异常超过两次，但可以在不同的块中声明相同的异常，
declare
  e_userdefinedexception exception;
  begin
    declare
    e_userdefinedexception exception;
    begin
      raise e_userdefinedexception;
      end;
      raise e_userdefinedexception;
   exception
      when others then
        dbms_output.put_line(sqlcode||sqlerrm);
   end;
--不同块中的异常的作用域级别
declare
 e_outerexception exception;
 begin
   declare
    e_innerexception exception;
   begin
      raise e_outerexception;
      raise e_innerexception;
   end;
   raise e_outerexception
   --  raise e_innerexception   在外层块中触发内存块中的异常是非法的；
   exception
     when others then
       dbms_output.put_line('出现错误'||SQLCODE);
 end;
 --使用exception_init
declare
 e_missingnull exception;
 PRAGMA excpetion_init(e_missingnull,-1400); --将该异常与-1400进行关联；
 begin
   insert into emp(ename) values(NULL);
   commit;
   exception
     when e_missingnull then
       dbms_output.put_line(SQLERRM);
       rollback;
 end;  
--使用raise_application_error  RAISE_APPLICATION_ERROE(error_number,error_message,[keep_errors]);
--error_number  是范围在-20000到-20999之间的负整数，
--error_message 最大长度为2048个字符串，
--keep_errors   布尔类型，true时，新的错误将被添加到已抛出的错误列表中，默认为false，新的错误替换当前的错误列表；
--注：raise_application_error 只能在存储的子程序中使用，当被调用时，将结束当前的子程序并返回一个用户自定义的错误代码和消息给应用程序，
--这些错误可以像oracle错误一样被捕获；
create or replace procedure registerEmp(
p_empno in emp.empno%type,
p_empname in emp.empname%type,
p_hirdate in emp.hirdate%type,
p_deptno in emp.deptno%type
)
is
 v_count integer;
 begin
  if p_empno is null
    then
    raise_application_error(-200001,'用户编号为空，报错');
    else
      select count(1) into v_count from emp where emp.empname=p_empname;
      if v_count>0
        then
          raise_application_error(-20002,'用户姓名已然存在，不能重名！');
      end if;
  end if;
 
  if
    p_deptno is null
    then
      raise_application_error(-20003,'部门编号为空，错误！');
  end if;
    insert into emp(empno, empname, deptno, hirdate) values(p_empno,p_empname,p_deptno,p_hirdate);
 exception
   when others then
     raise_application_error(-20004,'异常编码:'||sqlcode||';异常描述:'||sqlerrm);
 end;
--抛出异常
--预定义异常是当有关oracle错误产生时，由oracle隐式抛出的；自定义异常需要显式地使用raise语句进行抛出。自定义异常抛出的位置需要根据业务逻辑的规则来确定；
declare
 e_nocumm exception;
 v_comm number(10,2);
 v_empno number(4) :=5;
 begin
   select comm into v_comm from emp where empno=v_empno;
   if v_comm is null
     then
       raise e_nocumm;
       end if;
       exception
         when e_nocumm then
           dbms_output.put_line('选择的员工没有提成');
           when no_data_found then
               dbms_output.put_line('选择的员工没有提成!!!');
               when others then
                   rollback;
                   end;     
--也可以在一个处理器中处理多个异常
exception
  when zero_divide or no_data_found then
    dbms_output.put_line('------------');                    
--异常的传递  
declare
 d_innerexcpetion exception;
 d_outerexception exception;
 d_threeexception exception;
 begin
   begin
   raise d_innerexcpetion;
   raise d_outerexception;
   raise d_threeexception;
   exception
    when d_innerexcpetion then
    dbms_output.put_line('内部块异常');
    end;
    exception
      when d_outerexception then
         dbms_output.put_line('外部块异常');
 end;
 --注：当内部区块为when others then 时，所有异常都会在内层块中被捕获，将不能传递到外层的语句块；
--声明时异常传递  对变量赋初始值时产生异常，那么异常并不会被当前块所在的异常处理器捕获，异常会立即向外层块传递；
 begin
 declare
 v_ename varchar(2):='ABC';
 begin
   dbms_output.put_line(v_ename);
   exception
     when others then
        dbms_output.put_line('-----内部报错');
  end;
   exception
     when others then
        dbms_output.put_line(sqlcode||';'||sqlerrm);
 end;
--异常处理器中的异常
declare
 e_innerexception exception;
 e_outerexcpeiton exception;
 e_threeexception exception;
 begin
   declare
     e_innerexception exception;
     e_outerexcpeiton exception;
     e_threeexception exception;
     begin
       raise e_innerexception;
       raise e_outerexcpeiton;
       raise e_threeexception;
       exception
         when e_innerexception then
           raise e_outerexcpeiton;
           when e_outerexcpeiton then
             dbms_output.put_line(sqlcode||';'||sqlerrm);
             when others then
                 dbms_output.put_line(sqlcode||';'||sqlerrm);
     end;
     exception
        when others then
         dbms_output.put_line(sqlcode||';'||sqlerrm);
     end;
--重新抛出异常
declare
 v_commexception exception;
 v_common number(3,2);
 v_deptno integer:=&deptno;
 begin
   select comm into v_common from emp where deptno=&deptno;
   if v_common is null then
     raise v_commexception;
     end if;
     exception
       when others then
         dbms_output.put_line(sqlcode);
         raise;
 end;
 --这种重新抛出异常的方式只允许在异常处理程序中这样做；
--异常处理准则
--set serveroutput on;
declare
 c_errors exception;
 v_ename varchar(32);
 v_newename varchar(32) :='zhangsan';
 begin
   begin
   select ename into v_ename where emp where empno= 123;
   if v_ename = v_newename then
     raise c_errors;
     end if;
     exception
       when c_errors then
         v_newename:='lisi';
  end;
   insert into emp values(sys_guid(),v_newename,'123','');
     exception
       when c_errors then
         dbms_output.put_line(sqlcode);
 end;
--获取异常抛出的位置  有时候需要获取异常的具体位置，以下三种方法，建议第三种；
--计数器思想算出位置； 将每个查询语句都定义到子块中；  利用dbms_utility.format_error_backtrace;
--异常与事务处理；  抛出一个异常并不会终止一个事务，除非在异常处理器中显式地使用了rollback和commit语句。
--如果顶层的语句块存在一个未处理的异常，该异常将被传递到调用环境，那么事务将被服务器端自动回滚。
declare
 d_duplicate_name exception;
 v_ename varchar2(32);
 v_newname varchar2(32):='张三';
 begin
   loop
     begin
       savepoint 开始事务；
       select ename into v_ename from emp where empno=123;
       if v_ename =v_newname then
         raise d_duplicate_name;
       end if;
       insert into emp values();
       commit;
       exit;
       when d_duplicate_name then
         rollback to 开始事务；
         v_newname:='李四';
     end;
   end loop;
 end;