PolarDB:User-defined exceptions

Any number of errors (referred to in PL/SQL as exceptions) can occur during program execution. When an exception is thrown, normal execution of the program stops, and control of the program transfers to the error-handling portion of the program. An exception may be a predefined error that is generated by the server, or may be a logical error that raises a user-defined exception.

User-defined exceptions are never raised by the server. Instead, they are raised explicitly by a RAISE statement. A user-defined exception is raised when a developer-defined logical rule is broken. A common example of a logical rule being broken occurs when a check is presented against an account with insufficient funds. An attempt to cash a check against an account with insufficient funds will provoke a user-defined exception.

You can define exceptions in functions, procedures, packages, or anonymous blocks. You cannot declare the same exception twice in the same block, but you can declare the same exception in two different blocks.

Before implementing a user-defined exception, you must declare the exception in the declaration section of a function, a procedure, a package, or an anonymous block. You can then raise the exception by using the RAISE statement:

DECLARE
    exception_name EXCEPTION;

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

exception_name is the name of the exception.

Unhandled exceptions propagate back through the call stack. If the exception remains unhandled, the exception is eventually reported to the client application.

User-defined exceptions declared in a block are considered to be local to that block and global to any nested blocks within the block. To reference an exception that resides in an outer block, you must assign a label to the outer block, and then preface the name of the exception with the block name:

block_name.exception_name

Conversely, outer blocks cannot reference exceptions declared in nested blocks.

The scope of a declaration is limited to the block in which it is declared unless it is created in a package, and when referenced, qualified by the package name. For example, to raise an exception named out_of_stock that resides in a package named inventory_control, a program must raise an error named:

inventory_control.out_of_stock

The following example demonstrates declaring a user-defined exception in a package. The user-defined exception does not require a package qualifier when it is raised in check_balance, because it resides in the same package as the exception:

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 procedure (purchase) calls the check_balance procedure. If p_amount is greater than p_balance, check_balance raises an exception, and purchase catches the ar.overdrawn exception. purchase must refer to the exception with a package qualified name (ar.overdrawn) because purchase is not defined within the ar package.

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 ar.check_balance raises an exception, execution jumps to the exception handler defined in purchase:

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

The exception handler raises the credit limit of the customer and ends. When the exception handler ends, execution resumes with the statement that follows ar.check_balance.