转载:学习笔记：oracle异常处理

16-1:传递异常到调用环境

DECLARE
   v_ename emp.ename%TYPE;
BEGIN
   SELECT ename INTO v_ename FROM emp WHERE empno=&no;
   dbms_output.put_line('雇员名:'||v_ename);
END;
/
16-2:捕获异常
DECLARE
   v_ename emp.ename%TYPE;
BEGIN
   SELECT ename INTO v_ename FROM emp WHERE empno=&no;
   dbms_output.put_line('雇员名:'||v_ename);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     dbms_output.put_line('雇员号不正确，请核实雇员号！');
END;
/
16-3:使用预定义异常
DECLARE
   v_ename emp.ename%TYPE;
BEGIN
   SELECT ename INTO v_ename FROM emp WHERE sal=&salary;
   dbms_output.put_line('姓名:'||v_ename);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     dbms_output.put_line('不存在该工资值的雇员');
   WHEN TOO_MANY_ROWS THEN
     dbms_output.put_line('多个雇员具有该工资');
END;
/
16-4:非预定义异常
DECLARE
   e_integrity EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_integrity,-2291);
   name emp.ename%TYPE:=LOWER('&name');
   dno emp.deptno%TYPE:=&dno;
BEGIN
   UPDATE emp SET deptno=dno WHERE LOWER(ename)=name;
EXCEPTION
   WHEN e_integrity THEN
     dbms_output.put_line('该部门不存在');
END;
/
16-5:自定义异常
DECLARE
   e_integrity EXCEPTION;
   e_no_rows EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_integrity,-2291);
   name emp.ename%TYPE:=LOWER('&name');
   dno emp.deptno%TYPE:=&dno;
BEGIN
   UPDATE emp SET deptno=dno WHERE LOWER(ename)=name;
   IF SQL%NOTFOUND THEN
      RAISE e_no_rows;
   END IF;
EXCEPTION
   WHEN e_integrity THEN
     dbms_output.put_line('该部门不存在');
   WHEN e_no_rows THEN
     dbms_output.put_line('该雇员不存在');
END;
/
16-6:使用sqlcode和sqlerrm
BEGIN
   DELETE FROM dept WHERE deptno=&dno;
EXCEPTION
   WHEN OTHERS THEN
     dbms_output.put_line('错误号:'||SQLCODE);
     dbms_output.put_line('错误消息:'||SQLERRM);
END;
/
16-7:raise_application_error
CREATE OR REPLACE PROCEDURE update_sal
   (name VARCHAR2,salary NUMBER)
IS
BEGIN
   UPDATE emp SET sal=salary WHERE LOWER(ename)=LOWER(name);
   IF SQL%NOTFOUND THEN
      RAISE_APPLICATION_ERROR(-20000,'该雇员不存在');
   END IF;
END;
/
exec update_sal('mary',1000)
16-8:不使用编译预警
CREATE OR REPLACE PROCEDURE dead_code AS
BEGIN
   IF TRUE THEN
     dbms_output.put_line('TRUE');
   ELSE
     dbms_output.put_line('FALSE'); -- 死代码
   END IF;
END;
/
16-9:使用初始化参数plsql_warnings配置编译警告
ALTER SESSION SET plsql_warnings='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE dead_code AS
BEGIN
   IF TRUE THEN
     dbms_output.put_line('TRUE');
   ELSE
     dbms_output.put_line('FALSE'); -- 死代码
   END IF;
END;
/
show errors
16-10:使用包dbms_warning配置编译警告
exec dbms_warning.set_warning_setting_string('ENABLE:ALL' ,'SESSION');
CREATE OR REPLACE PROCEDURE update_sal
(name VARCHAR2,salary VARCHAR2)
IS
BEGIN
   UPDATE emp SET sal=salary WHERE ename=name;
END;
/
show errors