5.1.4 用户定义的异常处理
调用DBMS_STANDARD(ORACLE提供的包)包所定义的RAISE_APPLICATION_ERROR过程,可以重新定义异常错误消息,它为应用程序提供了一种与ORACLE交互的方法。
RAISE_APPLICATION_ERROR 的语法如下:
RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] );
这里的error_number 是从 –20,000 到 –20,999 之间的参数,
error_message 是相应的提示信息(字节),
keep_errors 为可选,如果keep_errors =TRUE ,则新错误将被添加到已经引发的错误列表中。如果keep_errors=FALSE(缺省),则新错误将替换当前的错误列表。
例4:创建一个函数get_salary, 该函数检索指定部门的工资总和,其中定义了-20991和-20992号错误,分别处理参数为空和非法部门代码两种错误:
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, ’部门代码为空’);
ELSIF p_deptno<0 THEN
RAISE_APPLICATION_ERROR(-20992, ’无效的部门代码’);
ELSE
SELECT SUM(employees.salary) INTO v_sal FROM employees
WHERE employees.department_id=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_deptno, -20992);
BEGIN
V_salary :=get_salary(10);
DBMS_OUTPUT.PUT_LINE('10号部门工资:' || 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('部门号为20的工资为:'||TO_CHAR(V_salary));
BEGIN
V_salary :=get_salary(NULL);
END inner2;
V_salary := get_salary(30);
DBMS_OUTPUT.PUT_LINE('部门号为30的工资为:'||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(SQLCODE||'---'||SQLERRM);
END outer;
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, ’部门代码为空’);
ELSIF p_deptno<0 THEN
RAISE_APPLICATION_ERROR(-20992, ’无效的部门代码’);
ELSE
SELECT SUM(employees.salary) INTO v_sal FROM employees
WHERE employees.department_id=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_deptno, -20992);
BEGIN
V_salary :=get_salary(10);
DBMS_OUTPUT.PUT_LINE('10号部门工资:' || 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('部门号为20的工资为:'||TO_CHAR(V_salary));
BEGIN
V_salary :=get_salary(NULL);
END inner2;
V_salary := get_salary(30);
DBMS_OUTPUT.PUT_LINE('部门号为30的工资为:'||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(SQLCODE||'---'||SQLERRM);
END outer;
例5:定义触发器,使用RAISE_APPLICATION_ERROR阻止没有员工姓名的新员式记录插入:
CREATE OR REPLACE TRIGGER tr_insert_emp
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :new.first_name IS NULL OR :new.last_name is null THEN
RAISE_APPLICATION_ERROR(-20000,'Employee must have a name.');
END IF;
END;
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :new.first_name IS NULL OR :new.last_name is null THEN
RAISE_APPLICATION_ERROR(-20000,'Employee must have a name.');
END IF;
END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21861353/viewspace-757674/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21861353/viewspace-757674/