本章では、プロシジャルーチンを利用するアプリケーションの作成方法について説明します。
4.1 プロシジャルーチンを利用する場合
通常のクライアント・サーバ間は、クライアント側にアプリケーションを置き、サーバにはデータベースの検索・更新処理だ けをさせる形態になっています。この場合、クライアントとサーバ間を検索結果などの大量なデータが行き来することで、
クライアント・サーバ間での通信負荷が高くなるため、大規模なトランザクション開発に適していません。プロシジャルーチ ンを利用して、サーバ側でデータベース処理を一括して実行させることで、クライアント・サーバ間の通信負荷を軽減させ ることができます。
本節では、プロシジャルーチンを利用する場合について、以下の項目を説明します。
・ プロシジャルーチンとは
・ プロシジャルーチンを利用するアプリケーションの作成の流れ
・ プロシジャルーチンの定義
・ プロシジャルーチンの実行
・ プロシジャルーチンの処理結果
・ プロシジャルーチン利用時のトランザクション
4.1.1 プロシジャルーチンとは
クライアント側にあるアプリケーションをサーバで実行する形態の場合は、クライアントからサーバにSQL文を送信し、
サーバ側の実行結果をクライアント側で受信して処理を行っています。このとき、SQL文単位で送信・受信を繰り返すた め、クライアント側に通信負荷がかかります。大規模なアプリケーション開発の場合、このようなクライアント側の性能限界 を解消し、開発/保守の生産性を向上させるためにはプロシジャルーチンを利用します。プロシジャルーチンとは、サー バに登録する処理手続きのことです。プロシジャルーチンをサーバに登録しておいて、クライアント側からプロシジャルー チンを呼び出し、サーバ側で一連のトランザクション処理を実行します。プロシジャルーチンとして登録する処理手続き は、プロシジャルーチン内で完結する処理が適しています。たとえば、プロシジャルーチン内でアクセスしたデータを基 に別の表を作成する処理などです。
以下に、プロシジャルーチンを利用する場合のクライアント・サーバ間の処理概要を示します。
図
4.1 プロシジャルーチンを利用したクライアント・サーバ間の処理概要4.1.2 プロシジャルーチンを利用するアプリケーションの作成の流れ
プロシジャルーチンを利用するアプリケーションは、以下の手順で作成します。
1. プロシジャルーチンの定義 2. プロシジャルーチンの実行
上記の指定方法を、その流れにそって次節以降で説明します。
参照
各SQL文の詳細については、“SQLリファレンス”を参照してください。
4.1.3 プロシジャルーチンの定義
プロシジャルーチンをスキーマに登録するには、以下に示すプロシジャルーチン定義文を使用します。
プロシジャルーチン定義文の指定方法
CREATE PROCEDURE SCM.RNAME ( IN PARA INT) (1) (2) (3) (4) (5) BEGIN
DECLARE VAR INT;
SET VAR = PARA + 1;
INSERT INTO SCM.TBL(ITEM) VALUES(VAR);
： END
(1) スキーマ名 (2) ルーチン名 (3) パラメタモード (4) パラメタ名 (5) データ型
プロシジャルーチンで定義できるSQL文をSQL手続き文と呼びます。プロシジャルーチンはSQL手続き文を組み合 わせることで、アプリケーションロジックのルーチンの開発を行います。SQL手続き文には以下のSQL文があります。
・ データ操作文
・ トランザクション管理文(トリガからの呼出し以外)
－ COMMIT文
－ ROLLBACK文
・ SQL制御文
－ 複合文
－ SQL変数宣言
－ IF文
－ CALL文
－ 代入文
－ LOOP文
－ WHILE文
－ REPEAT文
－ LEAVE文
－ GOTO文
－ 分岐先文
－ WHENEVER文
－ 条件宣言
－ ハンドラ宣言
－ SIGNAL文(トリガからの呼出し時)
－ RESIGNAL文(トリガからの呼出し時)
参照
SQL制御文の詳細については、“SQLリファレンス”を参照してください。
プロシジャルーチン定義をファイルに作成し、サーバ側でrdbddlexコマンドを実行してプロシジャルーチンを登録しま
す。rdbddlexコマンドでプロシジャルーチンを定義する場合、注釈を指定することができます。
定義ファイルからプロシジャルーチンを登録する例を定義ファイルからプロシジャルーチンを登録する例に、定義ファイ ルの例を定義ファイルの例に示します。例では、UNIX系の場合を示します。
Windowsの場合は、入力ファイル指定を、C:¥USERS¥DEFAULT¥DDL.DATに変更してください。
例
在庫表から、在庫数量が50以下の製品に対する発注を、倉庫番号によって営業所別のテーブルに振り分けます。
定義ファイルからプロシジャルーチンを登録する例
rdbddlex -d RDBDB -x /home/rdb/DDL/ddl.dat ↑
入力ファイル指定
定義ファイルの例 ddl.dat EXEC SQL
CREATE PROCEDURE 在庫管理.営業所別発注処理(IN 検索キー INT) BEGIN
-- SQL変数宣言
DECLARE 製品番号Ｖ SMALLINT;
DECLARE 製品名Ｖ CHAR(20);
DECLARE 在庫数量Ｖ INT;
DECLARE 倉庫番号Ｖ SMALLINT;
DECLARE 数量Ｖ INT;
DECLARE SQLSTATE CHAR(5);
： ： -- カーソル宣言
DECLARE CUR1 CURSOR FOR
SELECT 製品番号, 製品名, 在庫数量, 倉庫番号 FROM 在庫管理.在庫表;
DECLARE CUR2 CURSOR FOR
SELECT 取引先, 取引製品, 発注数量 FROM 在庫管理.発注表;
：
-- 検索キーに1が指定された場合,CUR1に対する検索と補充テーブルの -- INSERTを行う.
IF 検索キー = 1 THEN OPEN CUR1;
LABEL1:
-- データが終了するまでデータを検索する.
LOOP
FETCH CUR1 INTO 製品番号Ｖ，製品名Ｖ，在庫数量Ｖ，倉庫番号Ｖ;
IF SQLSTATE <> '00000' THEN
LEAVE LABEL1;
END IF;
-- 在庫数量が50以下の場合, 補充データの作成を行う．
IF 在庫数量Ｖ <= 50 THEN -- 補充の数量を算出する．
SET 数量Ｖ = 50 - 在庫数量Ｖ;
-- 倉庫番号によって発注先のテーブルを変更する．
IF 倉庫番号Ｖ = 2 THEN
INSERT INTO 在庫管理.営業所２(製品番号, 製品名, 数量)
VALUES(製品番号Ｖ，製品名Ｖ，数量Ｖ);
IF SQLSTATE <> '00000' THEN LEAVE LABEL1;
END IF;
ELSE
INSERT INTO 在庫管理.営業所１(製品番号, 製品名, 数量)
VALUES(製品番号Ｖ，製品名Ｖ，数量Ｖ);
IF SQLSTATE <> '00000' THEN LEAVE LABEL1;
END IF;
END IF;
END IF;
END LOOP LABEL1;
-- 検索キーに2が指定された場合, CUR2に対する処理を行う．
ELSEIF 検索キー = 2 THEN OPEN CUR2;
： END IF;
END END-EXEC;
4.1.4 プロシジャルーチンの実行
サーバ上のスキーマに登録済のプロシジャルーチンを、クライアント側から呼び出して実行するには、CallableStatement インタフェースのメソッドを使用します。
プロシジャルーチン実行の指定方法 CallableStatement cstmt =
con.prepareCall("{CALL GENERAL.PROC01[?,?,?]}");
(1) (2)
cstmt.setInt(1,7); (3) cstmt.setString(2,"bird"); (3) cstmt.registerOutParameter(2,java.sql.Types.CHAR); (4) cstmt.registerOutParameter(3,java.sql.Types.INTEGER); (4) cstmt.executeUpdate(); (5) (1) スキーマ名
(2) プロシジャルーチン名
(3) プロシジャルーチンへの入力パラメタの設定 (4) JDBC型の登録
(5) ストアドプロシジャの実行
4.1.5 プロシジャルーチンの処理結果
ここでは、プロシジャルーチン内での処理結果の確認方法と、プロシジャルーチンの呼出し元のアプリケーションでの処 理結果の確認方法について説明します。
プロシジャルーチン内での処理結果の確認
プロシジャルーチン内の出力パラメタの情報を、CallableStatement.getXXXメソッドで取得できます。registerOutParameter メソッドでJDBCデータ型を指定し、getXXXメソッドでデータを取得してください。
プロシジャルーチン内のSQL手続き文の実行中に、例外コード40の例外（ロールバック例外）が発生した場合は、トラ ンザクションをロールバックし、呼出し元のアプリケーションに無条件に復帰します。このときの例外事象は、呼出し元に エラーメッセージとSQLSTATEを通知します。
プロシジャルーチン内のSQL手続き文の実行中に、例外コード40以外の例外が発生した場合は、エラーメッセージと
SQLSTATEを通知します。
プロシジャルーチンの呼出し元での処理結果の確認
例外によってプロシジャルーチンが実行されない、またはプロシジャルーチン内で例外が発生して処理が中断された場 合は、その例外事象が呼出し元のSQLSTATEおよびSQLMSGに通知されます。プロシジャルーチンが実行されない 場合とは、アクセス規則違反や、プロシジャルーチンのパラメタ間の代入エラーが発生した場合などがあります。
以下に、具体例を示します。
プロシジャルーチンの呼出し元での処理結果の確認 CallableStatement cstmt = con.prepareCall(
"{CALL GENERAL.PROC01[?,?,?]}");
cstmt.setInt(1,7);
cstmt.setString(2,"bird");
cstmt.registerOutParameter(2,java.sql.Types.CHAR); (1) cstmt.registerOutParameter(3,java.sql.Types.INTEGER); (1) cstmt.executeUpdate();
int iID = 0;
String sName = null;
sName = cstmt.getString(2); (2) iID = cstmt.getInt(3); (2) System.out.println("ID = " + iID); (2) System.out.println("NAME = " + sName); (2) cstmt.close(); (3)
(1)JDBCデータ型の設定
(2)出力パラメタからのデータ取得 (3)オブジェクトのクローズ
4.1.6 プロシジャルーチン利用時のトランザクション
JDBCドライバを使用したアプリケーションでのトランザクション状態の変更は、commitメソッドまたはrollbackメソッドで 行います。
プロシジャルーチンに、SQL文のCOMMIT文やROLLBACK文が定義されていると、エラーが発生する場合がありま す。プロシジャルーチンには、COMMIT文やROLLBACK文を定義しないでください。