23异常处理
Practice 23
1.写一个PL/SQL块，让用户输入雇员代号选出雇员的名字，并进行如下的异常处理：
a.如果返回的信息多于一行，则引发异常，处理这个异常，并将信息“More than one employee with a salary of
b. 如果返回的信息为0行，则引发异常，处理这个异常，并打印出“NO emplyee with a salary of
c.如果返回的信息为一行，则打印出返回的雇员名字。
答案：
1.
DECLARE
e_more_one EXCEPTION;
e_zero EXCEPTION;
v_ename varchar2(12);
v_sal number;
v_count number;
BEGIN
v_sal:=&emplyeesalary;
Select count(*) into v_count
from emp
WHERE sal = v_sal;
IF v_count=0 THEN
RAISE e_zero;
ELSIF v_count>1 THEN
RAISE e_more_one;
ELSIF v_count=1 THEN
Select ename into v_ename
from emp
WHERE sal = v_sal;
DBMS_OUTPUT.PUT_LINE('employee name is :'||v_ename);
END IF;
EXCEPTION
WHEN e_zero THEN
DBMS_OUTPUT.PUT_LINE('NO emplyee with a salary of '||to_char(v_sal));
WHEN e_more_one THEN
DBMS_OUTPUT.PUT_LINE('more than ONE emplyee with a salary of '||to_char(v_sal));
END;
2.异常处理的例子，与ORACLE内部错误号相关连：
DECLARE
e_emps_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT (
e_emps_remaining, -2292);
v_deptno dept.deptno%TYPE := &p_deptno;
BEGIN
DELETE FROM dept
WHERE deptno = v_deptno;
COMMIT;
EXCEPTION
WHEN e_emps_remaining THEN
DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' ||
TO_CHAR(v_deptno) || '. Employees exist. ');
END;
3.异常处理例子，用户自定义异常：
DECLARE
v_empno emp.empno%TYPE :=&empno;
no_result EXCEPTION;
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
IF SQL%NOTFOUND THEN
RAISE no_result;
END IF;
EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE('There is no one found!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;
4.调用DBMS_STANDARD(ORACLE提供的包)包所定义的RAISE_APPLICATION_ERROR过程，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法：
建立错误代码表：
CREATE TABLE errlog(
Errcode NUMBER,
Errtext CHAR(40));
创建函数：
CREATE OR REPLACE FUNCTION get_salary (p_deptno NUMBER)
RETURN NUMBER AS
V_sal NUMBER;
BEGIN
IF p_deptno IS NULL THEN
RAISE_APPLICATION_ERROR(-20991,'Department number is null');
ELSIF p_deptno<0 THEN
RAISE_APPLICATION_ERROR(-20992,'Invalide department number');
ELSE
SELECT SUM(sal) INTO v_sal FROM EMP WHERE deptno=p_deptno;
RETURN V_sal;
END IF;
END;
调用函数：
DECLARE
V_salary NUMBER(7,2);
V_sqlcode NUMBER;
V_sqlerr VARCHAR2(512);
Null_deptno EXCEPTION;
Invalid_deptno EXCEPTION;
PRAGMA EXCEPTION_INIT(null_deptno,-20991);
PRAGMA EXCEPTION_INIT(invalid_dept
no, -20992);
BEGIN
V_salary :=get_salary(10);
DBMS_OUTPUT.PUT_LINE('10 department salary is: '||TO_CHAR(V_salary));
BEGIN
V_salary :=get_salary(-10);
EXCEPTION
WHEN invalid_deptno THEN
V_sqlcode :=SQLCODE;
V_sqlerr :=SQLERRM;
INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr);
COMMIT;
END inner1;
V_salary :=get_salary(20);
DBMS_OUTPUT.PUT_LINE('20department salary is: '||TO_CHAR(V_salary));
BEGIN
V_salary :=get_salary(NULL);
END inner2;
V_salary :=get_salary(30);
DBMS_OUTPUT.PUT_LINE('30department salary is: '||TO_CHAR(V_salary));
EXCEPTION
WHEN null_deptno THEN
V_sqlcode :=SQLCODE;
V_sqlerr :=SQLERRM;
INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr);
COMMIT;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Other errores!');
END outer;