How do you use exception handling in PL/SQL to manage errors?

In PL/SQL, exception handling is used to manage errors that may occur during the execution of a program. Exception handling involves identifying the type of error that occurred and taking appropriate actions to handle the error.

In order to use exception handling in PL/SQL, you need to define one or more exception handlers in your program. An exception handler is a block of code that is executed when a particular exception is raised. The general syntax for defining an exception handler is as follows:

BEGIN
   -- Some code here
EXCEPTION
   WHEN exception1 THEN
      -- Code to handle exception1
   WHEN exception2 THEN
      -- Code to handle exception2
   WHEN others THEN
      -- Code to handle all other exceptions
END;

In this syntax, ‘exception1‘ and ‘exception2‘ are specific exception names, and ‘others‘ is a special exception name that handles all exceptions that are not caught by other handlers. Within each exception handler block, you can include code to handle the specific exception that has been raised.

Here’s an example of how you might use exception handling in PL/SQL:

DECLARE
   balance NUMBER := 1000;
   withdrawal_amount NUMBER := 2000;
BEGIN
   IF withdrawal_amount > balance THEN
      RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');
   ELSE
      balance := balance - withdrawal_amount;
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

In this example, we have a ‘balance‘ variable that starts at 1000 and a ‘withdrawal_amount‘ variable that is set to 2000. We check to see if the withdrawal amount is greater than the balance, and if it is, we raise an application error using the ‘RAISE_APPLICATION_ERROR‘ procedure. This will cause the program to jump to the exception handler block and execute the ‘DBMS_OUTPUT.PUT_LINE
(SQLERRM)‘ statement, which will display the error message associated with the exception.

In summary, exception handling in PL/SQL involves defining one or more exception handler blocks to handle specific types of errors that may occur during program execution. By using exception handling, you can create more robust and error-tolerant programs that can gracefully handle unexpected situations.