declare v_ename emp.ename%type; v_sal emp.sal%type; /* -1:未选定行 */ v_err number; begin select ename,sal into v_ename,v_sal from emp where empno=&p_empno; v_err:=0; dbms_output.put_line(v_err); exception when no_data_found then v_err:=-1; dbms_output.put_line(v_err); end; /
DECLARE salary_too_high EXCEPTION; current_salary NUMBER := 20000; max_salary NUMBER := 10000; erroneous_salary NUMBER; BEGIN
BEGIN IF current_salary > max_salary THEN RAISE salary_too_high; -- raise exception END IF; EXCEPTION WHEN salary_too_high THEN -- start handling exception erroneous_salary := current_salary; DBMS_OUTPUT.PUT_LINE('Salary ' || erroneous_salary ||' is out of range.'); DBMS_OUTPUT.PUT_LINE ('Maximum salary is ' || max_salary || '.'); RAISE; -- reraise current exception (exception name is optional) END;
EXCEPTION WHEN salary_too_high THEN -- finish handling exception current_salary := max_salary;
DBMS_OUTPUT.PUT_LINE ( 'Revising salary from ' || erroneous_salary || ' to ' || current_salary || '.' ); END; /
Result:
Salary 20000 is out of range. Maximum salary is 10000. Revising salary from 20000 to 10000.
案例2-RAISE_APPLICATION_ERROR
CREATE PROCEDURE account_status ( due_date DATE, today DATE ) AUTHID DEFINER IS BEGIN IF due_date < today THEN -- explicitly raise exception RAISE_APPLICATION_ERROR(-20000, 'Account past due.'); END IF; END; /
DECLARE past_due EXCEPTION; -- declare exception PRAGMA EXCEPTION_INIT (past_due, -20000); -- assign error code to exception BEGIN account_status ('1-JUL-10', '9-JUL-10'); -- invoke procedure EXCEPTION WHEN past_due THEN -- handle exception DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000))); END; /
Result:
ORA-20000: Account past due.
课后练习
练习1-使用函数sqlerrm打印ORA-消息描述
/* 使用函数sqlerrm打印ORA-消息描述：*/ declare errm varchar2(1000); begin for errno in 20000..20999 loop errm:=sqlerrm(-errno); dbms_output.put_line(errm); end loop; end; /
练习2-处理系统预定义异常
捕获到no_data_found；则输出-1：
捕获到too_many_row；则输出查询返回太多行;
捕获到其他异常，则输出未知错误。
declare v_ename emp.ename%type; v_sal emp.sal%type; v_err number; begin update emp set deptno=80 where empno=7839; select ename,sal into v_ename,v_sal from emp where deptno=&p_deptno; v_err:=0; dbms_output.put_line(v_err); exception when no_data_found then v_err:=-1; dbms_output.put_line(v_err); when too_many_rows then dbms_output.put_line('查询返回太多行'); when others then dbms_output.put_line('未知错误'); end; /
练习3-处理内部定义异常ORA-02291
[oracle@NB-flexgw1 ~]$ oerr ora 2291 02291, 00000,"integrity constraint (%s.%s) violated - parent key not found" // *Cause: A foreign key value has no matching primary key value. // *Action: Delete the foreign key or add a matching primary key.
如果捕获到异常ORA-02291则输出：外键取值错误；
declare myerr exception; pragma exception_init(myerr,-2291); v_ename emp.ename%type; v_sal emp.sal%type; v_err number; begin update emp set deptno=80 where empno=7839; select ename,sal into v_ename,v_sal from emp where deptno=&p_deptno; v_err:=0; dbms_output.put_line(v_err); exception when no_data_found then v_err:=-1; dbms_output.put_line(v_err); when too_many_rows then dbms_output.put_line('查询返回太多行'); when myerr then dbms_output.put_line('外键取值错误'); end; /
练习4-综合练习
获取部门编号为1的员工姓名
SCOTT@testdb>select ename from emp where empno=1;
no rows selected
删除部门表中所有的行
SCOTT@testdb>delete dept; delete dept * ERROR at line 1: ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
更新员工表中部门编号为7839的部门编号，手动输入
打印员工表中部门编号为手动输入的编号的员工工资
/* 异常处理的作用 */ declare v_sal number; begin declare myerr exception; pragma exception_init(myerr,-2292); v_deptno number:=&p_deptno; v_ename varchar2(10); begin select ename into v_ename from emp where empno=1;/* 因为不存在empno=1的行，该行会触发异常no_data_found */ delete dept;/* dept表存在外键，该动作会会触发异常 ORA-02292*/ update emp set deptno=v_deptno where empno=7839; exception when myerr then dbms_output.put_line('ORA-02292'); when others then dbms_output.put_line('unknown error'); dbms_output.put_line(sqlcode||' ; '||sqlerrm); end; select sal into v_sal from emp where empno=&p_empno; dbms_output.put_line(v_sal); end; /
出现异常时不会继续往下执行，会跳出当前块，因此delete 和update语句都没有执行；而select sal into v_sal from emp where empno=&p_empno;语句执行成功。
运行结果
Enter value for p_deptno: 1 old 7: v_deptno number:=&p_deptno; new 7: v_deptno number:=1; Enter value for p_empno: 8000 old 20: select sal into v_sal from emp where empno=&p_empno; new 20: select sal into v_sal from emp where empno=8000; unknown error 100 ; ORA-01403: no data found 1888
PL/SQL procedure successfully completed.
我们调整一下代码
/* 异常处理的作用 */ declare v_sal number; begin declare myerr exception; pragma exception_init(myerr,-2292); v_deptno number:=&p_deptno; v_ename varchar2(10); begin delete dept;/* dept表存在外键，该动作会会触发异常 ORA-02292*/ update emp set deptno=v_deptno where empno=7839; exception when myerr then dbms_output.put_line('ORA-02292'); when others then dbms_output.put_line('unknown error'); dbms_output.put_line(sqlcode||' ; '||sqlerrm); end; select sal into v_sal from emp where empno=&p_empno; dbms_output.put_line(v_sal); end; /
执行结果
Enter value for p_deptno: 1 old 7: v_deptno number:=&p_deptno; new 7: v_deptno number:=1; Enter value for p_empno: 8000 old 19: select sal into v_sal from emp where empno=&p_empno; new 19: select sal into v_sal from emp where empno=8000; ORA-02292 1888
成功触发了ORA-02292异常。
练习5-处理自定义异常
/* 使用自定义异常 */ declare myerr exception; pragma exception_init(myerr,-20000); v_empno number:=&p_empno; v_sal number:=&p_sal; begin if v_sal<1000 then raise_application_error(-20000,'自定义错误！'); else update emp set sal=v_sal where empno=v_empno; end if; exception when myerr then dbms_output.put_line('工资太少!'); end; /