今天学了异常处理
有预定义异常 与 用户自定义异常 还有 raise_application_error()函数 raise_application_error() 只能把异常抛出而不能处理异常.
预定义异常包括
- NO_DATA_FOUND -- 没有找到数据
- TOO_MANY_ROWS -- 找到多行数据
- INVALID_CURSOR -- 失效的游标
- ZERO_DIVIDE -- 除数为零
- DUP_VAL_ON_INDEX - 唯一索引中插入了重复值
预定义异常的示例:
declare
v_id emp.empno%type; -- 声明变量
- begin
- select empno into v_id from emp where deptno =40;
exception -- 异常处理
when no_data_found then --no_data_found 是使用 select 某字段, 然后 into 的时候, 该字段没有出.
- rollback;
- dbms_output.put_line('没有 40 号部门记录');
when too_many_rows then --too_many_rows 是使用 select 某字段, 然后 into 的时候, 该字段有多个值.
- rollback;
- dbms_output.put_line('返回多条记录');
when others then -- 其它的异常出现
- rollback;
- dbms_output.put_line('出现其他错误.');
- end;
用户自定义异常就是用户定义一个异常情况, 遇到这种情况再对这种情况进行处理
因为用户定义的异常不一定是 Oracle 返回的系统错误, 系统不会自动触发, 需要在声明部分定义. 用户定义的异常处理部分基本上和预定义异常相同.
- declare
- salary_level varchar2(1);
invalid_salary_level exception; -- 声明异常
- begin
- salary_level := 'D';
- if salary_level not in ('A','B','C') then
raise invalid_salary_level; -- 触发异常
end if;
exception -- 异常处理
- when invalid_salary_level then
- dbms_output.put_line('invalid salary level');
- end;
raise_application_error() 函数只是将异常抛出, 不进行异常处理, 并且终止程序. 而用户自定义异常以及预定义异常不回终止程序, 但会终止该 PL/SQL 代码块, 所以一个存储过程中可以有多个 PL/SQL 代码块.
关于异常的语法及定义:
什么是异常:
PL/SQL 用异常和异常处理器来实现错误处理
Oracle 中出现错误的情形通常分为编译时错误 (compile-time error) 和运行时错误(run-time error).
异常在 PL/SQL 执行过程中很可能出现
对异常如果不进行处理, 异常可能会中断程序的运行
捕获异常的规则:
在异常部分 WHEN 子句没有数量限制
当异常抛出后, 控制无条件转到异常处理部分
EXCEPTION 关键词开始异常处理部分 WHEN OTHERS 为最后的一条子句
在异常块中, 只有一个句柄会处理异常
关于异常捕获的函数:
SQLCODE 返回错误代码
SQLERRM 返回与错误代码关联的消息
保存任何非预期的异常的错误编码和错误消息
- declare
- v_error_code NUMBER;
- v_error_message VARCHAR2(255);
- BEGIN
- EXCEPTION
- WHEN OTHERS THEN
- ROLLBACK;
- v_error_code := SQLCODE;
- v_error_message := SQLERRM;
- INSERT INTO err_logs VALUES (v_error_code, v_error_message);
- END;
异常的传播
PL/SQL 中错误处理的步骤:
步骤 1: 如果当前块中有该异常的处理器, 则执行该异常处理语句块, 然后控制权传递到外层语句块 步骤 2: 如果没有当前异常的处理器, 把该异常传播给外层块. 然后在外层执行步骤 1: 如果此语句在最外层语句块, 则该异常将被传播给调用环境
没有处理的异常将沿检测异常调用程序传播到外面, 当异常被处理并解决或到达程序最外层传播停止. 异常是自里向外逐级传递的.
小题:
1. 根据员工号, 获得员工到目前为止参加工作年限(保留到整数), 员工号不存在时提示 "此员工号不存在".
- create or replace function get_workyear
- (v_id in emp.empno%type)
- return varchar2
- IS
- v_workyear integer;
- BEGIN
select to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy') -- 两个数字字符串相减的值存到整数型变量中
- into v_workyear
- from emp
- where emp.empno = v_id;
- return v_workyear;
- EXCEPTION
- when no_data_found then
- dbms_output.put_line('此员工号不存在');
- return -1;
- END get_workyear;
- 2.
1建表 myemp. 该表内容与 emp 一致;
2建存储过程. 存储过程要的参数, 和表里的字段一一对应. 比如, 表里有 empno, 存储过程就要有一个参数对应这字段 i_empno, 类型肯定和 empno 一样, 如果你知道类型是 number(4), 就直接写成 (i_empno in number(4),...) 以此类推.
3功能实现, 根据 empno 判断, 如果 myemp 表里已经有这个 empno, 你就根据你传入的信息把 empno 的信息更新了, 如果没有, 就把你这些传入的字段, 插入到表里,
eg: 我只用两个字段来说明: empno,sal
入参 1:123,1000, 经过断, myemp 表里没有 123 这个 empno, 那么执行完存储过程, 这个信息要插入到表里;
入参 2:7369,2000, 经判断, 表里已经有这个编号, 但 sal 为 800, 那么执行完存储过程, 7369 的 sal 更新为 2000;
- create or replace procedure store_info
- (v_empno in myemp.empno%type,
- v_ename in myemp.ename%type,
- v_job in myemp.job%type,
- v_mgr in myemp.mgr%type,
- v_hiredate in myemp.hiredate%type,
- v_sal in myemp.sal%type,
- v_comm in myemp.comm%type,
- v_deptno in myemp.deptno%type
- )
- IS
- v_id myemp.empno%type:=0;
- BEGIN
- select count(*) into v_id
- from myemp
- where myemp.empno = v_empno;
- if (v_id=0) then
- insert into myemp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
- values (v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno);
- else
- update myemp
- set myemp.ename=nvl(v_ename,myemp.ename) , myemp.job=nvl(v_job,myemp.job),
- myemp.mgr=nvl(v_mgr,myemp.mgr) , myemp.hiredate=nvl(v_hiredate,myemp.hiredate),
- myemp.sal=nvl(v_sal,myemp.sal) , myemp.comm=nvl(v_comm,myemp.comm),
- myemp.deptno=nvl(v_deptno,myemp.deptno)
- where myemp.empno = v_empno ;
- end if;
- END store_info;
- begin
- store_info(7369,null,null,null,null,2000,null,null);
- end;
结果
[注意] :
为什么要把这一题关于存储过程的题放到这里?
因为我起初用异常处理部分来写这一题......, 这是不规范的.
本题中用到 count() 函数, count() 是用来计算满足条件的行数的, count(*) 计算所有的行, 包括空值.
用异常处理来写本题的代码:
- create table myemp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
- as
- select empno, ename, job, mgr, hiredate, sal, comm, deptno
- from emp;
- create or replace procedure store_info
- (v_empno in myemp.empno%type,
- v_ename in myemp.ename%type,
- v_job in myemp.job%type,
- v_mgr in myemp.mgr%type,
- v_hiredate in myemp.hiredate%type,
- v_sal in myemp.sal%type,
- v_comm in myemp.comm%type,
- v_deptno in myemp.deptno%type
- )
- IS
- v_id myemp.empno%type;
- BEGIN
- select myemp.empno into v_id
- from myemp
- where myemp.empno = v_empno;
- update myemp
- set myemp.ename=v_ename, myemp.job=v_job,myemp.mgr=v_mgr,
- myemp.hiredate=v_hiredate, myemp.sal=v_sal,
- myemp.comm=v_comm,myemp.deptno=v_deptno
- where myemp.empno = v_id;
- EXCEPTION
- when no_data_found then
- insert into myemp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
- values (v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno);
- END store_info;
- 3.
编写 PL/SQL 块, 使用 SELECT 语句将管理者编号为空的员工的姓名及工作编号显示出来, 如果符合条件的员工多于一人, 则返回字符串 "最高管理者人员过多!" 字符串, 如果找到没有符合条件的记录, 则返回字符串 "没有最高管理者, 请指定"
代码:
- declare
- o_ename emp.ename%type;
- o_empno emp.empno%type;
- v_id emp.empno%type;
- begin
- select emp.empno into v_id
- from emp
- where emp.mgr is null;
- select emp.ename into o_ename from emp where emp.empno = v_id;
- select emp.empno into o_empno from emp where emp.empno = v_id;
- dbms_output.put_line('员工姓名:'||o_ename||','|| '员工编号:'||o_empno);
- exception
- when no_data_found then
- dbms_output.put_line('没有最高管理者, 请指定');
- when too_many_rows then
- dbms_output.put_line('最高管理者人员过多');
- end;
4. 获得每个部门的平均工资, 如果平均工资大于 2000, 视为用户定义的异常, 提示 "该部门的平均工资过高".
- declare
- cursor cemp
- is
- select dept.dname,avg(sal)
- from emp,dept
- where emp.deptno = dept.deptno
- group by emp.deptno ,dept.dname;
- v_dname dept.dname%type ;
- v_asal emp.sal%type ;
- too_high_sal exception;
- begin
- open cemp;
loop -- 打开循环
- fetch cemp into v_dname,v_asal;
- exit when cemp%notfound;
begin -- 这里写了一个 PL/SQL 代码块, 里面可以做异常处理
- if v_asal> 2000 then
- raise too_high_sal;
- end if;
exception -- 异常处理, 会终止此代码块. 进入下一次循环
- when too_high_sal then
- dbms_output.put_line(v_dname||'该部门工资过高');
- end;
- end loop;
close cemp;-- 注意 end loop 与 close cemp 的先后顺序. 必须是先结束循环, 再关闭游标.
end;
附一张图:
来源: http://www.linuxidc.com/Linux/2019-02/156844.htm