第 5 章エラー処理コーディング お断り : 当資料は DB2 Universal Database for Linux, UNIX and Windows V8.2 をベースに作成されています
この章で学ぶこと エラーハンドリングとは? SQL プロシージャで使用可能なエラーハンドリング手法 SQLCODE と SQLSTATE シグナル 条件ハンドラー ロギング エラーハンドリング実装時の注意点 2
エラーハンドリングとは? (1/3) SQL プロシージャにおけるエラー 処理できない SQL が発行された時 ( 例 ) 実行対象の表が存在しない NOT NULL の列に NULL 値を INSERT( 制約違反 ) 等 システム側のエラー ( 例 ) 表スペースにアクセスできない ( ディスク障害 ) ログスペースが一杯になる 等 SQL プロシージャでエラーが起きると エラーシグナルが発生する 3
エラーハンドリングとは? (2/3) シグナルとは? エラーが起きるとシグナルが ( システムによって ) 自動的に発生発生します シグナルが発生すると その時点で制御制御の流れがれが一時中断一時中断します ユーザはシグナルシグナルが発生発生したした際に自動的自動的に呼び出されるされるコードコードを書くことができます 条件ハンドラー ( シグナルハンドラー ) 4
エラーハンドリングとは? (3/3) 条件ハンドラーが無いプロシージャでシグナルが発生すると その呼び出し元 もしくはもしくは外側外側のブロックブロックにシグナルシグナルが通知通知されます その呼び出し元でも条件ハンドラーを実装していない場合 さらにその呼び出し元へとシグナルが伝播していきます 最初の呼び出し元まで戻っても条件ハンドラーが無い場合は そのプログラムがエラー終了します DB2 クライアント : CALL SP1() SIGNAL SP1() CALL SP2() SIGNAL SP2() < エラー!> つまり DB2 ストアドプロシージャでのエラーハンドリングとは条件ハンドラーを正しく実装する事 5
SQLCODE と SQLSTATE エラー発生時にシグナルが発生する エラーにも色々な種類がある シグナルをキャッチした後にエラーの原因を理解し 処理を切り分ける必要がある エラーの内容は SQLCODE か SQLSTATE の 2 つの値を参照する事で判別可能 各ステートメントを実行するたびに SQLCODE と SQLSTATE という値がシステムによって自動的に設定されます ( 書き換えられます ) SQLCODE 整数の状況コード ベンダー独自の番号付け エラー状態や終了条件の判断には SQLCODEを使用する 例 ) カーソル処理の場合は FETCHステートメント終了後にSQLCODEの値を確認 SQLCODE=0の時 FETCHステートメントからデータが戻された SQLCODE=100の時にFETCH ステートメントからデータが戻されない SQLSTATE 5 桁の文字列 ( 整数 5 文字 ) ISO/ANSI SQL92 標準 DBMS 製品に依存依存しないしない値 役割は SQLCODE と同じ 6
条件ハンドラーの宣言 条件ハンドラーは 変数宣言と同様に宣言部 ( コードの前半部分 ) に書く DECLARE *** HANDLER 文を使用 *** の部分は後述 CREATE PROCEDURE proc1 () LANGUAGE SQL DECLARE SQLCODE INT DEFAULT 0; 条件ハンドラー DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ( 条件ハンドラー内のコード ) ; 宣言部 SELECT * FROM... IF... : ロジック部 7
シグナルの動作 (1/2) 条件ハンドラーが定義されていない場合 エラー (SQLEXCEPTION) が発生すると そこで動作が停止し 呼び出し元にシグナルが伝わる CREATE PROCEDURE proc1 : CALL proc2() 1 呼び出し CREATE PROCEDURE proc2 () DECLARE SQLCODE INT DEFAULT 0; : : SELECT * FROM... SET v_num = v_num / 0 IF v_num.. 2 処理の流れ 3エラー!(0による除算) SQLSTATE 22012 でシグナル発生 4 処理はその時点で停止され 呼び出し元にシグナルが伝わる 8
シグナルの動作 (2/2) 条件ハンドラーが定義されている場合 エラー (SQLEXCEPTION) が発生すると そこで動作が停止し 条件ハンドラー内に制御が移る CREATE PROCEDURE proc1 : CALL proc2() 1 呼び出し CREATE PROCEDURE proc2 () DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ( 条件ハンドラー内のコード ) ; SET v_num = v_num / 0 2 処理の流れ 3エラー!(0による除算) SQLSTATE 22012 IF v_num.. でシグナル発生 4 処理はその時点で停止され 条件ハンドラー内に制御が移る 9
条件ハンドラーの書式 条件ハンドラーは 以下の設定を行うことができます 特定の条件にだけをハンドリングする 条件ハンドラー内の処理を行ったあとの処理を選択する DECLARE < 条件ハンドラータイプ > HANDLER FOR < 条件 > < ステートメント > あるステートメント実行後に < 条件 > にマッチする状況が発生した場合 < 条件ハンドラータイプ > に従って < ステートメント > が実行されます ステートメントは 一つの SQL 文か ~ のブロックです 例 ) DECLARE CONTINUE HANDLER FOR SQLEXCEPTION : -- ( 条件ハンドラー内での処理 ) 10
条件ハンドラーにおける < 条件 > DECLARE < 条件ハンドラータイプ > HANDLER FOR < 条件 > < ステートメント > < 条件 > には 次の 4 種類があり 対象となるシグナル ( エラー ) のみをハンドリングします SQLEXCEPTION 全ての エラー をハンドリングします ( 警告などは含まない ) SQLCODE が負の数 SQLSTATE= 01***, 02*** 以外全部 SQLWARNING 全ての警告をハンドリングします SQLCODE=+100 以外の正の数 SQLSTATE= 01*** NOT FOUND クエリーの結果 アンサーセットが 0 行 ( アンサーなし ) だった場合 FETCH の際 結果セットの行がもう無い場合 SQLCODE=+100 SQLSTATE= 02000 カスタム 特定のエラーエラー 警告警告のみをのみをハンドリング 11
条件ハンドラー : カスタム条件 (1/2) カスタム条件は 任意の SQLSTATE のみハンドリングする条件ハンドラー DECLARE < 条件ハンドラータイプ > HANDLER FOR SQLSTATE ' 番号 ' 例 )NULL 制約違反 (SQLSTATE=23502) のみをハンドリングしたい場合 DECLARE < 条件ハンドラータイプ > HANDLER FOR SQLSTATE '23502' -- 処理コード SQLSTATE の番号に独自の名前をつけて定義する事も可能 名前を付けると何の処理の条件ハンドラーなのか 読み手に分かりやすくなる 例 ) 外部キー違反のSQLSTATE=23503にFOREIGN_KEY_VIOLATIONという名前を付ける DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE '23503'; DECLARE < 条件ハンドラータイプ > HANDLER FOR FOREIGN_KEY_VIOLATION... 12
条件ハンドラー : カスタム条件 (2/2) カムタム条件で NOT NULL 制約 (SQLSTATE=23502) に違反している場合の例 : CREATE PROCEDURE proc4(out parasqlcode INT) LANGUAGE SQL DECLARE notnull CONDITION FOR SQLSTATE '23502'; SQLSTATE23502 に notnull という名前を付ける DECLARE EXIT HANDLER FOR notnull SET parasqlcode = SQLCODE; ; 作成した名前を使用して条件ハンドラーを定義 条件ハンドラー内へ処理が移動 INSERT INTO t1 VALUES(1, NULL); --NOT NULL 制約違反が発生 13
( 参考 ) よくあるエラー / 警告 以下に良くハンドリング対象になるエラー / 警告の一部を載せます これ以外にも エラーや警告は定義されています インフォメーションセンターの 参照情報 メッセージ SQL を参照 アンサーセット無しスカラー全選択 SELECT INTOの結果が複数行になった未定義の名前 0による除算ユニーク制約違反 NULL 制約違反挿入または更新の値が親キーの値と同じではない SQLCODE +100-811 -204-801 -803-407 -530 SQLSTATE 02000 21000 42704 22012 23505 23502 23503 14
条件ハンドラータイプ DECLARE < 条件ハンドラータイプ > HANDLER FOR < 条件 > < ステートメント > 条件ハンドラータイプには 3 種類あります シグナルをハンドリングするという意味では同等ですが ハンドリングの際の動作が異なります CONTINUE EXIT UNDO < 条件 > が発生した場合 < ステートメント > を実行し その後 < 条件 > を発生したステートメントの次のステートメントステートメントからから実行実行を継続継続します < 条件 > が発生した場合 < ステートメント > を実行し この条件ハンドラーが定義されている ~ ブロックから実行実行を抜けます < 条件 > が発生した場合 この条件ハンドラーが定義されている ~ ブロックの実行実行をロールバックロールバックした後 < ステートメント > を実行し ~ブロックから実行を抜けます UNDOは ATOMICと定義されたブロック内でのみ指定できます 単にと書いた場合は NOT ATOMICで定義されます 15
条件ハンドラー :CONTINUE CONTINUE ハンドラーは ハンドラーのコードを実行後 シグナルを発生したステートメントの次のステートメントから実行を再開します CREATE PROCEDURE p1() DECLARE SQLCODE INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -- ( ハンドラーの処理 ) 2 ; 1 1SQLEXCEPTION が発生したので条件ハンドラーに処理が移る 2 条件ハンドラーのコードを実行する 3 条件ハンドラーの処理が終了すると SQLEXCEPTION の原因となった次のステートメントから実行を開始 3 UPDATE STAFF SET SALARY=SALARY*1.1 WHERE... SELECT SALARY INTO parasal FROM STAFF; -- SQL0811N が発生 SELECT MAX(SALARY) INTO paramsal FROM STAFF; 16
条件ハンドラー :EXIT (1/2) EXIT ハンドラーはハンドラーのコードを実行後 ブロック (~) から抜けますブロックがプロシージャの最も外のブロックの場合は 呼び出し元に制御が戻ります CREATE PROCEDURE p1() DECLARE SQLCODE INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -- ( ハンドラーの処理 ) 2 1 1SQLEXCEPTION が発生したので条件ハンドラーに処理が移る 2 条件ハンドラーのコードを実行する 3 条件ハンドラーの処理が終了すると ブロックから抜ける この場合プロシージャの最も外のブロックなので呼び出し元に制御が返る ; 3 UPDATE STAFF SET SALARY=SALARY*1.1 WHERE... SELECT SALARY INTO parasal FROM STAFF; -- SQL0811N が発生 SELECT MAX(SALARY) INTO paramsal FROM STAFF; この行は実行されない 17
条件ハンドラー :EXIT (2/2) ブロックが入れ子になっている例 3がEXITハンドラーでの動作 3 はCONTINUEハンドラーの場合の動作 CREATE PROCEDURE p2() DECLARE EXIT HANDLER FOR SQLEXCEPTION 2 --( ハンドラーの処理 ) ; 1 1SQLEXCEPTION が発生したので条件ハンドラーに処理が移る 2 条件ハンドラーのコードを実行する 3 条件ハンドラーの処理が終了すると ブロックから抜け 一つ外のブロックに制御が移る 3 ; 3 SELECT SALARY INTO parasal FROM STAFF; -- SQL0811N が発生 SELECT MAX(SALARY) INTO vmaxsal FROM STAFF; この行は実行されない SELECT SALARY INTO parasal FROM STAFF WHERE ID = 10; : この行から実行再開 18
条件ハンドラー :UNDO UNDO ハンドラーは ブロック内の処理をロールバックしたのち ハンドラーの処理を行い ブロックから抜けます - つまり EXIT との違いは処理をロールバックするか否かです CREATE PROCEDURE p1() ATMIC DECLARE SQLCODE INT DEFAULT 0; 1SQLEXCEPTIONが発生したので条件ハンドラーに処理が移るその際 ブロック内のステートメントステートメントはロールバックされる 2 条件ハンドラーのコードを実行する 3 DECLARE UNDO HANDLER FOR SQLEXCEPTION -- ( ハンドラーの処理 ) 2 ; UPDATE STAFF SET SALARY=SALARY*1.1 WHERE... SELECT SALARY INTO parasal FROM STAFF; -- SQL0811N が発生 1 3 条件ハンドラーの処理が終了すると ブロックから抜ける この場合プロシージャの最も外のブロックなので呼び出し元に制御が返る このステートメントで行った更新はロールバックされる SELECT MAX(SALARY) INTO paramsal FROM STAFF; この行は実行されない 19
SQLCODE と SQLSTATE の取得 (1/3) SQLCODEとSQLSTATEの値を取得するには 変数変数を宣言宣言しておく必要必要がある DECLARE SQLCODE INT DECLARE SQLSTATE CHAR(5) 通常はデフォルト値を指定して DECLARE SQLCODE INT DEFAULT 0 DECLARE SQLSTATE CHAR(5) DEFAULT 00000 と定義します 上記の名前で変数を宣言しておくと SQLSTATE や SQLCODE の値が自動的に上記変数に格納されます 20
SQLCODE と SQLSTATE の取得 (2/3) このサンプルソースコードでは ストアド プロシージャの呼び出し元に SQLEXCEPTION が発生した時の SQLCODE と SQLSTATE を返そうとしています CREATE PROCEDURE proctest(out parasqlcode INT,OUT parasqlstate CHAR(5)) LANGUAGE SQL DECLARE SQLCODE INT DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE EXIT HANDLER FOR SQLEXCEPTION SET parasqlcode = SQLCODE; 1 ; SET parasqlstate = SQLSTATE; 2 しかし このコードは期待した動きをしません SQLCODE と SQLSTATE は 各ステートメントステートメントの実行後にセットセットされるのでされるので 最初最初の SET 文を実行したした際に SQLCODE/SQLSTATE の値が変わってしまうためです ( 右表 ) 変数 SQLCODE SQLSTATE parasqlcode parasqlstate 1 実行前 -810 21000 1 実行後 0 00000-810 2 実行後 0 00000-810 00000 21
SQLCODE と SQLSTATE の取得 (3/3) SQLSTATE と SQLCODE を両方取得する場合は以下のように 一つの SQL 文で 2 つの値を代入する必要があります CREATE PROCEDURE proctest (OUT parasqlcode INT,OUT parasqlstate CHAR(5)) LANGUAGE SQL DECLARE SQLCODE INT DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE EXIT HANDLER FOR SQLEXCEPTION ; SELECT SQLCODE,SQLSTATE INTO parasqlcode,parasqlstate FROM SYSIBM.SYSDUMMY1; SELECT~INTO 文で 2 つの変数への代入をアトミックに ( 一度に ) 行っています この際 FROM 句が必要になるので 実際に存在して 1 行のみ入っている表を指定する必要があります SYSIBM.SYSDUMMY1 などが適当でしょう 22
シグナル (SIGNAL) を使った通知 シグナルをユーザーが発生させる事ができます SIGNAL ステートメントを使用します 任意の SQLSTATE とメッセージテキストを発生させる事ができます SIGNAL SQLSTATE 'XXXXX' SET MESSAGE_TEXT = < メッセージテキスト > XXXXX は5 文字の文字ストリングです V8.2 からは CHAR(5) 型の変数変数を指定指定することもすることも可能可能です SQLSTATEの規則に従う必要があります SET MESSAGE_TEXT= には 任意の文字列を指定します この文字列はSQLCAのSQLERRMCフィールドに戻されます 文字列が70バイトを超える場合 警告が出されずに切り捨てられます 23
複雑な SQL プロシージャのエラー処理と RESIGNAL(1/2) プロシージャが大きくなってくると ブロック内にブロックを持つ構造になる場合がある その場合 内側ブロックで起こったエラーを内側の条件ハンドラーで一旦処理 (1) し その後で 外側ブロックの条件ハンドラーにエラー処理を渡したい (2) 場合があります そのために RESIGNAL ステートメントが用意されています CREATE PROCEDURE p5(...) LANGUAGE SQL m: DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 外側の条件ハンドラー (2) m m1: DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 内側の条件ハンドラー < エラーを起こすステートメント > m1; (1) 24
複雑な SQL プロシージャのエラー処理と RESIGNAL(2/2) RESIGNAL の動作 RESIGNAL を引数無しで呼び出すと ハンドラーで受信し 再度 SIGNAL する事ができます これにより 外側のハンドラーの呼び出しを強制できます RESIGNAL しない場合 受信したシグナルは消えて 外側には渡りません CREATE PROCEDURE p5(...) LANGUAGE SQL m: DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 外側の条件ハンドラー m m1: DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 処理を行う RESIGNAL; < エラーを起こすステートメント > m1; (1) (2) 25
( 参考 )RESIGNAL の構文 RESIGNAL は シグナルの情報を一部変更して送信する事も可能です >>-RESIGNAL-----------------------------------------------------> >--+-----------------------------------------------------------------------------------+-><.-VALUE-. '-+-SQLSTATE--+-------+--+-sqlstate-string-constant-+-+--+------------------------+-' ' variable-name------------' '- signal-information -' '-condition-name------------------------------------' signal-information: --SET--MESSAGE_TEXT-- = --+-variable-name--------------+------- '-diagnostic-string-constant- V8.2 より 変数の指定も可能 26
ロギングについて 質問 : エラーが起こったこった時 端末 ( 画面 ) やログファイルログファイルにエラーエラーが起こったことをこったことを出力出力したいがしたいが SQL プロシージャ言語言語だけでだけでログログをファイル出力出力したいしたい 回答 :DB2 標準ではでは用意用意されていないのでされていないので C 言語でロギングロギング用のプロシージャプロシージャを作成作成し それをそれを呼び出す事で回避回避します 具体例については デバッグ方法 で扱います 27
考慮点 1: 条件ハンドラーが複数有る場合 以下のように条件ハンドラーが複数作成されている場合 DECLARE CONTINUE HANDOLER FOR SQLEXCEPTION... DECLARE EXIT HANDOLER FOR SQLSTATE '23502'... SQLSTATE 23502 が発生すると どちらのハンドラーの処理が行われるのか? DECLARE EXIT HANDOLER FOR SQLSTATE '23502' の方が処理される そちらの方が条件が 特定的 だから 条件ハンドラーは 最も特定的な条件の方が処理される 他の条件ハンドラーは実行されない 宣言の順番は関係ない まったく同じ条件 (FOR 句 ) のハンドラーは複数定義できない (SQLCODE = -782) 28
考慮点 2: エラー処理のパターン エラー処理のパターンには 主に以下の方法が考えられます 1. プロシージャ内ではでは一切一切エラーエラー処理処理をしないをしないパターン エラーが発生した場合 そのエラーは 呼び出し側の条件ハンドラーで捕捉する それ以外の追加情報は取得できない 2. プロシージャ内ではでは 条件条件ハンドラーハンドラーでエラーエラー情報情報の収集収集を行い SIGNAL/RESIGNAL で MESSAGE_TEXT にエラーエラー情報情報を設定設定するするパターン エラーが発生した場合には 呼び出し側の条件ハンドラーで捕捉できる 詳細情報は SQLCAのSQLERRMCにセットされている GET DIAGNOSTIC EXCEPTION 1 < 変数名 >=MESSAGE_TEXTでそのメッセージを取得できる 3. プロシージャ内ではでは 条件条件ハンドラーハンドラーでエラーエラー情報情報の収集収集を行い そのその情報情報を OUT パラメータにセットセットするする また プロシージャプロシージャの EXIT 時に SIGNAL/RESIGNAL しないパターン 呼び出し側は 常に OUTパラメータあるいは戻り値で正常終了あるいはエラーを確認する ( 必要がある ) エラーコードをRETURNで戻している場合には 以下でその値を取得することができる GET DIAGNOSTIC < 変数名 >=RETURN_STATUS; 詳細なエラー情報をOUTパラメータで取得できる クライアントプログラムの書きやすさを考慮すると 2) が推奨される 29
まとめ プロシージャでのエラーハンドリングとは 正しく条件ハンドラーを定義する事 エラーが起こると シグナルが発生 シグナルはシグナルハンドラーで捕捉 SQLSTATE か SQLCODE でエラーの内容を把握できる 条件ハンドラーの処理後の動作は ハンドラータイプによって異なる EXIT - エラー処理後にブロックを抜ける CONTINUE - エラー処理後に エラー発生の次のステートメントから処理を継続 UNDO - 処理を ROLLBACK してから EXIT する エラーハンドリング実装時の注意点 SQLCODE と SQLSTATE の取得方法 30
参考文献 DB2 UDB V8 マニュアル アプリケーション開発ガイド : サーバー アプリケーションのプログラミング アプリケーション開発ガイド : アプリケーションの構築および実行 DB2 Developer Domain の技術白書 http://www.ibm.com/jp/software/data/developer/library/techdoc/db2dev.htm DB2 UDB V8 Application Development http://www.ibm.com/software/data/db2/udb/ad/ SQL プロシージャからのファイル i/o について ( 外部公開されている情報 ) http://www.ibm.com/jp/software/data/developer/library/techdoc/udf.html http://www.ibm.com/developerworks/db2/library/techarticle/0303stolze/0303stolze. html 31