Stored procedure exception handling

 DECLARE

 TotalUpd   NUMBER(36) := 0;
 BEGIN
    dbms_output.put_line ('Job Start time............... : ' 
        || TO_CHAR(SYSDATE, '             hh24:mi:ss'));
    UPDATE Asset SET _status = 'PROGRESS' WHERE status IS null;
    TotalUpd := SQL%ROWCOUNT; 
    IF TotalUpd = 0 THEN
        dbms_output.put_line ('No more data to update.');
    ELSE
        dbms_output.put_line('Total Records Updated. : '
            || TotalUpd);
    END IF; 
    COMMIT; 
 EXCEPTION 
 WHEN OTHERS THEN
    dbms_output.put_line ('Error while status as SUCCESS '); 
 END; 

Declare @val1 int;  
Declare @val2 int;  
BEGIN TRY  
Set @val1=8;  
Set @val2=@val1/0; /* Error Occur Here */  
END TRY  
BEGIN CATCH  
Print 'Error Occur that is:'  
Print Error_Message()  
END CATCH  

Exception handling is mainly used for Transaction Management. Let us see an example.
Begin Transaction Trans  
   Begin Try  
      Delete From Employee Where Employee.Emp_IID<3  
      Update Employee Set Employee.First_Name='Pankaj kumar' 
      Where Employee.Emp_IID='6th' /* Error Will Occur Here */  
      If @@TranCount>0  
      begin Commit Transaction Trans  
   End  
End Try  
Begin Catch  
   if @@TranCount>0  
   Print 'Error Is Occur in Transaction'  
   begin Rollback Transaction Trans  
End  
End Catch  
  
Select * From Employee