PL/SQL_处理例外1（预定义例外）

EXCEPTION
WHEN exception1 [OR exception2...] THEN
statement1;
statement2;
...
[WHEN exception3 [OR exception4...] THEN
statement1;
statement2;
...
[when OTHERS THEN
statement1;
statement2;
...

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;

处理预定义例外

CREATE TYPE emp_type AS object(
  NAME VARCHAR2(10),
  sal NUMBER(6,2)
);

DECLARE
  v_emp emp_type;
  --v_emp emp_type := emp_type(‘‘,0);
BEGIN
  v_emp.name := ‘张三‘;
  v_emp.sal := 1000;
  EXCEPTION
    WHEN ACCESS_INTO_NULL THEN
      dbms_output.put_line(‘首先初始化emp‘);
END;

DECLARE
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno = &NO;
  CASE
    WHEN v_sal < 1000 THEN
      UPDATE emp SET sal = sal + 100 WHERE empno = &NO;
    WHEN v_sal < 2000 THEN
      UPDATE emp SET sal = sal + 150 WHERE empno = &NO;
    WHEN v_sal < 3000 THEN
      UPDATE emp SET sal = sal + 200 WHERE empno = &NO;
  END CASE;
  EXCEPTION
    WHEN CASE_NOT_FOUND THEN
      dbms_output.put_line(‘在CASE语句中缺少与‘ || v_sal || ‘相关的条件‘);
END;

DECLARE
  TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  ename_table ename_table_type;
BEGIN
  SELECT ename INTO ename_table(1) FROM emp WHERE empno = &NO;
  dbms_output.put_line(‘雇员名：‘ || ename_table(1));
  EXCEPTION
    WHEN COLLECTION_IS_NULL THEN
      dbms_output.put_line(‘必须使用构造方法初始化集合元素‘);
END;

DECLARE
  CURSOR cur_emp IS
  SELECT ename,sal FROM emp;
BEGIN
  --打开游标
  OPEN cur_emp;
  FOR emp_record IN cur_emp LOOP
    dbms_output.put_line(emp_record.ename);
  END LOOP;
  EXCEPTION
    WHEN CURSOR_ALREADY_OPEN THEN
      dbms_output.put_line(‘游标已经打开‘);
END;

BEGIN
  UPDATE dept SET deptno = 40 WHERE deptno = &NO;
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      dbms_output.put_line(‘在deptno列上不允许重复值‘);
END;

DECLARE
  CURSOR cur_emp IS
  SELECT ename,sal FROM emp;
  TYPE emp_record_type IS RECORD(
    ename emp.ename%TYPE,
    sal emp.sal%TYPE
  );
  emp_record emp_record_type;
BEGIN
  --未打开游标
  FETCH cur_emp INTO emp_record;
  --关闭游标
  CLOSE cur_emp;
  EXCEPTION
    WHEN INVALID_CURSOR THEN
      dbms_output.put_line(‘请检查游标是否已经打开‘);
END;

BEGIN
  UPDATE emp SET sal = sal + ‘1OO‘;
  EXCEPTION
    WHEN INVALID_NUMBER THEN
      dbms_output.put_line(‘输入的数字值不正确‘);
END;

DECLARE
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_sal FROM emp
  WHERE LOWER(ename) = LOWER(‘&ename‘);
  dbms_output.put_line(‘工资：‘ || v_sal);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line(‘不存在该雇员‘);
END;

DECLARE
  v_ename emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_ename FROM emp
  WHERE sal = 3600;
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      dbms_output.put_line(‘返回多行‘);
END;

DECLARE
  num1 INT := 100;
  num2 INT := 0;
  num3 NUMBER(6,2);
BEGIN
  num3 := num1/num2;
  EXCEPTION
    WHEN ZERO_DIVIDE THEN
      dbms_output.put_line(‘除数不能为0‘);
END;

DECLARE
  TYPE emp_array_type IS VARRAY(20) OF VARCHAR2(20);
  emp_array emp_array_type := emp_array_type(‘‘,‘‘);
BEGIN
  dbms_output.put_line(emp_array(3));
  EXCEPTION
    WHEN SUBSCRIPT_BEYOND_COUNT THEN
      dbms_output.put_line(‘超出下标范围‘);
END;

DECLARE
  TYPE emp_array_type IS VARRAY(20) OF VARCHAR2(20);
  emp_array emp_array_type := emp_array_type(‘‘,‘‘);
BEGIN
  dbms_output.put_line(emp_array(-1));
  EXCEPTION
    WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN
      dbms_output.put_line(‘嵌套表或VARRAY下标不能为负‘);
END;

DECLARE
  v_ename VARCHAR2(2);
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE empno = &NO;
  dbms_output.put_line(v_ename);
  EXCEPTION
    WHEN VALUE_ERROR THEN
      dbms_output.put_line(‘变量尺寸不足‘);
END;

2、其他预定义例外

PL/SQL_处理例外1（预定义例外）,布布扣,bubuko.com

PL/SQL_处理例外1（预定义例外）

原文：http://blog.csdn.net/com185272358/article/details/21785269