浏览 2115 次
锁定老帖子 主题:学习笔记:16异常处理
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2010-01-05
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 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |