Oracle Note VII. PL/SQL exception handling

--Exception handling

Declare

  Snum number: = 0;

Begin

  Snum: = 5/snum;  

  Dbms_output.put_line (Snum);

  When others then

    Dbms_output.put_line (' is error! ');

End

--Custom exceptions

Declare

       Ex_custom_invaild_age exception; --Custom Exception Myerr

       Age int;

Begin

     Age: = & Please enter ages;

     if (age < 0) Then

        Raise Ex_custom_invaild_age; --Throw a custom exception

     Else

        Dbms_output.put_line (' Age is: ' | |     

     End If;

      When Ex_custom_invaild_age Then

        Dbms_output.put_line (' illegal age ');

End

--Throws an application exception

--raise_application_error (Exception number, description);

Declare        

       Age int;

Begin

     Age: = & Please enter ages;

     if (age < 0) Then

        Raise_application_error (-20500, ' age cannot be negative ");

     Else

        Dbms_output.put_line (' Age is: ' | |

     End If;

End

--Non-pre-defined exception

Declare     

     Ex_custom_error exception;

     pragma exception_init (ex_custom_error,-1); --Associate a number with a custom exception,

     --the equivalent of a 1-numbered exception named Ex_custom_error so that the exception can be caught

Begin       

     INSERT INTO Dept values (' AAA ', ' BBB ');

     exception       

       When Ex_custom_error Then

       Dbms_output.put_line (' sector number already exists ');

End

--Exception handling

Declare

  Vsal Emp.sal%type;

Begin

  Select Sal into Vsal from EMP;

  exception

    When Too_many_rows Then

      Dbms_output.put_line (' multiple data ');

    When others then

      Dbms_output.put_line (' Error ');

End

Declare

  Vsal Emp.sal%type;

Begin

  Select Sal into Vsal from emp where empno = 1;

  exception

    When No_data_found Then

      Dbms_output.put_line (' no data ');

    When others then

      Dbms_output.put_line (' Error ');

End

--Exception Log processing

CREATE TABLE Errorlog (

       ID number primary Key,

       Errcode number,

       ErrMsg VARCHAR2 (1024),

       Errdate Date

);

--Create sequence, starting from 1, plus 1 per time

Create sequence seq_errorlog_id start with 1 increment by 1;

Declare

       Vdeptno Dept.deptno%type: = 10;

       Verrcode number;

       Verrmsg VARCHAR2 (1024);

Begin

  Delete from dept where deptno = Vdeptno;

  Commit

  exception

    When others then

      Rollback

      Verrcode: = SQLCODE;

      Verrmsg: = SQLERRM;

      INSERT into errorlog values (Seq_errorlog_id.nextval, Verrcode, verrmsg, sysdate);

      Commit

End

SELECT * from Errorlog;

Oracle Note VII. PL/SQL exception handling