OceanBase支持的预定义异常如下表所示。

示例：多个预定义异常处理程序

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
    OPEN ref_cursor FOR SELECT sum(w_ytd) sum_ytd  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 sum_ytd;
    dbms_output.put_line('Fetch a ware ytd sum : ' || to_char(nvl(sum_ytd, '-999.99')) || '.');
    CLOSE ref_cursor;
    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 
        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;
    SELECT w_name INTO ware_name FROM ware WHERE ( w_id = p_w_id AND p_w_id IS NOT NULL ) OR (p_w_id IS NULL );
    dbms_output.put_line('Fetch again a ware name  : ' || ware_name || '.');
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. ');
    WHEN OTHERS THEN 
        dbms_output.put_line('Raise an unkown exception !');
        RAISE ;
END;
/
delimiter ;
obclient> set serveroutput on;
Query OK, 0 rows affected (0.00 sec)
obclient> select w_id,w_ytd,w_name from ware;
+------+-------+------------+
| W_ID | W_YTD | W_NAME     |
+------+-------+------------+
|    2 |  1200 | L6xwRsbDk  |
|    1 |  1200 | n1P4zYo8OH |
+------+-------+------------+
2 rows in set (0.01 sec)
obclient> call sp_test_exception(NULL);
Query OK, 0 rows affected (0.03 sec)
Fetch a ware ytd sum : 2400.
Fetch a ware name : L6xwRsbDk.
Handle a TOO_MANY_ROWS exception.
obclient>
obclient> call sp_test_exception(1);
Query OK, 0 rows affected (0.01 sec)
Fetch a ware ytd sum : 1200.
Fetch a ware name : n1P4zYo8OH.
Fetch again a ware name  : n1P4zYo8OH.
obclient>
obclient> call sp_test_exception(3);
Query OK, 0 rows affected (0.00 sec)
Fetch a ware ytd sum : -999.99.
Fetch a ware name but ref cursor return NOTFOUND.
Handle a NO_DATA_FOUND exception.
obclient>