一文看懂基本的oracle预定义异常和自定义异常以及oracle异常函数

oracle预定义异常

语法格式如下:

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

直接上代码:

 DECLARE
  v_deptno dept.deptno%TYPE := &deptno;
  v_dname  dept.dname%TYPE;
  v_str    VARCHAR2(50);
BEGIN
  SELECT dname INTO v_dname FROM dept WHERE deptno >= v_deptno;
  SELECT decode(MOD((40 - length(v_dname)) / 2, 2),
                0,
                lpad('*', (40 - length(v_dname)) / 2, '*'),
                lpad('*', trunc((40 - length(v_dname)) / 2), '*')) ||
         v_dname ||
         decode(MOD((40 - length(v_dname)) / 2, 2),
                0,
                lpad('*', (40 - length(v_dname)) / 2, '*'),
                lpad('*', trunc((40 - length(v_dname)) / 2), '*'))
    INTO v_str
    FROM dual;
  dbms_output.put_line('****************************************');
  dbms_output.put_line(v_str);
  dbms_output.put_line('****************************************');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('无锡');
  WHEN TOO_MANY_ROWS THEN
    dbms_output.put_line('盐城');
  WHEN OTHERS THEN
    NULL;
END;

oracle自定义异常

直接上代码

DECLARE
  v_deptno NUMBER := 500;
  v_name   VARCHAR2(20) := 'Testing';
  e_invalid_department EXCEPTION;
BEGIN
  UPDATE departments
     SET department_name = v_name
   WHERE department_id = v_deptno;
  IF SQL % NOTFOUND THEN
    RAISE e_invalid_department;
  END IF;
  COMMIT;
EXCEPTION
  WHEN e_invalid_department THEN
    DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

oracle异常函数

直接上代码

DECLARE
  v_deptno NUMBER := 270;
  v_name   VARCHAR2(20) := 'Testing';
BEGIN
  UPDATE departments
     SET department_name = v_name
   WHERE department_id = v_deptno;
  IF SQL%FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Warning!');
  END IF;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE);
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    ROLLBACK;
END;

文中代码参考了一部分墨天轮讲座，便于后期回顾，特此记录