学习笔记：16异常处理

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