pl/sql---异常处理

异常处理

预定义异常

DECLARE
  V_ENAME VARCHAR2(20);
BEGIN
  SELECT ENAME INTO V_ENAME FROM EMP 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('数据过多');
END;
/

CREATE TABLE ERR_INFO(
  ERR_INFO    VARCHAR2(500),
  TS          VARCHAR2(30)
  
);
SELECT * FROM ERR_INFO;   ----日志表

DECLARE
  V_ENAME VARCHAR2(20);
BEGIN
/*  SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=1234;
*/  SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=7369;
  DBMS_OUTPUT.PUT_LINE(V_ENAME);
  INSERT INTO ERR_INFO VALUES('程序正常执行',TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
  COMMIT;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
      INSERT INTO ERR_INFO VALUES('数据不存在',TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
      COMMIT;
      RAISE;
      WHEN TOO_MANY_ROWS THEN
        INSERT INTO ERR_INFO VALUES('数据过多',TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
        COMMIT;
        RAISE;
      WHEN OTHERS THEN
        INSERT INTO ERR_INFO VALUES('程序异常',TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
        COMMIT;
        RAISE;
END;

非预定义异常

DECLARE
 V_ENAME VARCHAR2(20);
  ZS         EXCEPTION;
  PRAGMA EXCEPTION_INIT(ZS,-00093);
BEGIN
  SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=111;
  DBMS_OUTPUT.PUT_LINE(V_ENAME);
  INSERT INTO ERR_INFO VALUES('程序正常执行',TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
  COMMIT;
  EXCEPTION 
    WHEN ZS THEN
     DBMS_OUTPUT.PUT_LINE('出错了');
      RAISE;
      WHEN TOO_MANY_ROWS THEN
        INSERT INTO ERR_INFO VALUES('数据过多',TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
        COMMIT;
        RAISE;
    
END;

自定义异常

DECLARE
   V_ENAME VARCHAR2(20) :='&ENAME';
   SMITH  EXCEPTION;
   PRAGMA EXCEPTION_INIT(SMITH,-20001);
BEGIN
  IF V_ENAME='SMITH' THEN 
   /* RAISE SMITH;*/
   RAISE_APPLICATION_ERROR(-20001,'SMITH是元老  不能走');
  ELSE
    DELETE FROM EMP1 WHERE ENAME=V_ENAME;
    DBMS_OUTPUT.PUT_LINE(V_ENAME||'已离职');
  END IF;
  EXCEPTION
    WHEN SMITH THEN
      DBMS_OUTPUT.PUT_LINE('出错了');
      RAISE;
END;

捕获错误信息

DECLARE
  V_DEPTNO  NUMBER;
  V_SAL_SUM NUMBER;
  V_ERRM    VARCHAR2(1000);
BEGIN
  SELECT DEPTNO,SUM(SAL) INTO V_DEPTNO,V_SAL_SUM FROM EMP;
  EXCEPTION 
    WHEN OTHERS THEN
      V_ERRM :=SQLERRM;
      DBMS_OUTPUT.put_line(V_ERRM);
  
END;