4.3.1 ストアドプロシジャの定義
ストアドプロシジャは,SQLで記述した一連のデータベース処理手続きを,手続きとしてデータベースに登録しておく機能です。
- 〈この項の構成〉
(1) SQLストアドプロシジャの効果
データベースを操作する場合,FETCH文で検索し,データの有無によってUPDATE文,又はINSERT文の発行を繰り返すと,クライアントとサーバ間のオーバヘッドが増大します。このようなデータベースアクセス処理を手続き(プロシジャ)として定義しておくと,CALL文で手続きを呼び出すだけで,一連の処理を実行できるため,クライアントサーバ間のデータの受渡しなどオーバヘッドの抑制が図れます。また,手続き中のSQL文はコンパイルされた形式(SQLオブジェクト)でサーバ側に登録されているため,処理を共用化できる以外に,SQL解析のオーバヘッドも削減できます。
SQLストアドプロシジャの効果を次の図に示します。
(2) SQLストアドプロシジャの定義と実行
SQLストアドプロシジャは,CREATE PROCEDURE又はCREATE TYPE実行時に手続きがデータベースに登録され,DROP PROCEDURE実行時に削除されます。
登録した手続きは,CALL文で呼び出して実行します。
SQLオブジェクトが無効になっている手続きがある場合は,ALTER PROCEDURE又はALTER ROUTINEで手続きを再作成することもできます。
また,既にSQLストアドプロシジャがある場合には,pddefrevコマンドを実行すれば,既にあるSQLストアドプロシジャの定義系SQLを作成できます。処理が似ているSQLストアドプロシジャを新たに作成するときに使用すると便利です。pddefrevコマンドについては,マニュアル「HiRDB Version 9 コマンドリファレンス」を参照してください。
SQLストアドプロシジャの定義と実行を次の図に示します。
(3) SQLストアドプロシジャの例
SQLストアドプロシジャの例として,複数のSQL文と,それらを制御する文(ルーチン制御SQL)を組み合わせて一つの手続き(プロシジャ)として定義する例とそれを呼び出して実行する例を次の図に示します。
- [説明]
-
-
手続きの名前とSQLパラメタの定義
-
複合文の開始
-
SQL変数の宣言
-
文の繰り返しの指定
-
文の途中終了の指定
-
条件分岐の指定
-
値の代入の指定
-
条件分岐の終了
-
文の繰り返しの終了
-
複合文の終了
-
手続きの呼び出し
-
- 注1
-
各SQL文については,マニュアル「HiRDB Version 9 SQLリファレンス」を参照してください。
- 注2
-
この例では,entrydateでソートするためにカーソル宣言のSELECT句に選択項目としてentrydateを指定しています。ただし,この値は参照しないため,FETCH文では対応する埋込み変数を省略して,entrydateの値は取り出しません。
(4) SQLストアドプロシジャのデバッグ
SQLストアドプロシジャのデバッグをする場合,参照するSQL変数,SQLパラメタなどを,ルーチン制御SQLのWRITE LINE文を使用してクライアント側のファイルに出力して行います。WRITE LINE文については,マニュアル「HiRDB Version 9 SQLリファレンス」を参照してください。
SQLストアドプロシジャ中にWRITE LINE文を記述する例を次に示します。
CREATE PROCEDURE proc_1 (IN fromdate date, IN todate date) BEGIN : : WRITE LINE 'fromdate='||char(fromdate); ..........................1 WRITE LINE 'todate='||char(todate); ..............................2 : :
- [説明]
-
-
SQLパラメタ「fromdate」の値を文字列に変換して,ファイルに出力する指定です。
-
SQLパラメタ「todate」の値を文字列に変換して,ファイルに出力する指定です。
-
WRITE LINE文を記述したSQLストアドプロシジャから,WRITE LINE文の値式の値をクライアント側のファイルに出力する場合,クライアント環境定義PDWRTLNFILSZを設定し,UAPからSQLストアドプロシジャを呼び出します。例を次に示します。
- csh(Cシェル)の場合のPDWRTLNFILSZの設定例(HiRDBクライアントがUNIX版の場合)
setenv PDWRTLNFILSZ 4096
- PDWRTLNFILSZの設定例(HiRDBクライアントがWindows版の場合)
PDWRTLNFILSZ=4096
- SQLストアドプロシジャの呼び出し
strcpy(e_fromdate, "2003-06-01"); strcpy(e_todate, "2003-06-30"); EXEC SQL CALL proc_1(IN :e_fromdate, IN :e_todate);
- 出力ファイルの内容
fromdate=2003-06-01 todate=2003-06-30
注 出力ファイルは,クライアント環境定義PDWRTLNPATHで設定します。
デバッグ終了後,WRITE LINE文を記述したSQLストアドプロシジャから,WRITE LINE文の値式の値をファイルに出力する必要がなくなった場合,クライアント環境定義PDWRTLNFILSZを省略してUAPを実行してください。PDWRTLNFILSZの指定を省略すると,SQLストアドプロシジャ中のWRITE LINE文は実行されません。
(5) ストアドプロシジャ中のトランザクションの決着
(a) トランザクションを決着するSQL
次のSQL文をストアドプロシジャ中で実行すると,トランザクションを決着できます。ただし,Cストアドプロシジャ中では,SQL文を実行できません。
-
COMMIT文
-
ROLLBACK文
次のSQLを実行した場合,自動的にCOMMITが行われます。
-
PURGE TABLE文
-
定義系SQL(Javaストアドプロシジャだけ)
次の条件に該当する場合,自動的にROLLBACKが行われます。
-
ROLLBACKが必要なエラー
(b) ストアドプロシジャを再実行する場合の注意事項
ストアドプロシジャ中でトランザクションを決着した後にエラーが発生した場合,そのプロシジャの実行は途中で終了します。エラーになったストアドプロシジャを再実行する場合,プロシジャの処理は先頭から実行されるため,エラー発生前のトランザクション解決までに行った操作が,二重に実行されてよいかどうかを考慮する必要があります。例を次に示します。
(6) 結果集合返却機能(SQLストアドプロシジャ限定)
SQLストアドプロシジャ定義時に,CREATE PROCEDUREのDYNAMIC RESULT SETS句に1以上の値を指定した場合,結果集合返却機能を使用できます。なお,SQLストアドファンクションについては,結果集合返却機能は使用できません。
(a) 結果集合返却機能とは
SQLストアドプロシジャ内での,SELECT文の実行によって得られるカーソルを,SQLストアドプロシジャの呼び出し元で参照する機能を,結果集合返却機能といいます。
結果集合返却機能の概要を次の図に示します。
(b) 結果集合返却機能を使用できる呼び出し元の言語
結果集合返却機能を使用できる呼び出し元の言語を次に示します。
-
Java
-
C
-
C++
-
COBOL※
-
OOCOBOL
- 注※
-
RDBファイル入出力機能を使用していない場合,使用できます。
(c) 結果集合返却機能の使用例
SQLストアドプロシジャ内で,表emps_1,及び表emps_2に対して,id<10の条件を満たす列id,name,及びageを取得します。呼び出し元で2個の結果集合を受け取り,これらを操作します。
-
SQLストアドプロシジャの定義
CREATE PROCEDURE proc2(IN param1 INTEGER) ............... 1 DYNAMIC RESULT SETS 2 ................................. 2 BEGIN DECLARE CUR1 CURSOR WITH RETURN ..................... 3 FOR SELECT id,name,age FROM emps_1 WHERE id < param1 ORDER BY id; DECLARE CUR2 CURSOR WITH RETURN ..................... 4 FOR SELECT id,name,age FROM emps_2 WHERE id < param1 ORDER BY id; OPEN CUR1; .......................................... 5 OPRN CUR2; .......................................... 6 END; .................................................. 7
- [説明]
-
-
プロシジャ名,パラメタの定義
-
返却する検索結果情報数の指定
-
カーソル(CUR1)の宣言
-
カーソル(CUR2)の宣言
-
カーソル(CUR1)のオープン
-
カーソル(CUR2)のオープン
-
呼び出し終了,及び結果集合返却
-
-
呼び出し元(C言語の埋込み型UAPの場合)
#include <stdio.h> #include <string.h> main() { EXEC SQL BEGIN DECLARE SECTION; struct { short len; char str[31]; } cur1; int emp_id; char emp_name[13]; int emp_age; EXEC SQL END DECLARE SECTION; --------(HiRDBへのCONNECT処理(省略))-------- cur1.len = sprintf(cur1.str, "cursor1"); .................... 1 EXEC SQL CALL PROC(10); ..................................... 2 If (SQLCODE == 120) { ....................................... 3 EXEC SQL ALLOCATE GLOBAL :cur1 FOR PROCEDURE PROC2; ........................... 4 printf("*** emps_1 ***\n"); ............................... 5 while (1) { ............................................... 5 EXEC SQL FETCH GLOBAL :cur1 ............................. 5 INTO :emp_id,:emp_name,:emp_age; ............. 5 if (SQLCODE<0 || SQLCODE==100) break; ................... 5 printf("ID=%d NAME=%s AGE=%d\n", ...................... 5 emp_id, emp_name, emp_age); ....................... 5 } ......................................................... 5 CLOSE GLOBAL :cur1; ....................................... 6 if (SQLCODE==121) { ....................................... 7 printf("*** emps_2 ***\n"); ............................. 8 while (1) { ............................................. 8 EXEC SQL FETCH GLOBAL :cur1 ............................... 8 INTO :emp_id,:emp_name,:emp_age; ........... 8 if (SQLCODE<0 || SQLCODE==100) break; ................. 8 printf("ID=%d NAME=%s AGE=%d\n", .................... 8 emp_id, emp_name, emp_age); ..................... 8 } ....................................................... 8 CLOSE GLOBAL :cur1; ..................................... 9 } } }
- [説明]
-
-
カーソル名の設定
-
CALL文の実行
-
返却結果集合があるかどうかの確認
-
カーソルの割り当て(1個目の結果集合とカーソルを関連づけます)
-
1個目の結果集合からの情報を出力
-
カーソルを閉じる(2個目の結果集合とカーソルを関連づけます)
-
次の結果集合があるかどうかの確認
-
2個目の結果集合からの情報を出力
-
カーソルを閉じる
-
(d) 結果集合返却機能を使用する場合の注意事項
-
CREATE PROCEDUREでのSQLストアドプロシジャ定義時
-
結果集合として返却するカーソルのカーソル宣言には,「WITH RETURN」を指定してください。
-
結果集合として返却するカーソルは,「WITH RETURN」指定で宣言したカーソルのうち,プロシジャ終了時に開いた状態のカーソルだけです。
-
返却する結果集合が2個以上の場合,カーソルを開いた順序で返却されます。
-
-
呼び出し元作成時
-
結果集合を返却するプロシジャを実行した場合,SQLSTATEに0100C,SQLCODEに120が設定されます。
-
埋込み型UAP,及びSQLストアドプロシジャで結果集合を受け取る場合には,ALLOCATE CURSOR文で結果集合の組にカーソルを割り当て,その先頭の結果集合とカーソルを関連づけます。返却される結果集合が2個以上の場合,2個目以降の結果集合は,前の結果集合を参照しているカーソルに対してCLOSE文を実行すると,カーソルと関連づけられます。CLOSE文を実行した場合に,次の結果集合があり,その結果集合とカーソルを関連づけたときには,SQLSTATEに0100D,SQLCODEに121が設定されます。次の結果集合がない場合には,SQLSTATEに02001,SQLCODEに100が設定されます。
-