Implement User-defined exceptions - PolarDB

In PL/SQL, errors are called exceptions. When an exception is raised, normal execution stops and control passes to the exception-handling section. PL/SQL has two categories of exceptions:

Predefined exceptions — raised implicitly by the server when a built-in error occurs.
User-defined exceptions — never raised by the server. You raise them explicitly with a RAISE statement when a developer-defined logical rule is violated.

A common example: if a customer tries to cash a check against an account with insufficient funds, the application raises a user-defined exception to signal the business rule violation.

Declare and raise an exception

Declare exceptions in the declaration section of a function, procedure, package, or anonymous block. Then raise them with RAISE:

DECLARE
    exception_name EXCEPTION;

BEGIN
    ...
    RAISE exception_name;
    ...
END;

exception_name is the name you assign to the exception.

Duplicate declarations: Declaring the same exception twice in the same block is not allowed. Declaring the same exception in two different blocks is allowed.

Exception scope

Exceptions follow block-scoping rules:

An exception declared in a block is local to that block and visible to all nested blocks within it.
Outer blocks cannot reference exceptions declared in inner (nested) blocks.
An unhandled exception propagates up the call stack. If it reaches the top without a handler, it is reported to the client application.

Reference an exception from an outer block

To reference an exception declared in an enclosing block, assign a label to that block and qualify the exception name with the label:

block_name.exception_name

Reference an exception from a package

Exceptions declared in a package have package-level scope. Qualify the exception name with the package name when referencing it from outside the package:

inventory_control.out_of_stock

Example: user-defined exception in a package

The following example defines a user-defined exception (overdrawn) in the ar package and raises it inside check_balance.

Because check_balance is part of the ar package, it raises overdrawn without a package qualifier:

CREATE OR REPLACE PACKAGE ar AS
  overdrawn EXCEPTION;
  PROCEDURE check_balance(p_balance NUMBER, p_amount NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY ar AS
   PROCEDURE check_balance(p_balance NUMBER, p_amount  NUMBER)
   IS
   BEGIN
       IF (p_amount > p_balance) THEN
         RAISE overdrawn;
       END IF;
    END;

The following purchase procedure calls check_balance. Because purchase is defined outside the ar package, it must reference the exception with the package-qualified name ar.overdrawn:

CREATE PROCEDURE purchase(customerID INT, amount NUMERIC)
AS
  BEGIN
     ar.check_ balance(getcustomerbalance(customerid), amount);
       record_purchase(customerid, amount);
  EXCEPTION
     WHEN ar.overdrawn THEN
       raise_credit_limit(customerid, amount*1.5);
  END;

When check_balance raises overdrawn, execution jumps to the exception handler in purchase:

EXCEPTION
     WHEN ar.overdrawn THEN
       raise_credit_limit(customerid, amount*1.5);

The handler raises the customer's credit limit, then exits. Execution resumes at the statement immediately following the ar.check_balance call.