PowerGres

技術情報 | PowerGres体験記 | 第 5 回 ストアドプロシジャの移行 – Oracle マイグレーション

このページに記載の情報は、2005 年にリリースした PowerGres Plus V2 を対象としています。PowerGres Plus V2 の販売はすでに終了しています。

PowerGres Plus の最新バージョンに関する情報は、製品紹介ページまたは PowerGres 体験記ページを参照してください。

SRA OSS では、Oracle データベースなど他のデータベースから、PostgreSQL や PowerGres へのマイグレーションサービスを提供しています。

詳細は「データベースマイグレーションサービス」のページをご覧ください。

第 5 回 ストアドプロシジャの移行 『基本をおさえて簡単移行』

はじめに

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

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

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

データベース移行の概要

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

ストアドプロシジャの概要

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

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

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

図 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 文の概要
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 パラメタが 1 個の場合

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 パラメタを返却する関数を定義します。

OUT パラメタが 2 個以上の場合

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

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

Oracle の場合
CREATE PROCEDURE nonpayment_rent_proc(IN 契約者番号,
                                      IN 延滞日数,
                                      OUT 請求額,
                                      OUT 契約者電話番号) IS
BEGIN
    ...
END;
PowerGres Plus の場合 (create_nonpayment_rent_func.sql)
      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: ストアドプロシジャの登録

Java プログラムの作成

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

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

Java プログラムのコンパイル

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

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

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

Java プログラムの実行

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

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

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

組み込み関数

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

Oracle PowerGres Plus 対処
BITAND ビット演算子「&」で代替
CONCAT 組み込み関数を作成
INSTR 組み込み関数を作成
ADD_MONTHS 組み込み関数を作成
LAST_DAY 組み込み関数を作成
MONTHS_BETWEEN 組み込み関数を作成
NEXT_DAY 組込み関数を作成
SYSDATE current_date 関数で代替
SYSTIMESTAMP current_timestamp 関数で代替
BIN_TO_NUM キャストで代替
DECODE CASE 文で代替
GREATEST 組み込み関数を作成
LEAST 組み込み関数を作成

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

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

CONCAT 関数実行例

図 6: CONCAT 関数実行例

例外処理

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

移行のポイント
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 型の値を格納する変数で、以下のように設定されます。

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

例外処理の実行

図 7: 例外処理の実行

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

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

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

その他のポイント

カーソル変数

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();

SELECT 文の記述形式

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

項目 Oracle PowerGres Plus
列の別名 「AS」省略可 「AS」が必須
結果の部分取り出し ROWNUM OFFSET
LINE BETWEEN OFFSET 〜 LIMIT
アウタージョイン 拡張構文「(+)」 標準の記述形式
FROM 句副問合せ エイリアス必要無 エイリアス必須
階層問合せを FROM 句で使用する記述形式 START WITH connectby() 関数を FROM 句で記述
集合演算 MINUS MINUS 句と等価な EXCEPT 句に変更
更新のための行ロック FOR UPDATE 句、かつ NOWAIT モードを使用 Oracle と同等の NOWAIT モード機能はないので、FOR UPDATE 句を使用する場合は、デッドロックにならないよう注意する

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

列の別名 (customer_id カラムに別名として、「契約者番号」を付ける場合)
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;
FROM 句の副問合せ (例では、FROM 句の副問合せに「T」というエイリアスを付加)
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 がサポートしている幾何データ型の操作のための関数があらかじめ登録されているためです。

製品・サービスに関するお問い合わせ
03-5979-2701

お問い合せ受付時間 月 - 金 10:00 - 17:00

メールフォームでのお問い合わせ

ページトップへ