本書では以下のDBMSを前提にした調査結果を記載します。「ストアドプロシージャ」についてPostgreSQLにおける特徴を紹介します。PostgreSQLにおけるストアドプロシージャ¶PostgreSQLではストアドプロシージャはユーザ定義関数(FUNCTION)として定義を行います。実行方法は、関数として実装するため呼び出し方法もSQL文の中で他の関数と同様に利用することになります。処理ロジックの記述には、PostgreSQL専用の手続き言語としてPL/pgSQLが用意されています。上記以外に、CやPerlなどでも処理ロジックを組み込むことも可能です。PL/pgSQLについて¶PL/pgSQLは、OracleのPL/SQLと同様にSQLに制御構造(条件分岐やLOOP処理)などを組み込んだ、PostgreSQLで標準として実装されている手続き言語です。記述された処理ロジックは、ユーザ定義関数としてデータベースに格納する事が出来ますが、事前にコンパイルはされずに、実行時に解釈され実行されます。plpgsql;PostgreSQLでは処理内容の記述部分(変数宣言とBEGINからENDまで)を文字列定数として作成する必要があります。そのためにドル引用符付け($$)を使って処理記述の範囲を囲います。単一引用符で範囲を囲む方法も可能ですが、この場合には関数の本体部分で使用される単一引用符(')とバックスラッシュ(\)は二重にする必要があります。処理内容の記述に使用している言語の指定が必須で、LANGUAGE句で指定します。変数宣言部にDECLAREが必須ですので追加する必要があります。引数を持たないFUNCTIONを作成するとき、にはOracleでは”()”を省略できますが、PostgreSQLでは”()”の記述が必須です。END;に書き換える必要があります。PL/SQLではEND部分にFUNCTION名を記載することがありますが、PL/pgSQLでは記載しません。PROCEDURE文¶PostgreSQLにはPROCEDUREは実装されていません。FUNCTIONで代用する事になります。PACKAGE文¶PROCEDUREと同様にPACKAGEは実装されていません。FUNCTIONで代用することになります。PACKAGEレベルで共通使用する定数などは、一時テーブルに保存するなどの方法を検討する必要があります。PROCEDUREがPACKAGEに属している構成を元々とっていた場合には、SCHEMAで代替することができます。((後略)SCHEMAを使用した場合は、FUNCTION名にどのSCHEMAに属しているかを指定する必要があります。ひとつのFUNCTION内で別のFUNCTIONを呼ぶ場合も同様にSCHEMAを指定する必要があります。PL/pgSQLでは仕様部と本体に分けず、一つの関数定義は一箇所に記述します。FUNCTION文¶OracleとPostgreSQLでは互換性がありません。Oracleでは再コンパイルに関する命令になります。PostgreSQLでは関数名の変更、所有者の変更などのFUNCTIONが保持している情報を変更する命令になります。);PostgreSQLでは、同名の関数が存在している場合、引き渡しパラメータも含めて指定する必要があります。パラメータの指定はデータ型のみの記載でも問題ありません。OracleからPostgreSQLへの移行(標準手続き言語関連)¶OracleとPostgreSQLにそれぞれ実装されている手続き言語である、PL/SQLとPL/pgSQLにおける記述の相違を中心に書換え方法を記述します。構造のステートメントには相違ありません。コメント¶コメントの記述には相違ありません。:行末までをコメントとします。ブロック(複数行でも可)をコメントとします。引数の宣言ではPL/SQLと同じようにPL/pgSQLでもIN引数、OUT引数、INOUT引数を使用することができます。OUT引数又はINOUT引数を使用した場合はRETURNSの指定が不要になることもPL/SQLと同じです。以下の事柄について注意が必要です。同名で同引数を持つ関数を複数作成できないことはOracleでもPostgreSQLでも同じですが、PostgreSQLはこの判断にOUT引数を考慮しません。したがって、引数の数や名前が違う場合でもそれらがOUT引数である場合、同名で同引数の関数と判断され作成時にエラーとなります。OUT引数関数の呼び出し方OUT引数(INOUT引数)を持つ関数を呼び出す場合、OUT引数は呼出引数に含めず返り値を引数に代入するように記述する必要があります。複数OUT引数がある場合は一旦RECORD型に代入する必要があります。使うというものがあります。データ型¶PostgreSQLで使用可能なデータ型はPL/pgSQLで使用できます。データ型の変換については別ドキュメント「組み込みデータ型対応表(Oracle-PostgreSQL)」を参照してください。同様に%ROWTYPE型や%TYPEはそのまま使用できます。RECORD型については注意が必要です。RECORD;PL/pgSQLではRECORD型の宣言時にはレコードの内容は記述しません。レコードの内容は直接SELECT文を記述したり、カーソルのFETCHで使用されると定義が確定されます。rec_nameデータ型のキャストには組み込み関数を使用することも可能ですが、PostgreSQLでは伝統的に「::」を使用してキャストを行います。Oracleで用意されている型キャストの関数の中にはPostgreSQLでは存在しないものもあります。関してもOracleとPostgreSQLでは違いがありますので注意が必要になります。NULLについては別ドキュメント「SQL移行調査編」を参照してください。テーブル型はOracleでは宣言する必要がありますが、PostgreSQLではテーブルを定義した時点でそのテーブル名と同名のものが利用できるようになるため、宣言する必要がありません。変数の宣言¶プログラム内で使用する変数は必ず宣言部に記述して宣言を行う必要があります。例外としてFORループで使用するループ変数はこの限りではありません。例外の名前の宣言はPL/pgSQLでは宣言する事が出来ません。RAISE文を使ってエラーを発生させます。LOOP命令¶LOOPの記述には相違ありません。LOOP」の間に記述された命令を繰り返し実行します。LOOPを抜けるためにはEXITを使用します。EXITに続けてLOOPを抜ける条件式を記述します。EXITのみでは無条件でLOOPから抜けます。WHILE命令¶WHILEの記述には相違ありません。LOOP」の間に繰り返す命令を記述します。条件式を満たす前にLOOPを抜けるためにはEXITを使用します。FOR命令¶FORの記述には相違ありません。LOOP」に記述された命令を繰り返し実行します。「REVERSE」を使って値を最大値から最小値までを行う場合には書換えが必要です。LOOP;最大値と最小値の値の指定が逆になります。EXIT命令¶EXITの記述には相違ありません。10;ラベルが指定されない場合には最も内側のLOOPを終わらせます。ラベルの指定がある場合には指定されたラベルのループを抜けます。WHENが指定された場合には、条件式を満たしていればEXITを実行します。CONTINUE命令¶CONTINUEの記述には相違ありません。条件式;ラベルが指定されない場合には実行しているLOOPの先頭に戻り次の反復に制御を移します。ラベルの指定がある場合には指定されたラベルの先頭に戻り次の反復に制御を移します。WHENが指定された場合には、条件式を満たしていればCONTINUEを実行します。IF命令¶IF文については、Oracleの記述と相違ありません。CASE命令¶CASEの記述には相違ありません。CASE;WHEN句内の値と比較を行い一致すれば指定された命令が実行されます。全てのWHENを順番に評価した後一致するものがない場合、ELSEの命令を実行します。GOTO命令¶PostgreSQLにはGOTO命令がありません。;[対応する命令なし]置換える命令がありません。無条件に指定したラベルに制御を移すことは出来ません。カーソルの宣言¶カーソルの宣言については注意が必要です。クエリー;どちらも宣言はDECLARE部で行いますが、文法が違います。FORの部分はISで記述されていても文法エラーにはなりません。引数を宣言する際にINというキーワードがPL/pgSQLでは不要になります。カーソルのOPEN¶カーソルのOPENの記述には相違ありません。カーソル名;宣言をしたカーソルから行を取り出すために、OPENによりカーソルを開きます。カーソルのFETCH¶カーソルのFETCHの記述には相違ありません。取得した値を格納する変数;カーソルから行を1行づつ取り出して変数に格納します。カーソルの終了判定¶カーソルをすべてFETCHしたときの判定方法は注意が必要です。FOUND;Oracleでは、カーソル名を明示して終了判定(NOTFOUND)しますが、PostgreSQLではカーソル名の指定はできません。カーソルの更新¶カーソルのカレント行に対する更新の記述には相違ありません。UPDATEを使って作成したカーソルの現在行に対して項目の値の変更およびレコードの削除を行います。カーソルのCLOSE¶カーソルのCLOSEの記述には相違ありません。カーソル名;OPENしたカーソルを閉じます。PL/pgSQLには%ISOPENが存在していません。PL/SQLではカーソルの閉じ忘れ防止としても使用していましたが、PL/pgSQLではそれができません。クローズを忘れないようにすれば問題ありませんが、%ISOPENの代用としては以下の方法があります。NULL;END;すでにクローズされたカーソルをクローズしようとするとエラーが発生しますが、それを例外として拾いそこでは何もしないという処理をしています。オープンの状態であればクローズし、クローズされていれば何もしません。関数の引数や返り値、変数としてカーソルを使用する場合は、refcursor型として宣言します。PL/SQLではSYS_REFCURSORと宣言されていたものです。EXCEPTION文¶EXCEPTIONの記述には相違ありません。エラー処理内容3END;WHENの後に記述された例外の内容と合致したときにTHENの後に記述された処理を行います。指定された例外以外が発生したときは、呼び出し元にエラー情報が伝搬します。例外に設定されている名前に相違があるものは個別に書換えが必要です。以下は例外の一部についての対比をまとめましたので、参考にしてください。書換え必要なお、PostgreSQLのエラーコードに対する例外名はマニュアルの付録に記載があるので参考にしてください。関して注意すべき点あります。OracleではSELECTの結果が0であった場合にこの例外に該当しますが、PostgreSQLでは明示的にハンドリングしなければSELECTの結果が0行であっても例外として判断されません。INTO文にSTRICTを加えるかもしくは代入先の変数がNULLであるかを確認して例外を投げる必要があります。RAISE文¶RAISEを使った例外を発生させる記述には相違ありません。exception;事前定義の例外を明示的に呼び出します。Oracleでは宣言部で例外の名前を宣言して、RAISEで例外を呼び出せますが、PostgreSQLでは宣言部での名前の宣言が出来ないので、RAISE文で例外の詳細を記述する事になります。代替として、任意のSQLSTATE(5文字の状態コード)を使用することができます。PL/pgSQL関連の例外はP0から始まるのが作法ですが、最低限マニュアルにも記載されている規定のSQLSTATEと重複しなければ問題ありません。ついては注意が必要です。ファンクション名();PostgreSQLでは、ストアドファンクション(関数)として登録していますのでSELECT文を使って呼び出します。Oracleでは引数がない場合には括弧は不要ですが、PostgreSQLでは括弧が必要です。関数の中で別の関数を実行する場合、基本的には呼び出し先の関数が返す値に合わせたデータ型の変数を宣言し、それに代入するような形で記載します。ファンクション名();返り値がない関数を実行する場合には、PL/SQLとは違いPL/pgSQLではPERFORM命令が必要になります。トランザクション制御¶PostgreSQLのストアドファンクションは、外部トランザクションの一部として実行されますので、処理中にCOMMITを実行できません。使って呼び出し元とトランザクションを分離する事が出来ますが、PostgreSQLにはこのような機能はありません。EXCEPTIONで例外の発生が判断された時は、BEGIN以降のすべてのデータベースに対する更新処理が自動的にロールバックします。シーケンス¶PostgreSQLとOracleでは、シーケンスから値を取り出す構文が異なります。以下がPostgreSQLでのシーケンス値の取り出し方です。現在値を再度取り出すその他シーケンス移植時の情報は別ドキュメント「DB移行開発見積り編」も参照ください。組み込み関数に関しても書き換えが必要になる部分があります。これに関しては別ドキュメント「組み込み関数移行調査編」を参照ください。DUAL¶PostgreSQLではOracleのようにDUALテーブルは用意されていません。対応方法に関しては別ドキュメント「SQL移行調査編」を参照ください。パッケージ変数代替¶PostgreSQLではパッケージという概念が無いためPL/SQLのパッケージ変数をそのまま移植することができません。PL/pgSQLでそれを実現させるためにはいくつか方法があるかもしれませんが、ここでは一時テーブルを使用したものを紹介します。PostgreSQLの一時テーブルは接続ごとに独立して作成され、接続が切断されるとテーブル定義はそのデータと共に消えます。これを実現させるためにはその一時テーブルの作成とテーブルへのデータ挿入、更新そしてデータの取得を行うための関数をパッケージごとに作成する必要があります。PostgreSQLではパッケージという概念がないためスキーマを代わりに使用している前提となります。IF;END;$$;すでに一時テーブルが存在していないことを確認し、一時テーブルを作成します。テーブル内の列は2つでkeyに変数名を格納し、valにその変数の値を格納します。変数の値は一旦文字列として保存し取り出す際にあるべきデータ型にキャストすることになります。IF;END;$$;初期化用の関数を実行することですでにテーブルができている状態を確立します。条件分岐ではもし一時テーブル内に登録しようとしている変数が存在していなければ新規登録を行い、すでに存在している場合は更新を行うようになっています。取得した値用変数;END;$$;上記3つの関数を使用することでDBへの接続ごとに値を保持することが可能になり関数間で使い回すことが可能になります。PL/SQLの場合関数内でROLLBACKが実行された場合でもパッケージ変数の値は保たれますが、上記の方法ではそれを実現できていないことに注意してください。本項目に関しては以下の資料を御参照下さい。本項目に関しては以下の資料を御参照下さい。異種DBMSからPostgreSQLへの移行に関するまとめ¶SQLレベルであったり手続き言語の構文については、ある程度単純な置換え作業は可能と思われます。業務処理を移行するためには以下の様な問題があります。PostgreSQLではファンクション(関数)としてのみしか実装できないので呼び出し手順が変わる異種DBMSの個別機能(例えばOracleのパッケージなど)の対応が複雑もしくは代替手段がない複雑なバッチ処理に必要なトランザクション制御が実装できないこのような状況を考えると、単純に移行が出来る異種DBMSのストアドプロシージャは限られてくるものと思われます。一つPL/pgSQLの特徴として、実行時にソースの解析が行われます。異種DBMSに実装されている事前コンパイル機能などにより、実行レスポンスを向上させる目的で使用しているのであれば、この部分においては移行前と同等の性能は期待できない可能性があります。これらを総合すると処理の内容によっては、異種DBMSのストアドプロシージャは、PL/pgSQLに移行するよりも他の言語で実装する方が容易になる可能性があります。Oracleのユーティリティーパッケージについて¶Oracleのストアドプロシージャでは、ユーティリティパッケージ(DBMS_OUTPUTやUTL_FILE)が、よく使用されていますが、これらはOracleが提供しているのでPostgreSQLには実装されていません。NOTICEで代用できるものもありますが、構文が違うので個別での対応が必要と思われます。参考ですがOrafceではユーティリティーパッケージの一部の実装を実現しています。仕様的にOracleとの違いがありますので注意が必要です。Consortium。