In PL/SQL, exceptions are categorized into two types: predefined and user-defined. 1. Predefined exceptions are built-in errors such as NO_DATA_FOUND, TOO_MANY_ROWS, VALUE_ERROR, ZERO_DIVIDE, and INVALID_NUMBER, which are automatically raised during specific runtime errors. 2. User-defined exceptions allow developers to manually declare, raise, and handle custom errors using the DECLARE section, RAISE statement, and EXCEPTION block. Additionally, best practices include avoiding empty handlers, using WHEN OTHERS cautiously, logging meaningful messages, and limiting unnecessary custom exceptions to ensure clean, maintainable error handling in PL/SQL programs.

In PL/SQL, exceptions are used to handle runtime errors so that a program can continue running or terminate gracefully. These exceptions can either be predefined (like NO_DATA_FOUND) or user-defined (custom exceptions created by the developer). Knowing how both types work and when to use them is key to writing robust PL/SQL code.

Predefined Exceptions: Built-in Errors You Should Know

Oracle has a set of predefined exceptions that are automatically raised when specific database errors occur. These are the most commonly used and recognized exceptions in PL/SQL.

Some of the more frequently encountered ones include:

• NO_DATA_FOUND: Raised when a SELECT INTO statement returns no rows.
• TOO_MANY_ROWS: Raised when a SELECT INTO returns more than one row.
• VALUE_ERROR: Happens when an arithmetic, conversion, truncation, or size constraint error occurs.
• ZERO_DIVIDE: When division by zero happens.
• INVALID_NUMBER: When converting a string to a number fails due to invalid input.

Here’s how you’d typically catch a predefined exception:

BEGIN
   SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No employee found with that ID.');
END;

You don’t need to declare these exceptions — they’re already known to PL/SQL. Just reference them in your EXCEPTION block.

User-Defined Exceptions: When You Need More Control

Sometimes, predefined exceptions aren’t enough. That’s where user-defined exceptions come in handy. You can define your own exceptions for situations that aren’t covered by Oracle's built-in list.

The process involves three steps:

1. Declaration — Declare your exception in the DECLARE section.
2. Raising — Use the RAISE statement to trigger it manually.
3. Handling — Catch it in the EXCEPTION block like any other.

For example:

DECLARE
   invalid_salary EXCEPTION;
   v_salary NUMBER := -5000;
BEGIN
   IF v_salary < 0 THEN
      RAISE invalid_salary;
   END IF;
EXCEPTION
   WHEN invalid_salary THEN
      DBMS_OUTPUT.PUT_LINE('Salary cannot be negative.');
END;

This gives you control over what constitutes an error in your logic. You can also associate user-defined exceptions with error codes using PRAGMA EXCEPTION_INIT, which lets you link them to specific Oracle error numbers if needed.

Best Practices for Exception Handling

When working with exceptions in PL/SQL, there are a few important things to keep in mind:

• Avoid empty exception handlers: Swallowing exceptions without logging or handling them can hide bugs.
• Use WHEN OTHERS carefully: It catches all unhandled exceptions, but should usually end with a RAISE or logging mechanism.
• Log meaningful messages: Help future developers (including yourself) understand what went wrong.
• Don’t overuse user-defined exceptions: Only create custom exceptions when they truly add clarity or control.

Also, remember that exception blocks are executed only when an error occurs, so avoid putting regular logic inside them unless necessary.

Wrapping Up

Handling exceptions in PL/SQL boils down to understanding two main types — predefined and user-defined — and knowing when and how to use each. While Oracle provides many built-in exceptions for common database issues, defining your own can help manage application-specific logic errors. Either way, good exception handling makes your programs more reliable and easier to debug.

That's basically it — not overly complicated, but easy to overlook some details if you're not careful.

The above is the detailed content of How are exceptions handled in PL/SQL (predefined, user-defined)?. For more information, please follow other related articles on the PHP Chinese website!