例外エラーには、システム内部例外、定義済み例外、ユーザー定義例外の3種類があります。
機能の適用範囲
この内容はOceanBaseデータベースEnterprise Editionにのみ適用されます。OceanBaseデータベースCommunity EditionはMySQLモードのみを提供します。
システム内部例外
システムが暗黙的に定義する例外エラーは、一般的にSQL実行時のエラーであり、例えばデッドロックなどが該当します。システム内部例外はOceanBaseデータベースのPLエンジンによって自動的にスローされますが、例外名の定義はありません。プログラムは SQLCODE と SQLERRM を使用して詳細を取得できます。
この種の例外には、次の2つの処理方法があります：
PRAGMA EXCEPTION_INITを使用して宣言し、その後EXCEPTION HANDLEでその名前をキャッチします。EXCEPTION HANDLEのOTHERSを使用してキャッチし、SQLCODEとSQLERRMで詳細を取得します。
名前付けキャッチ
EXCEPTION_INIT の構文は以下のとおりです：
PRAGMA EXCEPTION_INIT(handle_name, sql_err_code);
ここで、handle_name は例外の名前であり、この名前を使用して EXCEPTION 内でキャッチできます。sql_err_code は対応するデータベースエラー番号です。
例：名前付けキャッチを使用してシステム内部例外をキャッチします。
obclient> CREATE TABLE dept(
dept_id NUMBER(10,0),
dname VARCHAR(15),
loc VARCHAR(20),
CONSTRAINT pk_dept PRIMARY KEY(dept_id)
);
Query OK, 0 rows affected
obclient> INSERT INTO dept VALUES (100,'ACCOUNTING','Los Angeles'),(110,'OPERATIONS','CHICAGO'),
(111,'SALES','NEW YORK');
obclient> DECLARE
DUPLICATED_DEPT_ID EXCEPTION;
PRAGMA EXCEPTION_INIT(DUPLICATED_DEPT_ID, -5024);
BEGIN
UPDATE dept SET dept_id=110
where dept_id=100;
EXCEPTION
WHEN DUPLICATED_DEPT_ID THEN
DBMS_OUTPUT.PUT_LINE('Duplicated Department ID!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
/
Query OK, 0 rows affected
Duplicated department id!
OTHERSを使用したキャッチ
例：OTHERS を使用してシステム内部例外をキャッチします。
obclient> DECLARE
BEGIN
UPDATE dept SET dept_id=110
where dept_id=100;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Err Found: '||SQLCODE);
END;
/
Query OK, 0 rows affected
Err Found: -5024
プリペアド例外
OceanBaseデータベースのPLエンジンでプリペアドされている例外は、PLプログラムの実行時によく発生する問題でもあります。プリペアド例外はOceanBaseデータベースが自動的にスローし、明確な例外名を持ち、プログラム内で直接キャッチできます。例えば、SELECT... INTO... は NO_DATA_FOUND 例外をトリガーする可能性があります。
このような例外状況を処理するには、PLブロックの例外処理部分で対応する例外名を直接参照し、適切なエラー処理を行うだけです。
例：
obclient> CREATE TABLE employees(
empno NUMBER(4,0),
empname VARCHAR(10),
job VARCHAR(10),
deptno NUMBER(2,0),
salary NUMBER(7,2),
CONSTRAINT PK_emp PRIMARY KEY (empno)
);
Query OK, 0 rows affected
obclient>INSERT INTO employees VALUES (200,'Jennifer','AD_ASST',1,15000),
(202,'Pat','MK_REP',3,12000),(113,'Karen','PU_CLERK', 4,null),(201,'Michael','MK_MAN',3,9000);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient>DECLARE
v_empid employees.empno%TYPE;
v_sal employees.salary%TYPE;
BEGIN
v_empid := 100;
SELECT salary INTO v_sal FROM employees
WHERE empno=v_empid;
IF v_sal<=10000 THEN
UPDATE employees SET salary=salary+100 WHERE empno=v_empid;
DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' updated');
ELSE
DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' ignored');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- 従業員番号 v_empid が存在しない場合、NO_DATA_FOUND 例外をトリガー
DBMS_OUTPUT.PUT_LINE('Employee id '||v_empid||' not found');
WHEN TOO_MANY_ROWS THEN
-- 従業員番号 v_empid が複数存在する場合、TOO_MANY_ROWS 例外をトリガー
DBMS_OUTPUT.PUT_LINE('Duplicated id: '||v_empid);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
/
Query OK, 0 rows affected
Employee id 100 not found
プリペアド例外の情報は以下の表のとおりです：
定義済み例外 |
エラーコード |
|---|---|
| ACCESS_INTO_NULL | -6530 |
| CASE_NOT_FOUND | -6592 |
| COLLECTION_IS_NULL | -6531 |
| CURSOR_ALREADY_OPEN | -6511 |
| DUP_VAL_ON_INDEX | -1 |
| INVALID_CURSOR | -1001 |
| INVALID_NUMBER | -1722 |
| LOGIN_DENIED | -1017 |
| NO_DATA_FOUND | +100 |
| NO_DATA_NEEDED | -6548 |
| NOT_LOGGED_ON | -1012 |
| PROGRAM_ERROR | -6501 |
| ROWTYPE_MISMATCH | -6504 |
| SELF_IS_NULL | -30625 |
| STORAGE_ERROR | -6500 |
| SUBSCRIPT_BEYOND_COUNT | -6533 |
| SUBSCRIPT_OUTSIDE_LIMIT | -6532 |
| SYS_INVALID_ROWID | -1410 |
| TIMEOUT_ON_RESOURCE | -51 |
| TOO_MANY_ROWS | -1422 |
| VALUE_ERROR | -6502 |
| ZERO_DIVIDE | -1476 |
ユーザー定義例外
プログラムの実行中に、プログラマが異常と判断する状況が発生した場合、ユーザーはプログラム内でその例外を定義し、明示的に例外を発生させたうえで、適切な処理を行う必要があります。
ユーザー定義例外は、RAISE ステートメントを明示的に呼び出してトリガーでき、通常はアプリケーションロジックの異常処理に使用されます。基本的な手順は以下のとおりです：
PLプログラムの
DECLARE部分で例外名を定義します。構文はexception_name EXCEPTIONです。PLプログラム内で明示的に例外をトリガーします。構文は
RAISE exception_nameです。PLプログラムの
EXCEPTION部分で、例外状況に対応する処理を行います。構文はWHEN exception_name THENです。
例：
obclient> DECLARE
v_empid employees.empno%TYPE;
v_sal employees.salary%TYPE;
-- 1.例外名 SALARY_NOT_SET を定義する
SALARY_NOT_SET EXCEPTION;
BEGIN
v_empid := 113;
SELECT salary INTO v_sal FROM employees
WHERE empno=v_empid;
IF v_sal<=10000 THEN
UPDATE employees SET salary=salary+100 WHERE empno=v_empid;
DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' updated');
ELSIF v_sal is NULL THEN
-- 2. v_sal がNULLの場合、この例外をトリガーする
RAISE SALARY_NOT_SET;
ELSE
DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' ignored');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee id '||v_empid||' not found');
-- 3. SALARY_NOT_SET 例外を処理する
WHEN SALARY_NOT_SET THEN
DBMS_OUTPUT.PUT_LINE('Salary not set: '||v_empid);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
/
Query OK, 0 rows affected
Salary not set: 113
カスタムエラーコード
OceanBaseデータベースは、カスタムエラーコードを定義するプロシージャ RAISE_APPLICATION_ERROR とエラーメッセージを提供しており、関数内でカスタムエラーをスローし、関数呼び出し時に例外をキャッチして処理するなど、エラー処理をより柔軟にします。
構文は以下のとおりです：
RAISE_APPLICATION_ERROR(error_number,error_message ) ;
ここで、
error_numberはエラーコードで、範囲は -20000 から -20999 です。error_messageは対応するエラーメッセージで、最大長は 2048 バイトです。
例：
obclient> DECLARE
v_empid employees.empno%TYPE;
v_sal employees.salary%TYPE;
BEGIN
v_empid := 103;
SELECT salary INTO v_sal FROM employees
WHERE empno=v_empid;
IF v_sal is NULL THEN
-- エラー 20999 をスロー
RAISE_APPLICATION_ERROR(-20999, 'The salary of employee is not found');
ELSIF v_sal<=1500 THEN
UPDATE employees SET salary=salary+100 WHERE empno=v_empid;
DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' updated');
ELSE
DBMS_OUTPUT.PUT_LINE('Employee '||v_empid||' ignored');
END IF;
END;
/
OBE-20999: The salary of employee is not found