自定义异常的声明语法格式如下：

exception_name EXCEPTION;

自定义异常也可以使用PRAGMA EXCEPTION_INIT``(exception_name, error_code)``;

语句跟现有的错误码关联起来，其中，error_code是 OceanBase 已有的错误码编号。

示例：声明和处理自定义异常

delimiter /
CREATE OR REPLACE PROCEDURE sp_test_user_exception(p_w_id IN  ware.w_id%TYPE )
AS
    TYPE TYPE_REFCURSOR IS REF CURSOR ;
    ref_cursor  TYPE_REFCURSOR;
    ware_name   ware.w_name%TYPE;
    READ_NOTHING EXCEPTION;
    PRAGMA EXCEPTION_INIT(READ_NOTHING,-4026);
BEGIN
    OPEN ref_cursor FOR SELECT w_name FROM ware WHERE ( w_id = p_w_id AND p_w_id IS NOT NULL ) OR (p_w_id IS NULL );
    FETCH ref_cursor INTO ware_name ;
    IF ref_cursor%NOTFOUND  THEN 
        RAISE READ_NOTHING;
    ELSE 
        dbms_output.put_line('Fetch a ware name : ' || ware_name || '.');
    END IF;
    CLOSE ref_cursor;
EXCEPTION 
    WHEN CURSOR_ALREADY_OPEN THEN
        dbms_output.put_line('Handle a CURSOR_ALREADY_OPEN exception. ');
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line('Handle a NO_DATA_FOUND exception. ');
    WHEN TOO_MANY_ROWS THEN
        dbms_output.put_line('Handle a TOO_MANY_ROWS exception. ');
END;
/
delimiter ;
obclient> set serveroutput on;
Query OK, 0 rows affected (0.00 sec)
obclient> call sp_test_user_exception(2);
Query OK, 0 rows affected (0.05 sec)
Fetch a ware name : L6xwRsbDk.
obclient> call sp_test_user_exception(3);
Query OK, 0 rows affected (0.00 sec)
Handle a NO_DATA_FOUND exception.
obclient>

在这个示列中将一个自定义异常跟错误码 -4026 关联，实际上就是跟 OceanBase 预定义异常 NO_DATA_FOUND 进行关联。所以，在最后面的异常处理部分能被处理。语句 RAISE READ_NOTHING;也可以直接改为 RAISE NO_DATA_FOUND;，效果是一样的。

另外一种用户自定义异常可以通过 RAISE_APPLICATION_ERROR 方法直接抛出，不能被异常处理逻辑捕获。RAISE_APPLICATION_ERROR 语法格式如下：

RAISE_APPLICATION_ERROR( error_number, error_message )

其中，error_number 是从 -20000 到 -20999 之间的整数，error_message提示相应的信息（少于2048字节）。示例如下：

delimiter /
CREATE OR REPLACE PROCEDURE sp_test_exception(p_w_id IN  ware.w_id%TYPE )
AS
    TYPE TYPE_REFCURSOR IS REF CURSOR ;
    ref_cursor  TYPE_REFCURSOR;
    ware_name   ware.w_name%TYPE;
    sum_ytd     number(10,2) := -1;
BEGIN
    IF p_w_id IS NULL THEN 
        RAISE_APPLICATION_ERROR(-20000, 'Warehouse ID is NULL!');
    END IF; 
    OPEN ref_cursor FOR SELECT w_name FROM ware WHERE w_id = p_w_id ;
    FETCH ref_cursor INTO ware_name ;
    IF ref_cursor%NOTFOUND  THEN 
        dbms_output.put_line('Fetch a ware name but ref cursor return NOTFOUND.');
    ELSE 
        dbms_output.put_line('Fetch a ware name : ' || ware_name || '.');
    END IF;
    CLOSE ref_cursor;
EXCEPTION 
    WHEN CURSOR_ALREADY_OPEN THEN
        dbms_output.put_line('Handle a CURSOR_ALREADY_OPEN exception [' || sqlcode || ':' || sqlerrm || '] !');
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line('Handle a NO_DATA_FOUND exception [' || sqlcode || ':' || sqlerrm || '] !');
    WHEN TOO_MANY_ROWS THEN
        dbms_output.put_line('Handle a TOO_MANY_ROWS exception [' || sqlcode || ':' || sqlerrm || '] . ');
    WHEN OTHERS THEN 
        dbms_output.put_line('Raise an unkown exception [' || sqlcode || ':' || sqlerrm || '] !');
        RAISE ;
END;
/
delimiter ;
obclient> call sp_test_exception(null);
ERROR-20000: Warehouse ID is NULL!
obclient>