转载自:http://fengshen-xia.iteye.com/blog/745204
- --异常处理---------------------------------------------------------//
- --预定义异常
- --NO_DATA_FOUND 没有数据满足查询要求
- --ZERO_DIVIDE 算术错误,如试图用0去除一个数
- --INVALID_NUMBER 在要求数据的地方使用了非数据
- --NOT_LOGGED_ON 没有连接上Oracle
- --TOO_MANY_ROWS SELECT INTO 返回多行记录
- --VALUE_ERROR 遇到算术的、转换的、截去的或约束错误
- --CURSOR_ALREADY_OPEN 试图打开一个已经打开的游标
- --DUP_VAL_ON_INDEX 试图插入一个已经存在的唯一约束值
- --LOGIN_DENIED 要求进入系统的请求被拒绝
- --TIMEOUT_ON_RESOURCE 等待的系统时间已经超时
- --OTHERS 其它异常
- --NO_DATA_FOUND 异常-----------------------------------------------//
- --%TYPE是指定义一个变量和数据库中某个表的某个字段的数据类型一样
- DECLARE
- v_name emp.ename%TYPE;
- BEGIN
- SELECT ename INTO v_name FROM emp WHERE empno='1234'; --异常发生,empno 中没有1234
- DBMS_OUTPUT.PUT_LINE('Name is : ' || v_name);
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- DBMS_OUTPUT.PUT_LINE('No Data Found');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --ZERO_DIVIDE 异常-------------------------------------------------//
- BEGIN
- DBMS_OUTPUT.PUT_LINE(5/0); --异常发生,不能被0除
- EXCEPTION
- WHEN ZERO_DIVIDE THEN
- DBMS_OUTPUT.PUT_LINE('Can not divide zero');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --TOO_MANY_ROWS 异常-----------------------------------------------//
- DECLARE
- no emp.empno%TYPE;
- name emp.ename%TYPE;
- dept emp.deptno%TYPE;
- BEGIN
- SELECT empno,ename,deptno INTO no,name,dept FROM emp WHERE empno = 7369;
- DBMS_OUTPUT.PUT_LINE('员工号 :' || no);
- DBMS_OUTPUT.PUT_LINE('员工名 :' || name);
- DBMS_OUTPUT.PUT_LINE('部门号 :' || dept);
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- DBMS_OUTPUT.PUT_LINE('7369雇员不存在'); --未找到数据
- WHEN TOO_MANY_ROWS THEN
- DBMS_OUTPUT.PUT_LINE('有多个学员号是7369'); --返回多行
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --DUP_VAL_ON_INDEX 异常--------------------------------------------//
- BEGIN
- INSERT INTO emp
- VALUES('7788','LaoZhao','Soft','7689','03-12月-81','2600','0','20');
- EXCEPTION
- WHEN DUP_VAL_ON_INDEX THEN
- DBMS_OUTPUT.PUT_LINE('违反了PRIMARY KEY约束:7788雇员已经存在');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- -------------------------------------------------------------------//
- --如果员工7788的工资小于3000,则更新为3000,如未找到该雇员,则利用异常处理打印相应信息
- DECLARE
- salary emp.sal%TYPE;
- BEGIN
- SELECT sal INTO salary FROM emp WHERE empno='7788';
- IF salary < 3000 THEN
- UPDATE emp SET sal = '3000' WHERE empno = '7788';
- DBMS_OUTPUT.PUT_LINE('记录已更新');
- ELSE
- DBMS_OUTPUT.PUT_LINE('IF 条件不成立');
- END IF;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- DBMS_OUTPUT.PUT_LINE('7788雇员不存在');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --用户自定义异常---------------------------------------------------//
- --001
- DECLARE
- --v_num1 NUMBER:=5;
- v_num1 NUMBER:=0;
- --v_num2 NUMBER:=0;
- v_num2 NUMBER:=5;
- MyException EXCEPTION;
- BEGIN
- DBMS_OUTPUT.PUT_LINE(v_num1/v_num2);
- RAISE MyException; --引发自定义异常
- DBMS_OUTPUT.PUT_LINE('OK'); --此句永远不会执行,因为发生异常后程序将直接跳到EXCEPTION段
- EXCEPTION
- WHEN MyException THEN
- DBMS_OUTPUT.PUT_LINE('Cannot Divide Zero');
- --WHEN ZERO_DIVIDE THEN
- -- DBMS_OUTPUT.PUT_LINE('Can not Divide Zero');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --002
- DECLARE
- id score.id%TYPE;
- java score.java%TYPE;
- MyException EXCEPTION;
- BEGIN
- SELECT id,java INTO id,java FROM score WHERE id = 4;
- IF java < 0 OR java > 100 THEN
- RAISE MyException;
- ELSE
- DBMS_OUTPUT.PUT_LINE('OK');
- END IF;
- EXCEPTION
- WHEN MyException THEN
- UPDATE score SET java=0 WHERE id = 4;
- DBMS_OUTPUT.PUT_LINE('分数不能大于100或小于0,现已经清0');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --003
- DECLARE
- v_date emp.hiredate%TYPE;
- MyException EXCEPTION;
- BEGIN
- INSERT INTO emp(empno,hiredate)
- VALUES(1111,TO_DATE('2010-12-25','yyyy-mm-dd'));
- COMMIT;
- SELECT hiredate INTO v_date FROM emp WHERE empno=1111;
- IF v_date > SYSDATE THEN
- RAISE MyException;
- END IF;
- DBMS_OUTPUT.PUT_LINE('执行完毕');
- EXCEPTION
- WHEN MyException THEN
- DBMS_OUTPUT.PUT_LINE('雇用日期不能大于当前日期');
- --raise_application_error(-20002,'雇用日期不能大于当前日期');
- UPDATE emp SET hiredate = SYSDATE WHERE empno = 1111;
- DBMS_OUTPUT.PUT_LINE('日期已更新为当前日期');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --将定义好的异常与标准的ORACLE错误联系起来-------------------------//
- --001
- DECLARE
- MyException EXCEPTION;
- PRAGMA EXCEPTION_INIT(MyException,-00001); --关联语句
- BEGIN
- INSERT INTO emp(empno) VALUES(1234); --发生异常,企图插入有Check约束的重复值
- COMMIT; --插入后提交
- DBMS_OUTPUT.PUT_LINE('插入正常');
- EXCEPTION
- WHEN MyException THEN
- DBMS_OUTPUT.PUT_LINE('ORA-00001: 违反了唯一约束');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --002
- CREATE TABLE a
- (
- id INT NOT NULL,
- name VARCHAR2(20) NOT NULL
- );
- DECLARE
- MyException EXCEPTION;
- PRAGMA EXCEPTION_INIT(MyException,-1400); --关联语句
- BEGIN
- INSERT INTO a(id) VALUES(1); --发生异常,违反了NOT NULL约束
- EXCEPTION
- WHEN MyException THEN
- DBMS_OUTPUT.PUT_LINE('错误代号:-1400');
- DBMS_OUTPUT.PUT_LINE('错误描述:违反了NOT NULL约束');
- DBMS_OUTPUT.PUT_LINE('错误发生时间:' ||
- TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
- INSERT INTO errinfo
- VALUES('-1400','违反了 NOT NULL约束',TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
- DBMS_OUTPUT.PUT_LINE('已经将错误记录在ErrInfo表');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --003
- DECLARE
- MyException EXCEPTION;
- salary emp.sal%TYPE;
- BEGIN
- SELECT sal INTO salary FROM emp WHERE empno = '7788';
- IF salary > 2000 THEN
- RAISE MyException;
- END IF;
- EXCEPTION
- WHEN MyException THEN
- RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR异常发生',FALSE); --TRUE
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --课后练习---------------------------------------------------------//
- --001
- DECLARE
- gz emp.sal%TYPE;
- MyExc EXCEPTION;
- BEGIN
- SELECT sal INTO gz FROM emp WHERE ename='SCOTT';
- DBMS_OUTPUT.PUT_LINE('原工资 : ' || gz);
- IF gz < 2000 THEN
- RAISE MyExc;
- ELSE
- DBMS_OUTPUT.PUT_LINE('工资无需更新');
- END IF;
- EXCEPTION
- WHEN MyExc THEN
- UPDATE emp SET sal = 3000 WHERE ename='SCOTT';
- DBMS_OUTPUT.PUT_LINE('记录已经更新');
- SELECT sal INTO gz FROM emp WHERE ename='SCOTT';
- DBMS_OUTPUT.PUT_LINE('更新后工资 : ' || gz);
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --002
- DECLARE
- lo salgrade.losal%TYPE;
- hi salgrade.hisal%TYPE;
- MyExc EXCEPTION;
- BEGIN
- INSERT INTO salgrade VALUES(6,2600,2400);
- COMMIT;
- SELECT losal,hisal INTO lo,hi FROM salgrade WHERE grade = 6;
- IF lo > hi THEN
- RAISE MyExc;
- ELSE
- DBMS_OUTPUT.PUT_LINE('记录已经插入');
- END IF;
- EXCEPTION
- WHEN MyExc THEN
- --删除插入的记录并打印相应信息
- delete salgrade WHERE grade = 6;
- DBMS_OUTPUT.PUT_LINE('记录插入失败 LOSAL 必须小于 HISAL');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('Unknown Error');
- END;
- /
- --附注-------------------------------------------------------------//
- --如需在PL\SQL中进行值输入,
- DECLARE
- num LONG;
- BEGIN
- num := '&num';
- DBMS_OUTPUT.PUT_LINE('Input value is : ' || num);
- END;
- /
- --注意:如果要使你在DBMS_OUTPUT.PUT_LINE(...)中显示的语句得以显示,则必须显示的调用
- SET SERVEROUTPUT ON;