RAISE_APPLICATION_ERROR
/*
===========================================================
| 使用RAISE_APPLICATION_ERROR存储过程。
============================================================
*/
--演示该存储过程
BEGIN
RAISE_APPLICATION_ERROR(-20000, 'Account past due.');-- explicitly raise exception
END;
--创建子程序
CREATE OR REPLACE PROCEDURE account_status (
due_date DATE,
today DATE
)
IS
BEGIN
IF due_date < today THEN
RAISE_APPLICATION_ERROR(-20000, 'Account past due.');-- explicitly raise exception
END IF;
END;
/
--调用子程序
DECLARE
past_due EXCEPTION; -- declare exception
PRAGMA EXCEPTION_INIT (past_due, -20000);
BEGIN
account_status (to_date('2010-7-9','yyyy-mm-dd'), to_date('2010-7-10','yyyy-mm-dd')); -- invoke procedure
EXCEPTION
WHEN past_due THEN -- handle exception
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
END;
/*
===========================================================
| 使用用户自定义实现。
============================================================
*/
CREATE OR REPLACE PROCEDURE account_status (
due_date DATE,
today DATE
)
IS
past_due EXCEPTION; -- declare exception
BEGIN
IF due_date < today THEN
RAISE past_due; -- explicitly raise exception
END IF;
EXCEPTION
WHEN past_due THEN -- handle exception
DBMS_OUTPUT.PUT_LINE ('Account past due.');
-- INSERT INTO t VALUES(due_date);
END;
/
--调用子程序
BEGIN
account_status ('2010-7-9', '2010-7-10'); -- invoke procedure
END;
用户自定义异常
/*
===========================================================
| 查询编号为7788的雇员的福利补助（comm列）。
============================================================
*/
DECLARE
v_comm employee.comm%TYPE;
e_comm_is_null EXCEPTION; --定义异常类型变量
BEGIN
SELECT comm INTO v_comm FROM employee WHERE empno=7788;
IF v_comm IS NULL THEN
RAISE e_comm_is_null;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('雇员不存在！错误为:'||SQLCODE||SQLERRM);
WHEN e_comm_is_null THEN
dbms_output.put_line('该雇员无补助');
WHEN others THEN
dbms_output.put_line('出现其他异常');
END;
非预定义异常
/*
===========================================================
| 非预定义异常
============================================================
*/
--需求：修改编号为7788的雇员所属的部门编号为99。
--前提是要在employee和dept表建立主外键约束
ALTER TABLE employee
ADD CONSTRAINT pk_empno PRIMARY KEY(empno);
ALTER TABLE dept
ADD CONSTRAINT pk_deptno PRIMARY KEY(deptno);
ALTER TABLE employee
ADD CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);
DECLARE
e_integrity EXCEPTION;
PRAGMA EXCEPTION_INIT(e_integrity,-2291); -- -2291为Oracle定义的错误号，违背了主外键约束
BEGIN
update employee set deptno=99 where empno=7788;
EXCEPTION
WHEN e_integrity THEN
dbms_output.put_line('该部门不存在');
END;
SELECT * FROM employee;
预定义异常
/*
===========================================================
| 预定义异常
============================================================
*/
--未进行异常处理
DECLARE
v_ename employee.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM employee
WHERE empno=1234;
dbms_output.put_line('雇员名:'||v_ename);
END;
--进行异常处理
DECLARE
v_ename employee.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM employee
WHERE empno=1234;
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('查询只能返回单行');
WHEN OTHERS THEN
dbms_output.put_line('错误号：'||SQLCODE||'错误描述：'||SQLERRM);
END;
