事例3および事例4では、SQL文の完了に関連付けられたハンドラー アクションによって発生した条件が、どのように呼び出し側のストアド プロシージャやアプリケーションに報告されるかが重要な側面です。
- 発生した条件が例外を出すことなく処理された場合は、ステータス変数に完了の条件が反映されます。発生した問題に関する情報については、一切呼び出し側に通知されません。そのため、ストアド プロシージャで失敗が起きてそれが処理された場合、呼び出し側は、失敗が起きたことやトランザクションがロールバックされたことに気付きません。
条件の発生に関する情報を把握するためには、アプリケーションのルールのような適切なメカニズムを定義する必要があります。
- 完了条件ハンドラーに関係したハンドラ アクション内の文が、正常完了以外の完了条件を引き起こし、その条件に対する適当なハンドラーが存在しない場合、そのハンドラ句内の次の文へと実行は継続します。ステータス変数には、元の完了条件を反映した値が入れられます。
ハンドラー アクションが完了するとただちにステータス変数は、そのハンドラ アクションが正常完了したことを表わすように設定されます。
- ハンドラー アクション句で例外条件が発生する可能性があることが分かっている場合は、このような例外を処理するため、ストアド プロシージャの作成時にハンドラー アクション句の中に適切なハンドラーを入れることができます。ハンドラーは、必要に応じて何重かの入れ子にすることができます。
例: ハンドラーによって発生した例外
この例では、ハンドラ アクションによって引き起こされた例外が処理されずに残され、このハンドラーを呼び出した元の条件が外側に伝搬されます。
CREATE PROCEDURE spSample2(IN pName CHARACTER(30), IN pAmt INTEGER) BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '23505' INSERT INTO Error_Tbl VALUES (:SQLSTATE,CURRENT_TIMESTAMP, 'spSample2', 'Failed to insert record'); ... L1:BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23505' BEGIN INSERT INTO Proc_Error_Tbl VALUES (:SQLSTATE, CURRENT_TIMESTAMP, spSample2', 'Failed to Insert record'); END; INSERT INTO tab1 VALUES (pName, pAmt); INSERT INTO tab1 VALUES (pName, pAmt); -- Duplicate row error ... END L1; ... END;
テーブルtab1が次のようにして作成されるとします。
CREATE SET TABLE tab1(c1 CHARACTER(30), c2 INTEGER);
次のようにしてテーブルProc_Error_Tblを削除します。
DROP TABLE Proc_Error_Tbl;
次のようにしてプロシージャspSample2を実行します。
CALL spSample2('Richard', 100);
ストアド プロシージャの実行中に、複合文L1の最後のINSERT文で、重複行例外が発生します。SQLSTATE '23505'に対して宣言されているCONTINUEハンドラーが呼び出されます。ハンドラ アクション文(INSERT)で、別の例外'42000'が発生します。
このハンドラーの中にはSQLSTATE '42000'を処理するためのハンドラーがないので、このハンドラーを呼び出した元の条件であるSQLSTATE '23505'は外側に伝搬されます。外側の複合文には、SQLSTATE '23505'に対して定義されているEXITハンドラーがあります。このハンドラーが例外を処理し、制御が複合文を終了します。プロシージャには他に文が含まれていないので、プロシージャが終了します。
例: ハンドラー内で発生した完了条件の無視
この例では、ハンドラー内で発生した完了条件が無視されます。
CREATE PROCEDURE spSample1(IN pName CHARACTER(30), IN pAmt INTEGER) BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23505' BEGIN DELETE FROM temp_table; INSERT INTO Proc_Error_Tbl VALUES (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample1', 'Failed to Insert record'); END; INSERT INTO tab1 VALUES (pName, pAmt); INSERT INTO tab1 VALUES (pName, pAmt); -- duplicate row error ... END;
テーブルtemp_tableおよびtab1が次のように定義されているとします。
CREATE TABLE temp_table(c1 INTEGER, c2 CHARACTER(30)); CREATE SET TABLE tab1(c1 CHARACTER(30), c2 DECIMAL(18,2));
次のようにしてプロシージャを実行します。
CALL spSample1('Richard', 10000);
最後のINSERT文で重複行例外が発生し、このエラーに対して宣言されているCONTINUEハンドラーが呼び出されます。ハンドラ アクション句のDELETE文で「no data found」完了条件が発生します。
このハンドラーの中ではこの条件を処理するためのハンドラーが定義されていないので、条件は無視され、ハンドラー アクション句の中の次の文(INSERT)からストアド プロシージャの実行が続けられます。
例: ハンドラー アクション句の内外で発生した条件の結合
この例は、ハンドラー アクション句の内外で発生する例外が混じりあった例で、ハンドラー アクションで発生する例外が処理されずに残される様子を示しています。
REPLACE PROCEDURE han1(INOUT IOParam1 INTEGER, INOUT IOParam2 CHARACTER(20)) Loutermost: BEGIN DECLARE Var1 INTEGER DEFAULT 10; L1: BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '42000' -- Statement 3_1a SET IOParam2 = 'Table does not exist in the outer block'; DECLARE EXIT HANDLER FOR SQLSTATE '23505' L2: BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '23505' -- Statement 3_2a SET IOParam2 = ' Duplicate row error '; DECLARE EXIT HANDLER FOR SQLSTATE '42000' BEGIN -- Statement 3_3a SET IOParam2 = 'Nonexistent table in inner block '; -- Statement 3_3b INSERT INTO tab1 VALUES (IOParam1); -- duplicate row error END; -- Statement 3_3c INSERT INTO notable VALUES (IOParam1, IOParam2); -- 42000 END L2; /* End Label L2 */ -- Statement 3_4a DELETE tab1 ALL; -- Statement 3_4b SET IOParam1 = Var1; -- Statement 3_4c INSERT INTO tab1 VALUES (IOParam1); -- Statement 3_4d INSERT INTO tab1 VALUES (IOParam1); -- duplicate row error END L1; /* End Label L1 */ END Loutermost;
ストアド プロシージャの実行中に、INSERT文(Statement 3_4d)で、重複行例外が発生します。SQLSTATE ’23505’に対して定義されている最初のEXITハンドラーが、L1というラベルの同じ複合文の中にあるので呼び出されます。
次にL2のStatement 3_3cにおいて、SQLSTATE '42000'の例外が発生します。この例外を処理するために、’42000’に対して定義されているEXITハンドラーが呼び出されます。INSERT文(ハンドラー内のStatement 3_3b)で、重複行例外が発生します。ハンドラーの中にはこの新しい条件を処理するためのハンドラーがないので、ハンドラーは終了します。
最も外側のハンドラーを呼び出した、SQLSTATE '23505'に対応する元の条件が外側に伝搬されます。一番外側の複合文であるLoutermostにも、この条件に適したハンドラはないので、ストアド プロシージャは'23505'に対応するエラーで終了します。
例: 入れ子のストアド プロシージャにおける条件ハンドラー
このセクションの例は、以下のストアド プロシージャに基づいています。
CREATE PROCEDURE spSample7a() BEGIN DECLARE hNumber INTEGER; -- Statement_7a_1 UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1001; -- Statement_7a_2 INSERT INTO EmpNames VALUES (1002, ’Thomas’); -- Statement_7a_3 UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1003; END;
EmpNamesテーブルが削除されている場合、上記ストアド プロシージャのStatement_7a_2は、SQLSTATEコード’42000’のエラーを戻します。このエラーのための条件ハンドラーは定義されていないので、このエラーは処理されません。
2番目のプロシージャはStatement_7b_2で最初のプロシージャを呼び出すことに注意してください。
2番目のストアド プロシージャ定義を考慮します。
CREATE PROCEDURE spSample7b() BEGIN DECLARE hNumber INTEGER; DECLARE EXIT HANDLER FOR SQLSTATE '42000' INSERT INTO Proc_Error_Table (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample7b', 'Failed to Insert Row'); -- Statement_7b_1 SELECT nextEmpNum INTO hNumber FROM EmpNext; UPDATE Employee SET nextEmpNum = hNumber+1; -- Statement_7b_2 CALL spSample7a(); -- Statement_7b_3 UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1003; END;
例: ハンドラーアクション発生条件を報告するためのANSIセッション モード
この例では、以下の3つのSQL文がBTEQからANSIセッション モードで対話式に呼び出されることを想定しています。
INSERT INTO Department VALUES (’10’, ’Development’); UPDATE Employee SET Salary_Amount = 10000 WHERE Employee_Number = 1000; CALL spSample7b();
上記の3つのSQLステートメントがANSIセッション モードで呼び出されると、次の一連のイベントが起こります。
- Statement_7b_2としてマークされたストアド プロシージャ文は、ストアド プロシージャspSample7aを呼び出します。
- ストアド プロシージャspSample7a内のStatement_7a_2は、SQLSTATEコード'42000'の例外を発生させます。
- spSample7bで条件ハンドラーが定義されていないため、制御は例外とともに呼び出し元のプロシージャspSample7aに戻ります。
- 例外はspSample7bで処理され、ハンドラー アクションが実行されます。
- ハンドラー タイプがEXITなので、制御は呼び出し元の複合文を終了します。
- 以下の項目は未コミットのまま残されます。
- 最初の2つの対話式SQL文
- Statement_7a_1から呼び出されるspSample7a
- spSample7bから呼び出されるStatement_7b_1
- spSample7b内の条件ハンドラーから呼び出されるINSERT文。
- 以下の項目は実行できません。
- spSample7aから呼び出されるStatement_7a_3
- spSample7bから呼び出されるStatement_7b_3
- プロセスの終わり。