pl/sq之例外处理

例外处理

 例外的分类
oracle将例外分为预定义例外，非预定义例外和自定义例外三种。
预定义例外用于处理常见的oracle错误
非预定义例外用于处理预定义例外不能处理的例外
自定义例外用于处理与oracle错误无关的其它情况

 例外传递
如果不处理例外我们看看会出现什么情况：
案例，编写一个过程，可接收雇员的编号，并显示该雇员的姓名。
问题是，如果输入的雇员编号不存在，怎样去处理呢？

Sql代码

1. --例外案例   
2. declare    
3. --定义   
4. v_ename emp.ename%type;   
5. begin  
6. --   
7. select ename into v_ename from emp where empno=&gno;   
8. dbms_output.put_line('名字：'||v_ename)     
9. /  

 

执行，弹出框，看图：


随便输个不存在的编号，回车，会抛出异常，显示：
ORA-01403: 未找到数据
ORA-06512: 在line 6

Sql代码

1. declare    
2. --定义   
3. v_ename emp.ename%type;   
4. begin  
5. --   
6. select ename into v_ename from emp where empno=&gno;   
7. dbms_output.put_line('名字：'||v_ename)     
8. exception    
9.     when no_data_found then  
10.     dbms_output.put_line('编号没有！');   
11. /  

执行，输入一个不存在的编号，回车，显示：
编号没有！

 处理预定义例外
预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle 规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外。我们给大家介绍常用的例外。

 预定义例外 case_not_found
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found的例外：

Sql代码

1. create or replace procedure sp_pro6(spno number) is  
2.   v_sal emp.sal%type;   
3. begin  
4.   select sal into v_sal from emp where empno = spno;   
5.   case  
6.     when v_sal < 1000 then  
7.       update emp set sal = sal + 100 where empno = spno;   
8.     when v_sal < 2000 then  
9.       update emp set sal = sal + 200 where empno = spno;   
10.   end case;   
11. exception   
12.   when case_not_found then  
13.     dbms_output.put_line('case语句没有与' || v_sal || '相匹配的条件');   
14. end;  

 预定义例外 cursor_already_open
当重新打开已经打开的游标时，会隐含的触发例外cursor_already_open

Sql代码

1. declare  
2.   cursor emp_cursor is select ename, sal from emp;   
3. begin  
4.   open emp_cursor;   
5.   for emp_record1 in emp_cursor loop   
6.     dbms_output.put_line(emp_record1.ename);   
7.   end loop;   
8. exception   
9.   when cursor_already_open then  
10.     dbms_output.put_line('游标已经打开');   
11. end;   
12. /  

 预定义例外 dup_val_on_index
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外dup_val_on_index例外

Sql代码

1. begin  
2.   insert into dept values (10, '公关部', '北京');   
3. exception   
4.   when dup_val_on_index then  
5.     dbms_output.put_line('在deptno列上不能出现重复值');   
6. end;  

 

 预定义例外 invalid_cursor
当试图在不合法的游标上执行操作时，会触发该例外
例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外

Sql代码

1. declare  
2.   cursor emp_cursor is select ename, sal from emp;   
3.   emp_record emp_cursor%rowtype;   
4. begin  
5.   --open emp_cursor; --打开游标   
6.   fetch emp_cursor into emp_record;   
7.   dbms_output.put_line(emp_record.ename);   
8.   close emp_cursor;   
9. exception   
10.   when invalid_cursor then  
11.     dbms_output.put_line('请检测游标是否打开');   
12. end;  

 预定义例外 invalid_number
当输入的数据有误时，会触发该例外
比如：数字100写成了loo就会触发该例外

Sql代码

1. begin  
2.   update emp set sal= sal + 'loo';   
3. exception   
4.   when invalid_number then  
5.     dbms_output.put_line('输入的数字不正确');   
6. end;  

预定义例外 no_data_found
  下面是一个pl/sql块，当执行select into 没有返回行，就会触发该例外

Sql代码

1. declare  
2.     v_sal emp.sal%type;   
3. begin    
4.     select sal into v_sal from emp   
5.     when ename='&name';   
6. exception   
7.     when no_data_found then  
8.     dbms_output.put_line('不存在该员工')；   
9. end;  

 预定义例外 too_many_rows
当执行select into语句时，如果返回超过了一行，则会触发该例外。

Sql代码

1. declare  
2.   v_ename emp.ename%type;   
3. begin  
4.   select ename into v_ename from emp;   
5. exception   
6.   when too_many_rows then  
7.     dbms_output.put_line('返回了多行');   
8. end;  

 

 预定义例外 zero_divide
当执行2/0语句时，则会触发该例外。

 预定义例外 value_error
当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value_error，比如：

Sql代码

1. declare  
2.   v_ename varchar2(5);   
3. begin  
4.   select ename into v_ename from emp where empno = &no1;   
5.   dbms_output.put_line(v_ename);   
6. exception   
7.   when value_error then  
8.     dbms_output.put_line('变量尺寸不足');   
9. end;  

 其它预定义例外(这些例外不是在pl/sql里触发的，而是在用oracle时触发的，所以取名叫其它预定义例外)
1.login_denied
当用户非法登录时，会触发该例外
2.not_logged_on
如果用户没有登录就执行dml操作，就会触发该例外
3.storage_error
如果超过了内存空间或是内存被损坏，就触发该例外
4.timeout_on_resource
如果oracle在等待资源时，出现了超时就触发该例外

 非预定义例外
  非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle错误，而当使用pl/sql开发应用程序时，可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等等。在这样的情况下，也可以处理oracle的各种例外，因为非预定义例外用的不多，这里我就不举例了。
 处理自定义例外
  预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle错误会隐含的触发相应的例外；而自定义例外与oracle错误没有任何关联，它是由开发人员为特定情况所定义的例外.
问题：请编写一个pl/sql块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。

 

Sql代码

1. --自定义例外   
2. create or replace procedure ex_test(spNo number)   
3. is  
4. begin  
5. --更新用户sal   
6. update emp set sal=sal+1000 where empno=spNo;   
7. end;   
8. /  

运行，该过程被成功创建。
SQL> exec ex_test(56);
PL/SQL过程被成功完成

这里，编号为56是不存在的，刚才的报异常了，为什么现在不报异常呢？
因为刚才的是select语句
怎么解决这个问题呢？ 修改代码，如下：

Sql代码

1. --自定义例外   
2. create or replace procedure ex_test(spNo number)   
3. is  
4. --定义一个例外   
5. myex exception;   
6. begin  
7. --更新用户sal   
8. update emp set sal=sal+1000 where empno=spNo;   
9. --sql%notfound这是表示没有update ,即update操作没有成功  
10. --raise myex;触发myex   
11. if sql%notfound then  
12. raise myex;   
13. end if;   
14. exception   
15. when myex then  
16. dbms_output.put_line('没有更新任何用户')；   
17. end;   
18. /  

现在再测试一次：
SQL> exec ex_test(56);
没有更新任何用户