エンタープライズ:特集 2003/08/24 11:32:00 更新

[JAVA Developer特別企画]2003年10月号
知っておきたいデータベース移行術「Oracle→DB2編」 (3/7)

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特別企画]
知っておきたいデータベース移行術「Oracle→DB2編」
・データベースオブジェクト
・データ型
・ビルトイン関数
・SQL文
・ロックと読み取り一貫性

関連リンク
▼JAVA Developer
▼定期購読のご案内
▼バックナンバー販売協力店

JAVA Developer10月号表紙 JAVA Developer 10月号

大特集
再入門 J2SE

特集2 Oracle9i Application Server
[特別企画]
・例外処理のメカニズム
・データベース移行術(2)
 「Oracle→DB2編」
・Tomcatで試すJava2セキュリティ

前のページ | 1 2 3 4 5 6 7 | 次のページ

[竹内 笑子,JAVA Developer]



Special

- PR -

Special

- PR -