|
JAVA Developer 2003年10月号より転載
・(9)ストアド・プロシージャ
PL/SQLでプロシージャを記述した経験がある開発者が一番取り組みやすく、かつパフォーマンスも良好といえるのが、「SQLプロシージャ」と呼ばれるものです。PL/SQLの記述法のように、簡単な制御文とSQL文からストアド・プロシージャを構成できます。ちなみにDB2では、C、C++、Javaなど多様な言語をサポートしています。
DB2のSQLプロシージャは、Cコンパイラを使用してコンパイルされます。Cコンパイラは、DB2製品自体には同梱されておらず、使用するプラットフォームにあわせて別途準備する必要があります。また、デフォルトと異なるCコンパイラを使用する場合には、レジストリ変数も変更する必要があります。
DB2のSQLプロシージャは、PL/SQLに比べ文法が貧弱なところがありますが、基本的用件は十分対応可能です（表2）。
リスト3、4は、Oracleのストアドプロシージャと同等機能を、DB2 SQLプロシージャで記述したものです。
表2 DB2におけるオブジェクト名の長さ
| オブジェクト |
最大長（バイト数） |
| スキーマ，列 |
30 |
| 表，ビュー（視点），シノニム（別名），ストアドプロシージャ |
128 |
| 索引，制約，UDF，UDT，トリガー，表スペース，バッファプール |
18 |
表3 ストアド・プロシージャ関連の機能対応表
| データ型 |
対応具合 |
解説 |
| BOOLEAN |
△ |
数値で対応 |
| レコード |
△ |
要素を分解して個別に値を扱わなくてはいけない |
| コレクション（VARRAY,ネスト表） |
× |
SQLプロシージャでは配列は扱えない |
| カーソル変数 |
× |
|
| SUBTYPE |
○ |
|
| %TYPE,%ROWTYPE |
△ |
直接記述する |
| 定数（CONSTANT） |
△ |
変数にする |
| 変数に対するNOT NULL制約 |
× |
|
| カーソル処理 |
対応具合 |
解説 |
| 明示カーソル処理 |
○ |
DECLARE、OPEN、FETCH、CLOSE |
| %FOUND |
△ |
NOT FOUND条件ハンドラを使用する |
| %NOTFOUND |
△ |
NOT FOUND条件ハンドラを使用する |
| SQL%NOTFOUND |
△ |
NOT FOUND条件ハンドラを使用する |
| %ISOPEN |
△ |
カーソルOPEN時にプログラム側でフラグを作成する |
| %ROWCOUNT |
△ |
アプリケーションで直接カウントする |
| SQL%ROWCOUNT |
○ |
GET DIAGNOSTICSを使用する |
| %BULKROWCOUNT |
× |
|
| %TYPE,%ROWTYPE |
△ |
直接記述する |
| 汎用カーソル |
○ |
PREPARE文を使用する |
| 例外処理 |
対応具合 |
解説 |
| 内部定義例外 |
○ |
Oracleで定義されている例外にあわせて個別に事前に例外を定義して対応 |
| ユーザー定義例外 |
○ |
条件ハンドラを定義して対応 |
| ユーザー定義システム例外（PRAGMA） |
○ |
SIGNALでSQLSTATE70000〜90000の例外を発生させる。受け手はハンドラで処理 |
| SQLCODE |
○ |
ただしDECLAREが必要な点がOracleと異なる |
| SQLERRM |
○ |
GET DIAGNOSTICS EXCEPTION句を使用する |
| ステートメント |
対応具合 |
解説 |
| 指数演算子（**） |
○ |
POWERを使う |
| FORALL |
× |
コレクション |
| ブロック |
○ |
複合ステートメントを使用する |
| IF THENほか制御文 |
○ |
ELSIFはELSEIF |
| WHILE |
○ |
|
| カウンタFORループ |
△ |
FORステートメントでカウンタを設定できないので、WHILE文に書き直す |
| 明示カーソル |
○ |
|
| カーソルFORループ |
○ |
|
| GOTO |
○ |
|
|
リスト3 簡単なストアド・プロシージャの例（Oracle）
|
CREATE or REPLACE PROCEDURE procRowtype
(v_job IN staff%TYPE)
IS
CURSOR staffline IS SELECT id,name,dept,job,years,salary,comm
FROM staff WHERE JOB=v_job;
staffrec staffline%ROWTYPE;
BEGIN
OPEN staffrec;
LOOP
FETCH staffline INTO staffrec;
EXIT WHEN staffline%NOTFOUND;
INSERT INTO tab1 VALUES(staffrec.ID,staffrec.NAME,staffrec.DEPT,
staffrec.SALARY,staffrec.COMM);
COMMIT;
END LOOP;
CLOSE staffline;
END;
/
|
|
リスト4 SQLプロシージャの例（DB2）
|
DROP PROCEDURE procRowtype@
CREATE PROCEDURE procRowtype
(IN v_job CHAR(5))
LANGUAGE SQL
BEGIN
DECLARE staff_id SMALLINT;
DECLARE staff_name VARCHAR(9);
DECLARE staff_dept SMALLINT;
DECLARE staff_salary DEC(7,2);
DECLARE staff_comm DEC(7,2);
DECLARE vbreak INT DEFAULT 0;
DECLARE curstaff CURSOR WITH HOLD FOR SELECT ID, NAME, DEPT,SALARY,COMM
FROM STAFF WHERE JOB=v_job;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET vbreak = 1;
END;
OPEN curstaff;
L1:LOOP
FETCH curstaff INTO staff_id,staff_name,staff_dept,
staff_salary,staff_comm;
IF vbreak != 0 THEN
LEAVE L1;
END IF;
INSERT INTO tab1 VALUES(staff_id,staff_name,staff_dept,
staff_salary,staff_comm);
COMMIT;
END LOOP L1;
CLOSE curstaff;
END
@
|
・(10)ストアド・ファンクション
Oracleのストアドファンクションは、DB2ではUDF（ユーザー定義関数）に相当します。UDFは、Oracleと同じくCREATE FUNCTION文で作成します。記法は、変数宣言や制御文に関してはSQLプロシージャと同じ。制限事項も表2の項目が当てはまります。なおUDFでは、DML文（INSERT、DELETE、UPDATE）を関数の中で発行できないという制限があります。
リスト5、6はストアドファンクションの例です。
|
リスト5 簡単なストアドファンクションの例（Oracle）
|
CREATE OR REPLACE FUNCTION get_stock_status(v_wine_id IN CHAR)
RETURN VARCHAR2
IS
stock_num WINE_MASTER.NUM%TYPE;
BEGIN
SELECT num INTO stock_num FROM wine_master WHERE wine_id=v_wine_id;
IF stock_num = 0 THEN
RETURN '在庫切れです。';
ELSIF stock_num<10 THEN
RETURN '残りわずか！';
ELSE
RETURN '';
END IF;
END;
/
|
|
リスト6 UDFの例（DB2）
|
DROP FUNCTION get_stock_status2@
CREATE FUNCTION GET_STOCK_STATUS2 (V_WINE_ID CHAR(3) )
RETURNS VARCHAR(20)
BEGIN ATOMIC
DECLARE stock_num INTEGER;
SET stock_num=(SELECT NUM FROM WINE_MASTER
WHERE WINE_ID = V_WINE_ID);
IF STOCK_NUM = 0 THEN
RETURN '在庫切れです。';
ELSEIF STOCK_NUM < 10 THEN
RETURN '残りわずか！';
ELSE
RETURN CAST (NULL AS CHAR(1));
END IF;
END@
|
・(11)クラスター索引
クラスター索引とは、表データの格納先を決める基準となる索引です。この索引を作成することで、データは索引の順序に従って挿入されていきます。その結果、索引の順と表データの並び順が近い場合（たとえば索引を使用した範囲検索において）、読み込むデータページ数を少なくすることができます（図2）。
図2 クラスター索引の概念図
関連リンク
 JAVA Developer
定期購読のご案内
バックナンバー販売協力店
|
JAVA Developer 10月号
大特集
再入門 J2SE
特集2 Oracle9i Application Server
［特別企画］
・例外処理のメカニズム
・データベース移行術（２）
「Oracle→DB2編」
・Tomcatで試すJava2セキュリティ
|
|
