今回は、Oracle マイグレーションの最終回として、ストアドプロシジャの移行についてご紹介します。 ストアドプロシジャは、サーバプログラムとしてコンパイルされたコードがデータベースに格納されて実行されるので、クライアント側でプログラムロジックを作成する必要がありません。 業務で頻繁に行われる処理をストアドプロシジャで作成することで、クライアントの負荷を下げることができます。 また、ストアドプロシジャでよく使用される関数はあらかじめ、システムに組み込まれています。

PowerGres Plus は、Oracle の PL/SQL 言語と同等の PL/pgSQL 言語により、ストアドプロシジャを作成することができます。 2 つの言語構造は非常に似ているので、簡単なものであれば、ほとんど修正せずに使用できます。

第 5 回は、ストアドプロシジャの移行のポイントについて説明します。

• 第 2 回 データベース定義の移行
• 第 3 回 データの移行
• 第 4 回 アプリケーションの移行
• 第 5 回 ストアドプロシジャの移行 (今回)

図 1: データベース移行の概要

Oracle の PL/SQL と PowerGres Plus の PL/pgSQL の構文は非常に似ていますが、細かい部分が違います。 PowerGres Plus が復帰値を返却するファンクション 1 つに統一され、呼び出しをする場合は、SELECT 文に統一されています。 Oracle には復帰値なしのプロシジャと復帰値があるファンクションの 2 つの種類があり、呼び出しを行うプログラム言語が JDBC の場合は、呼び出し方法を変更する必要があります。

図 2: Oracle と PowerGres Plus のストアドプロシジャ

ストアドプロシジャの移行で、特に気を付けなければならない 3 つのポイントについて説明します。

• OUT パラメタの扱い
• 組み込み関数
• 例外処理

Oracle のストアドプロシジャで複数の OUT パラメタを使用している場合は、CREATE TYPE で OUT パラメタに対応する値を格納できるデータ型を定義します。 そのデータ型を復帰値に指定したファンクションを定義します。

インスタンス作成時に最初からシステムに登録されている関数を「組み込み関数」と呼びます。 PowerGres Plus と Oracle で名前が異なる関数の場合は対応する関数名に変更します。 PowerGres Plus に最初から登録されていないものは 1 度登録すれば、後は全く同じように使用できます。 サンプル一覧 の add_functions.sql を psql コマンドで登録してご利用ください。

PowerGres Plus では、エラー判定は SQL 文の直後に FOUND 変数で行い、その後の処理を一箇所にまとめて記述し、エラー処理の実行や例外を発生させます。 Oracle では例外処理を行う EXCEPTION ブロックを作成して、エラーが発生した場合はそのブロックでエラー処理を行います。

RAISE レベル 'フォーマット' [, 変数 [, ...]];

レベルには、DEBUG、LOG、NOTICE、WARNING、EXCEPTION の 5 種類があり、EXCEPTION を指定した場合のみ、エラーを発生し、現在のトランザクションを終了させます。 ストアドプロシジャの処理は 1 つのトランザクションと見なされるため、すべての SQL 文による処理がロールバックされます。 ストアドプロシジャを作成する場合は、トランザクションのロールバックが発生してもよい単位で SQL 文の処理を行うことが必要です。

EXCEPTION を記述した場合

val1 = 1;
RAISE EXCEPTION '' TEST (%).'', val1;

RAISE 文の実行結果

ERROR TEST (1).

今まで Oracle マイグレーションで使用してきた「賃貸情報検索システム」に以下の表を追加し、その情報を操作する処理で説明します。

• 契約者情報
• 連帯保証人

サンプル一覧 の create_table_sample.sql を以下のように psql コマンドで実行します。

図 3: 表定義とデータ挿入

OUT パラメタを復帰値に指定した関数を定義します。 以下に、契約者番号と、契約更新日を入力すると、契約更新日を更新し、入居年数を返却する場合を例に説明します。

Oracle の場合

CREATE PROCEDURE contractor_update_proc(p_customer_id IN number,
                                        p_contract_update_date IN date,
                                        out_year_from_moving OUT number) IS
BEGIN
    UPDATE contractor SET contract_update_date = p_contract_update_date
        WHERE customer_id = p_customer_id;
    SELECT ((p_contract_update_date - moving_house + 364) / 365) INTO out_year_from_moving
        FROM contractor WHERE customer_id = p_customer_id;
END;

PowerGres Plus の場合

CREATE FUNCTION contract_update_func(integer, date) RETURNS integer AS '
DECLARE
    p_customer_id ALIAS FOR $1;
    p_contract_update_date ALIAS FOR $2;
    ret_val integer;
BEGIN
    UPDATE contractor SET contract_update_date = p_contract_update_date
        WHERE customer_id = p_customer_id;
    SELECT (p_contract_update_date - moving_house + 364) / 356 INTO ret_val
        FROM contractor WHERE p_customer_id = customer_id;
    return ret_val;
END;
' LANGUAGE 'plpgsql';

このように、OUT パラメタが 1 個の場合は、その OUT パラメタを返却する関数を定義します。

復帰値に複数の値を返却できるユーザ定義型を定義し、そのデータ型を復帰値に指定します。

家賃を延滞している契約者番号および延滞日数を入力し、請求額および契約者電話番号の情報を返却する場合を例に説明します。

Oracle の場合

CREATE PROCEDURE nonpayment_rent_proc(IN 契約者番号,
                                      IN 延滞日数,
                                      OUT 請求額,
                                      OUT 契約者電話番号) IS
BEGIN
    ...
END;

PowerGres Plus の場合 (create_nonpayment_rent_func.sql)

作成のポイントは以下の 3 つです。
• 2 個以上の値を返却できるように、CREATE TYPE 文でユーザ定義型を定義
• ユーザ定義型を関数の復帰値に指定
• 復帰値を格納する変数をレコード型で定義し、その変数を返却する

      CREATE TYPE ret_type_nonpayment_rent AS (amount_claimed integer, telephon_no varchar);

CREATE FUNCTION nonpayment_rent_func(integer, integer) RETURNS ret_type_nonpayment_rent AS '
DECLARE
    p_customer_id ALIAS FOR $1;
    p_nopayment_rent_days ALIAS FOR $2;
    ret_val record;
BEGIN
    SELECT (rent_price / 30) * p_nopayment_rent_days + rent_price, telephon
        INTO ret_val FROM contractor WHERE p_customer_id = customer_id;
    RETURN ret_val;
END;
' LANGUAGE 'plpgsql';

OUT パラメタが 2 個以上の場合は、アプリケーションからの呼出し方法も変わります。 OUT パラメタが複数ある場合のストアドプロシジャの呼出し方法を、JDBC の場合を例に説明します。

サンプル一覧 の create_nonpayment_rent_func.sql を psql コマンドで登録します。

図 4: ストアドプロシジャの登録

CallNonpaymentRentFunc.java というサンプルを用意しておりますので、そのプログラムをご覧ください。

作成のポイントは以下の 2 つです。

• 関数の呼出しは、SELECT 文を使用する
• 取り出したレコードセットには、ユーザ定義型と同じ順で値が設定されているので、その値を取り出す

環境変数 CLASSPATH に PowerGres Plus でインストールした JDBC ドライバを設定し、javac コマンドでソースファイルをコンパイルします。

export CLASSPATH=.:/usr/local/pgsqlplus/share/java/jdbc3/postgresql.jar:$CLASSPATH
javac ./CallNonpaymentRentFunc.java

正常に終了すれば、CallNonpaymentRentFunc.class ファイルが作成されます。

契約者番号 101 の入居者が、10 日間家賃を滞納しているとしましょう。 家賃は、75,000 円なので、滞納分の 25,000 円を加算した請求額として 100,000 と、電話番号の 111-111-1111 が表示されるはずです。

図 5: ストアドプロシジャの Java アプリケーションからの呼び出し

事前に登録の必要な関数と代替可能な機能で対処できるものがあります。以下に頻繁に使用されると思われる関数の一覧を記述してありますので、ご覧ください。

サンプル一覧 の add_functions.sql を、psql コマンドで登録します。

試しに CONCAT 関数を実行してみましょう。 CONCAT 関数は、指定した 2 個のパラメタの文字列を結合して返却する関数です。

図 6: CONCAT 関数実行例

契約更新をするとき、関数のパラメタに指定した契約者番号が存在しなかったら、例外を発生させる場合を例に説明します。

• エラー判定は、各 SQL 文の直後で行うように修正
• EXCEPTION ブロック相当の処理は、関数の復帰の前にまとめて処理を定義
• 各 SQL 文の後の結果判定は、FOUND 変数を使用

Oracle の場合

CREATE PROCEDURE contractor_update_proc2(p_customer_id IN number,
                                         p_contract_update_date IN DATE,
                                         out_year_from_moving OUT number) IS
BEGIN
    SELECT ((p_contract_update_date - moving_house + 364) / 365) INTO out_year_from_moving FROM contractor WHERE customer_id = p_customer_id;
    UPDATE contractor SET contract_update_date = p_contract_update_date;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        raise_application_error(100, 'Data not found');
END;

PowerGres Plus の場合 (create_contract_update_func2.sql)

CREATE FUNCTION contract_update_func2(integer, date) RETURNS integer AS '
DECLARE
    p_customer_id ALIAS FOR $1;
    p_contract_update_date ALIAS FOR $2;
    w_errorkind char(1);
    ret_val integer;
BEGIN
    w_errorkind := ''S'';
    LOOP
        SELECT (p_contract_update_date - moving_house + 364) / 356 INTO ret_val
            FROM contractor WHERE p_customer_id = customer_id;
        IF FOUND = false THEN
            w_errorkind := ''E'';
            EXIT;
        END IF;
        EXIT;
    END LOOP;
--
-- EXCEPTION BLOCK
--
    IF w_errorkind = ''E'' THEN
        RAISE EXCEPTION ''customer id(%) is invalid.'', p_customer_id;
    END IF;
    IF w_errorkind = ''D'' THEN
        RAISE DEBUG ''customer id(%) is invalid.'', p_customer_id;
    END IF;
    RETURN ret_val;
END;
' LANGUAGE 'plpgsql';

SELECT 文の後で、FOUND 変数の値を参照して、エラー処理を行っています。

FOUND 変数は boolean 型の値を格納する変数で、以下のように設定されます。

• SELECT INTO 文で行が返された場合は真 (true)、返されない場合は偽 (false)
• PERFORM 文で行が生成された場合は真、生成されなかった場合は偽
• UPDATE、INSERT および DELETE 文で 1 行以上処理された場合は真、1 行も処理されなかった場合は偽
• FETCH 文で行が返却された場合は真、返却されなかった場合は偽
• FOR 文で 1 回以上繰り返しが行われた場合は真、行われなかった場合は偽

実際に存在しない、契約者番号を入力してみましょう。

図 7: 例外処理の実行

「0」という契約者番号は存在しないため、エラー処理が動作し、エラーメッセージが表示されます。

その他にも GET DIAGNOSTICS コマンドで、SQL 文の効果を判断することができます。 詳細はマニュアルに記述されておりますので、ご覧ください。

ここまでで、主な移行ポイントである、OUT パラメタの扱い、組み込み関数、例外処理について説明しました。 ほとんどのストアドプロシジャに関しては、以上の 3 点が移行のポイントとなりますが、その他にもいくつか移行のポイントとなるものがありますので、説明をします。

• カーソル変数
• トリガーの定義方法
• SELECT 文の記述形式

PowerGres Plus の場合、参照カーソルを利用してデータを返却することができます。 参照カーソルを利用することで、Oracle のカーソル変数と同じようにレコードセットの 1 行を返却することできます。

以下に、パラメタで指定された年数以上、居住している契約者情報を出力する場合を例に説明します。

登録するサンプルプロシジャ (create_refcursor_sample.sql)

DROP FUNCTION reffunc(integer);

CREATE FUNCTION reffunc(integer) RETURNS refcursor AS '
DECLARE
    p_years ALIAS FOR $1;
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT customer_id, moving_house FROM contractor WHERE (current_date - moving_house + 364) / 356 > p_years;
    RETURN ref;
END;
' LANGUAGE 'plpgsql';

この関数を呼び出して 2 年以上居住している契約者の情報を 1 行ずつ参照してみましょう。 今回は、psql コマンドを使用して確認してみましょう。

図 8: 参照カーソルの使用

2 年以上入居している契約者は、4 名いるので、5 回目は情報が表示されません。 この関数ではカーソル名を定義していないので、SELECT 文で自動生成カーソル名 <unnamed portal 1> が返却されます。 そのカーソル名を使用して FETCH 文で 1 行ずつ問い合せ結果を取り出しています。

PowerGres Plus の場合は、トリガー用の関数を作成し、その関数をトリガーとして定義します。 トリガー用の関数は復帰値を返却しないので、復帰値型が TRIGGER になっていることに注意してください。 Oracle では、CREATE TRIGGER 文に直接処理の内容を記述します。 トリガー定義では、以下の処理を行う場合を例に説明します。

契約者テーブル (contractor) に対して、

• データの挿入 (新規契約) があった場合、総契約者総数を表示する。
• データの更新 (契約更新) があった場合、その情報を表示する。
• データの削除 (契約解除) があった場合、対応する連帯保証人データを削除する。

Oracle の場合

CREATE TRIGGER triggertest1
BEFORE DELETE OR INSERT OR UPDATE ON testtable1
BEGIN
    ...
END;

PowerGres Plus の場合 (create_trigger_sample.sql)

CREATE FUNCTION triggertest1_func() RETURNS TRIGGER AS '
BEGIN
    ...
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER trigertest1 BERORE INSERT OR UPDATE OR DELETE
ON testtable1
FOR EACH ROW EXECUTE PROCEDURE triggertest1_func();

ストアドプロシジャ内で使用する SQL 文、特に SELECT 文の構文に細かい違いがあります。 そのため、その修正が必要となります。 以下に一覧を記述してありますのでご覧ください。

使用頻度の高いと思われる、列の別名、アウタージョイン、FROM 句の副問合せについて、記述例を説明します。

Oracle の場合

SELECT customer_id "契約者番号" FROM contractor;

PowerGres Plus の場合

SELECT customer_id AS "契約者番号" FROM contractor;

Oracle の場合

SELECT contractor.customer_id, contractor.name, consigner.name
    FROM contractor, consigner
    WHERE contractor.customer_id (+)= consigner.customer_id;

PowerGres Plus の場合

SELECT contractor.customer_id, contractor.name, consigner.name
    FROM contractor RIGHT OUTER JOIN consigner
    ON contractor.customer_id = consigner.customer_id;

Oracle の場合

SELECT name
    FROM (SELECT * from contractor WHERE moving_house  100000;

PowerGres Plus の場合

SELECT name
    FROM (SELECT * from contractor WHERE moving_house  100000;

今回ご紹介したとおり、Oracle から PowerGres Plus へのストアドプロシジャの移行には、対応する機能がありますので、ポイントを抑えておけば、簡単に移行できます。

また、組み込み関数を調べている過程で分かったのですが、組み込み関数は PowerGres Plus の方が数多く登録されています。 これは PowerGres Plus がサポートしている幾何データ型の操作のための関数があらかじめ登録されているためです。