WG2活動報告書ストアドプロシージャ移行調査編

Size: px

Start display at page:

Download "WG2活動報告書ストアドプロシージャ移行調査編"

あきみすえがら
5 years ago
Views:

1 エンタープライズコンソーシアム技術部会 WG#2 WG2 活動報告書ストアドプロシージャ移行調査編

2 目次目次 1. 改訂履歴 2. ライセンス 3. はじめに 3.1. 本資料の目的 3.2. 本資料で記載する範囲 3.3. 本資料で扱う用語の定義 3.4. 本資料で扱う DBMS およびツール 4. のストアドプロシージャについて 4.1. におけるストアドプロシージャ 4.2. PL/pgSQL について 5. からへの移行 ( 定義関連 ) 5.1. CREATE FUNCTION 文 5.2. CREATE PROCEDURE 文 5.3. CREATE PACKAGE 文 5.4. ALTER FUNCTION 文 5.5. DROP FUNCTION 文 6. からへの移行 ( 標準手続き言語関連 ) 6.1. 構造 6.2. コメント 6.3. 引数 6.4. データ型 6.5. 変数の宣言 6.6. 制御構造 LOOP 命令 WHILE 命令 FOR 命令 EXIT 命令 CONTINUE 命令 IF 命令 CASE 命令 GOTO 命令 6.7. カーソルカーソルの宣言カーソルの OPEN カーソルの FETCH カーソルの終了判定カーソルの更新カーソルの CLOSE REFCURSOR 6.8. エラーハンドリング EXCEPTION 文 RAISE 文 7. からへの移行 ( その他 ) 7.1. 起動方法 7.2. 呼出方法 7.3. トランザクション制御 7.4. シーケンス 7.5. 組み込み関数 7.6. DUAL 7.7. パッケージ変数代替 8. SQL Server からへの移行 9. DB2 からへの移行 10. 異種 DBMS からへの移行に関するまとめのユーティリティーパッケージについて 11. 著者 Page 2 of 23

3 Page 3 of 23 Enterprise Consortium

4 1. 改訂履歴版改訂日 /03/25 新規作成 /03/ 年度活動成果の追加 /03/16 変更内容 PostgreSQ L の対象バージョンを 10.3 に更新 5.5. DRO P FUNCTIO N 文の記述を変更 6.3. 引数を追加 IF 命令の記述を変更 REFCU RSO R を追加 EXCEPTIO N 文に NO _DATA_FO U ND に関する注意点を追加 7.2. 呼出方法を追加 7.4. シーケンスを追加 7.5. 組み込み関数を追加 7.6. DU AL を追加 7.7. パッケージ変数代替を追加 Page 4 of 23

5 2. ライセンス本作品は CC-B Y ライセンスによって許諾されていますライセンスの内容を知りたい方はこちらでご確認ください文書の内容表記に関する誤りご要望感想等につきましては PG ECon s のサイトを通じてお寄せいただきますようお願いいたします Eclipse は Eclipse Fou n dation In c の米国およびその他の国における商標もしくは登録商標です IB M および DB 2 は世界の多くの国で登録された In tern ation al B u sin ess Mach in es Corporation の商標です In tel インテルおよび Xeon は米国およびその他の国における In tel Corporation の商標です Java は O racle Corporation 及びその子会社関連会社の米国及びその他の国における登録商標です文中の社名商品名等は各社の商標または登録商標である場合があります Lin u x は Lin u s Torvalds 氏の日本およびその他の国における登録商標または商標です Red Hat および Sh adow man logo は米国およびその他の国における Red Hat,In c. の商標または登録商標です Microsoft W in dow s Server SQ L Server 米国 Microsoft Corporation の米国及びその他の国における登録商標または商標です MySQ L は O racle Corporation 及びその子会社関連会社の米国及びその他の国における登録商標です文中の社名商品名等は各社の商標または登録商標である場合があります O racle は O racle Corporation 及びその子会社関連会社の米国及びその他の国における登録商標です文中の社名商品名等は各社の商標または登録商標である場合があります PostgreSQ L は PostgreSQ L Commu n ity Association of Can ada のカナダにおける登録商標およびその他の国における商標です W in dow s は米国 Microsoft Corporation の米国およびその他の国における登録商標です TPC, TPC B en ch mark, TPC-B, TPC-C, TPC-E, tpmc, TPC-H, TPC-DS, Q ph H は米国 Tran saction Processin g Performan ce Cou n cil の商標ですその他本資料に記載されている社名及び商品名はそれぞれ各社が商標または登録商標として使用している場合があります Page 5 of 23

6 3. はじめに 3.1. 本資料の目的本資料は異種 DB MS から PostgreSQ L へストアドプロシージャを移行する作業の難易度およびボリュームの事前判断と実際に書き換えを行う際の参考資料として利用されることを想定しています 3.2. 本資料で記載する範囲本資料では移行元の異種 DB MS として O racle Database IB M DB 2 および Microsoft SQ LServer を想定し PostgreSQ L へストアドプロシージャを移行する際に書き換えが必要である箇所とその書き換え方針について手続き言語を中心に記載しますスキーマ SQ L 組み込み関数については本資料では取り扱っていませんこれらに関してはそれぞれスキーマ移行調査編 SQ L 移行調査編組み込み関数移行調査編を参照してください 3.3. 本資料で扱う用語の定義資料で記述する用語について以下に定義します N o. 用語表 3.1 用語定義意味 1 DB MS データベース管理システムを指しますここでは PostgreSQ L および異種 DB MS の総称として利用します 2 異種 DB MS PostgreSQ L ではないデータベース管理システムを指します本資料では O racle Database IB M DB 2 および Microsoft SQ LServer が該当します 3 O racle データベース管理システムの O racle Database を指します 4 DB 2 データベース管理システムの IB M DB 2 を指します 5 SQ LServer データベース管理システムの Microsoft SQ LServer を指します 3.4. 本資料で扱う DBMS およびツール本書では以下の DB MS を前提にした調査結果を記載します表 3.2 本書で扱う DB MS DBM S 名称 PostgreSQ L 10.3 バージョン O racle Database 11gR IB M DB Microsoft SQ LServer 2005 Express Page 6 of 23

7 4. のストアドプロシージャについてデータベースに対する一連の処理手順をまとめて DB MS 内に格納するストアドプロシージャについて PostgreSQ L における特徴を紹介します 4.1. におけるストアドプロシージャ PostgreSQ L ではストアドプロシージャはユーザ定義関数 (FUNCTIO N) として定義を行います実行方法は関数として実装するため呼び出し方法も SQ L 文の中で他の関数と同様に利用することになります処理ロジックの記述には PostgreSQ L 専用の手続き言語として PL/pgSQ L が用意されています上記以外に C や Perl などでも処理ロジックを組み込むことも可能です 4.2. PL/pgSQL について PL/pgSQ L は O racle の PL/SQ L と同様に SQ L に制御構造 ( 条件分岐や LO O P 処理 ) などを組み込んだ PostgreSQ L で標準として実装されている手続き言語です記述された処理ロジックはユーザ定義関数としてデータベースに格納する事が出来ますが事前にコンパイルはされずに実行時に解釈され実行されます Page 7 of 23

8 5. からへの移行 ( 定義関連 ) 5.1. CREATE FUNCTION 文表 5.1 CREATE FUNCTIO N 文の比較 CREATE OR REPLACE FUNCTION ファンクション名 (@ 引数名 IN データ型 ) RETURN 戻り値データ型 IS 変数名データ型 ; BEGIN 処理内容 ; END [ ファンクション名 ]; CREATE OR REPLACE FUNCTION proc_f ( 引数名 IN データ型 ) RETURNS 戻り値データ型 AS $$ DECLARE 変数名データ型 ; BEGIN 処理内容 ; END; $$ LANGUAGE plpgsql; PostgreSQ L では処理内容の記述部分 ( 変数宣言と B EG IN から END まで ) を文字列定数として作成する必要がありますそのためにドル引用符付け ($$) を使って処理記述の範囲を囲います単一引用符で範囲を囲む方法も可能ですがこの場合には関数の本体部分で使用される単一引用符 (') とバックスラッシュ (\) は二重にする必要があります処理内容の記述に使用している言語の指定が必須で LANG U AG E 句で指定します変数宣言部に DECLARE が必須ですので追加する必要があります引数を持たない FUNCTIO N を作成するときには O racle では () を省略できますが PostgreSQ L では () の記述が必須です上記以外では RETU RN RETU RNS IS AS END [FUNCTIO N 名 ]; END; に書き換える必要があります PL/SQ L では END 部分に FUNCTIO N 名を記載することがありますが PL/pgSQ L では記載しません 5.2. CREATE PROCEDURE 文 PostgreSQ L には PRO CEDU RE は実装されていません FUNCTIO N で代用する事になります 5.3. CREATE PACKAGE 文 PRO CEDU RE と同様に PACKAG E は実装されていません FUNCTIO N で代用することになります PACKAG E レベルで共通使用する定数などは一時テーブルに保存するなどの方法を検討する必要があります PRO CEDU RE が PACKAG E に属している構成を元々とっていた場合には SCHEMA で代替することができます Page 8 of 23

9 表 5.2 PACKAG E と SCHEMA の比較 CREATE OR REPLACE PACKAGE パッケージ名 IS PROCEDURE プロシージャ名 ( ( 後略 ) CREATE SCHEMA IF NOT EXISTS スキーマ名 ; CREATE OR REPLACE FUNCTION スキーマ名. ファンクション名 ( ( 後略 ) SCHEMA を使用した場合は FUNCTIO N 名にどの SCHEMA に属しているかを指定する必要がありますひとつの FUNCTIO N 内で別の FUNCTIO N を呼ぶ場合も同様に SCHEMA を指定する必要がありますまた PL/pgSQ L では仕様部と本体に分けず一つの関数定義は一箇所に記述します 5.4. ALTER FUNCTION 文 O racle と PostgreSQ L では互換性がありません O racle では再コンパイルに関する命令になります PostgreSQ L では関数名の変更所有者の変更などの FUNCTIO N が保持している情報を変更する命令になります 5.5. DROP FUNCTION 文表 5.3 DRO P FUNCTIO N 文の比較 DROP FUNCTION ファンクション名 ; DROP FUNCTION ファンクション名 ( 引数名 IN データ型 ); PostgreSQ L では同名の関数が存在している場合引き渡しパラメータも含めて指定する必要がありますパラメータの指定はデータ型のみの記載でも問題ありません Page 9 of 23

10 6. からへの移行 ( 標準手続き言語関連 ) O racle と PostgreSQ L にそれぞれ実装されている手続き言語である PL/SQ L と PL/pgSQ L における記述の相違を中心に書換え方法を記述します 6.1. 構造構造のステートメントには相違ありません DECLARE 変数名データ型 ; BEGIN 処理内容 END; DECLARE 部で変数の宣言 B EG IN 部で処理内容の記述 END でブロックの終了 6.2. コメントコメントの記述には相違ありません 6.3. 引数 -- コメント記述 : 行末までをコメントとします /* コメント記述 */ :/* から */ までのブロック ( 複数行でも可 ) をコメントとします引数の宣言では PL/SQ L と同じように PL/pgSQ L でも IN 引数 O U T 引数 INO U T 引数を使用することができますまた O U T 引数又は INO U T 引数を使用した場合は RETU RNS の指定が不要になることも PL/SQ L と同じですしかし以下の事柄について注意が必要です同名になってしまう O U T 引数付き関数同名で同引数を持つ関数を複数作成できないことは O racle でも PostgreSQ L でも同じですが PostgreSQ L はこの判断に O U T 引数を考慮しませんしたがって引数の数や名前が違う場合でもそれらが O U T 引数である場合同名で同引数の関数と判断され作成時にエラーとなります O U T 引数関数の呼び出し方 O U T 引数 (INO U T 引数 ) を持つ関数を呼び出す場合 O U T 引数は呼出引数に含めず返り値を引数に代入するように記述する必要がありますまた複数 O U T 引数がある場合は一旦 RECO RD 型に代入する必要があります < 複数の OUT 引数を持つ関数 > CREATE OR REPLACE ファンクション名 ( 引数 1 IN データ型, 引数 2 OUT データ型, 引数 3 OUT データ型 ) LANGUAGE plpgsql... < 呼び出し元の関数宣言部に以下を追加 > 変数 1 RECORD; < 呼び出し部分 > 変数 1 := ファンクション名 ( 引数 1); また上記以外にも細かな違いとして引数に対してデフォルトの値を与える際にも := ではなく = を使うというものがあります Page 10 of 23

11 PROCEDURE プロシージャ名 ( 変数名データ型 := デフォルト値... CREATE OR REPLACE FUNCTION ファンクション名 ( 引数 1 IN データ型 = デフォルト値データ型 PostgreSQ L で使用可能なデータ型は PL/pgSQ L で使用できますデータ型の変換については別ドキュメント組み込みデータ型対応表 (O racle-postgresq L) を参照してください同様に %RO W TYPE 型や %TYPE はそのまま使用できます RECO RD 型については注意が必要です type 変数名 is RECORD ( 変数名データ型 ); 変数名 RECORD; PL/pgSQ L では RECO RD 型の宣言時にはレコードの内容は記述しませんレコードの内容は直接 SELECT 文を記述したりカーソルの FETCH で使用されると定義が確定されます例 1. SELECT の結果をレコード型にストアする rec_n ame IN SELECT C1, C2 FRO M tb1 例 2. カーソル cu の結果をレコード型にストアする fetch cu in to rec_n ame データ型のキャストには組み込み関数を使用することも可能ですが PostgreSQ L では伝統的に :: を使用してキャストを行います O racle で用意されている型キャストの関数の中には PostgreSQ L では存在しないものもあります表 6.1 型キャストの比較変数 := TO_NUMBER( 値 ); 変数 := TO_CHAR( 値 ); 変数 := 値 ::numeric; 変数 := 値 ::text; NU LL に関しても O racle と PostgreSQ L では違いがありますので注意が必要になります NU LL については別ドキュメント SQ L 移行調査編を参照してくださいテーブル型は O racle では宣言する必要がありますが PostgreSQ L ではテーブルを定義した時点でそのテーブル名と同名のものが利用できるようになるため宣言する必要がありません 6.5. 変数の宣言プログラム内で使用する変数は必ず宣言部に記述して宣言を行う必要があります但し例外として FO R ループで使用するループ変数はこの限りではありません例外の名前の宣言は PL/pgSQ L では宣言する事が出来ません RAISE 文を使ってエラーを発生させます 6.6. 制御構造 LOOP 命令 LO O P の記述には相違ありません Page 11 of 23

12 LOOP 繰り返し処理 ; EXIT WHEN 条件式 ; END LOOP; LO O P と END LO O P の間に記述された命令を繰り返し実行します LO O P を抜けるためには EXIT を使用します EXIT に続けて LO O P を抜ける条件式を記述します EXIT のみでは無条件で LO O P から抜けます WHILE 命令 W HILE の記述には相違ありません WHILE 条件式 LOOP 繰り返し処理 ; END LOOP; W HILE と LO O P の間に繰り返しの条件式を記述し END LO O P の間に繰り返す命令を記述します条件式を満たす前に LO O P を抜けるためには EXIT を使用します FOR 命令 FO R の記述には相違ありません FOR 変数名 IN LOOP 繰り返し処理 ; END LOOP; IN の後に記述した最小値から最大値までの間 LO O P から END LO O P に記述された命令を繰り返し実行します但し REVERSE を使って値を最大値から最小値までを行う場合には書換えが必要です FOR 変数名 IN REVERSE LOOP 繰り返し処理 ; END LOOP; FOR 変数名 IN REVERSE LOOP 繰り返し処理 ; END LOOP; 最大値と最小値の値の指定が逆になります EXIT 命令 EXIT の記述には相違ありません EXIT; EXIT [ ラベル名 ] ; EXIT WHEN A1 > 10; ラベルが指定されない場合には最も内側の LO O P を終わらせますラベルの指定がある場合には指定されたラベルのループを抜けます W HEN が指定された場合には条件式を満たしていれば EXIT を実行します Page 12 of 23

13 CONTINUE 命令 CO NTINU E の記述には相違ありません CONTINUE; CONTINUE [ ラベル名 ] ; CONTINUE WHEN 条件式 ; ラベルが指定されない場合には実行している LO O P の先頭に戻り次の反復に制御を移しますラベルの指定がある場合には指定されたラベルの先頭に戻り次の反復に制御を移します W HEN が指定された場合には条件式を満たしていれば CO NTINU E を実行します IF 命令 IF 文については O racle の記述と相違ありません CASE 命令 CASE の記述には相違ありません CASE 変数 WHEN 条件値 THEN 分岐処理 ELSE 分岐処理 END CASE; W HEN 句内の値と比較を行い一致すれば指定された命令が実行されます全ての W HEN を順番に評価した後一致するものがない場合 ELSE の命令を実行します一致する W HEN がなく ELSE の記述が無い場合には CASE_NO T_FO U ND 例外が発生します GOTO 命令 PostgreSQ L には G O TO 命令がありません GOTO ラベル ; [ 対応する命令なし ] 置換える命令がありません無条件に指定したラベルに制御を移すことは出来ません 6.7. カーソルカーソルの宣言カーソルの宣言については注意が必要です CURSOR カーソル名 IS クエリー ; カーソル名 CURSOR FOR クエリー ; どちらも宣言は DECLARE 部で行いますが文法が違います FO R の部分は IS で記述されていても文法エラーにはなりません Page 13 of 23

14 また引数を宣言する際に IN というキーワードが PL/pgSQ L では不要になります CURSOR カーソル名 ( 引数 IN データ型 ) IS... カーソル名 CURSOR ( 引数データ型 ) FOR カーソルの OPEN カーソルの O PEN の記述には相違ありません OPEN カーソル名 ; 宣言をしたカーソルから行を取り出すために O PEN によりカーソルを開きますカーソルの FETCH カーソルの FETCH の記述には相違ありません FETCH カーソル名 INTO 取得した値を格納する変数 ; カーソルから行を 1 行づつ取り出して変数に格納しますカーソルの終了判定カーソルをすべて FETCH したときの判定方法は注意が必要ですカーソル名 %NOTFOUND; NOT FOUND; O racle ではカーソル名を明示して終了判定 (NO TFO U ND) しますが PostgreSQ L ではカーソル名の指定はできませんカーソルの更新カーソルのカレント行に対する更新の記述には相違ありません < 更新 > UPDATE テーブル名 SET 更新内容 WHERE CURRENT OF カーソル名 ; < 削除 > DELETE FROM テーブル名 WHERE CURRENT OF カーソル名 ; カーソルの宣言時に FO R U PDATE を使って作成したカーソルの現在行に対して項目の値の変更およびレコードの削除を行いますカーソルの CLOSE カーソルの CLO SE の記述には相違ありません CLOSE カーソル名 ; O PEN したカーソルを閉じます PL/pgSQ L には %ISO PEN が存在していません PL/SQ L ではカーソルの閉じ忘れ防止としても使用していましたが PL/pgSQ L ではそれができませんクローズを忘れないようにすれば問題ありませんが %ISO PEN の代用としては以下の方法があります Page 14 of 23

15 BEGIN CLOSE カーソル名 ; EXCEPTION WHEN invalid_cursor_name THEN NULL; END; すでにクローズされたカーソルをクローズしようとするとエラーが発生しますがそれを例外として拾いそこでは何もしないという処理をしていますオープンの状態であればクローズしクローズされていれば何もしません REFCURSOR 関数の引数や返り値変数としてカーソルを使用する場合は refcu rsor 型として宣言します PL/SQ L では SYS_REFCU RSO R と宣言されていたものです CREATE OR REPLACE FUNCTION ファンクション名 () RETURNS refcorsor LANGUAGE plpgsql AS $$ DECLARE カーソル名 refcursor; BEGIN ( 中略 ) RETURN カーソル名 ; END; 6.8. エラーハンドリング EXCEPTION 文 EXCEPTIO N の記述には相違ありません EXCEPTION WHEN エラーコード ( もしくは例外名 ) 1 THEN エラー処理内容 1 WHEN エラーコード ( もしくは例外名 ) 2 THEN エラー処理内容 2 WHEN OTHERS THEN エラー処理内容 3 END; W HEN の後に記述された例外の内容と合致したときに THEN の後に記述された処理を行います指定された例外以外が発生したときは呼び出し元にエラー情報が伝搬します例外に設定されている名前に相違があるものは個別に書換えが必要です以下は例外の一部についての対比をまとめましたので参考にしてくださいの例外名の例外名 CASE_NO T_FO U ND CASE_NO T_FO U ND 同じ INVALID_CU RSO R INVALID_CU RSO R_STATE 書換え必要 NO _DATA_FO U ND NO _DATA_FO U ND 同じ *1 STO RAG E_ERRO R O U T_O F_MEMO RY 書換え必要 TO O _MANY_RO W S TO O _MANY_RO W S 同じ ZERO _DIVIDE DIVISIO N_B Y_ZERO 書換え必要相違なお PostgreSQ L のエラーコードに対する例外名はマニュアルの付録に記載があるので参考にしてください h ttps://w w w.postgresql.jp/docu men t/10/h tml/errcodes-appen dix.h tml# errcodes-table *1 NO _DATA_FO U ND に関して注意すべき点あります O racle では SELECT の結果が 0 であった場合にこの例外に該当しますが PostgreSQ L では明示的にハンドリングしなければ SELECT の結果が 0 行であっても例外として判 Page 15 of 23

16 断されません SELECT INTO 文に STRICT を加えるかもしくは代入先の変数が NU LL であるかを確認して例外を投げる必要があります RAISE 文 RAISE を使った例外を発生させる記述には相違ありません RAISE exception; 事前定義の例外を明示的に呼び出します但し O racle では宣言部で例外の名前を宣言して RAISE で例外を呼び出せますが PostgreSQ L では宣言部での名前の宣言が出来ないので RAISE 文で例外の詳細を記述する事になります代替として任意の SQ LSTATE(5 文字の状態コード ) を使用することができます PL/pgSQ L 関連の例外は P0 から始まるのが作法ですが最低限マニュアルにも記載されている規定の SQ LSTATE と重複しなければ問題ありません Page 16 of 23

17 7. からへの移行 ( その他 ) 7.1. 起動方法実行方法については注意が必要です BEGIN EXECUTE プロシージャ名 END; SELECT ファンクション名 (); PostgreSQ L ではストアドファンクション ( 関数 ) として登録していますので SELECT 文を使って呼び出します O racle では引数がない場合には括弧は不要ですが PostgreSQ L では括弧が必要です 7.2. 呼出方法関数の中で別の関数を実行する場合基本的には呼び出し先の関数が返す値に合わせたデータ型の変数を宣言しそれに代入するような形で記載します (TEXT 型を返す関数を呼ぶ場合 ) DECLARE 変数 TEXT; BEGIN 変数 := ファンクション名 (); 返り値がない関数を実行する場合には PL/SQ L とは違い PL/pgSQ L では PERFO RM 命令が必要になります PERFORM ファンクション名 (); 7.3. トランザクション制御 PostgreSQ L のストアドファンクションは外部トランザクションの一部として実行されますので処理中に CO MMIT を実行できません O racle では PRAG MA AU TO NO MO U S_TRANSACTIO N を使って呼び出し元とトランザクションを分離する事が出来ますが PostgreSQ L にはこのような機能はありません EXCEPTIO N で例外の発生が判断された時は B EG IN 以降のすべてのデータベースに対する更新処理が自動的にロールバックします 7.4. シーケンス PostgreSQ L と O racle ではシーケンスから値を取り出す構文が異なります以下が PostgreSQ L でのシーケンス値の取り出し方です nextval(' シーケンス名 ') -- 次の値を取り出す setval(' シーケンス名 ', 値 ) -- 値をセットする currval(' シーケンス名 ') -- 現在値を再度取り出すその他シーケンス移植時の情報は別ドキュメント DB 移行開発見積り編も参照ください 7.5. 組み込み関数 Page 17 of 23

18 組み込み関数に関しても書き換えが必要になる部分がありますこれに関しては別ドキュメント組み込み関数移行調査編を参照ください 7.6. DUAL PostgreSQ L では O racle のように DU AL テーブルは用意されていません対応方法に関しては別ドキュメント SQ L 移行調査編を参照ください 7.7. パッケージ変数代替 PostgreSQ L ではパッケージという概念が無いため PL/SQ L のパッケージ変数をそのまま移植することができません PL/pgSQ L でそれを実現させるためにはいくつか方法があるかもしれませんがここでは一時テーブルを使用したものを紹介します PostgreSQ L の一時テーブルは接続ごとに独立して作成され接続が切断されるとテーブル定義はそのデータと共に消えますこれを実現させるためにはその一時テーブルの作成とテーブルへのデータ挿入更新そしてデータの取得を行うための関数をパッケージごとに作成する必要があります PostgreSQ L ではパッケージという概念がないためスキーマを代わりに使用している前提となります ( 初期化用関数例 ) CREATE OR REPLACE FUNCTION スキーマ名. 初期化用ファンクション名 () RETURNS void LANGUAGE plpgsql AS $$ DECLARE 変数 INTEGER := 0; BEGIN SELECT INTO 変数 count(*) FROM information_schema.tables WHERE table_name = lower(' 一時テーブル名 '); IF 変数 = 0 THEN CREATE TEMP TABLE 一時テーブル名 (key TEXT, val TEXT); INSERT INTO 一時テーブル名 VALUES (' パッケージ変数名 1',' 値 1'), (' パッケージ変数名 2',' 値 2'),...; END IF; END; $$; すでに一時テーブルが存在していないことを確認し一時テーブルを作成しますテーブル内の列は 2 つで key に変数名を格納し val にその変数の値を格納します変数の値は一旦文字列として保存し取り出す際にあるべきデータ型にキャストすることになります ( 登録用関数 ) CREATE OR REPLACE FUNCTION スキーマ名. 登録用ファンクション名 ( 登録する変数 TEXT, 登録する値 TEXT) RETURNS void LANGUAGE plpgsql AS $$ DECLARE 更新する一時テーブル名 TEXT := ' 一時テーブル名 '; 変数 INTEGER := 0; BEGIN PERFORM スキーマ名. 初期化用ファンクション名 (); EXECUTE 'SELECT count(*) FROM ' quote_ident( 更新する一時テーブル名 ) ' WHERE key = $1' INTO 変数 USING 登録する変数 ; IF 変数 = 0 THEN EXECUTE 'INSERT INTO ' quote_ident( 更新する一時テーブル名 ) ' VALUES ($1, $2)' USING 登録する変数, 登録する値 ; ELSE EXECUTE 'UPDATE ' quote_ident( 更新する一時テーブル名 ) ' SET val = $1 WHERE key = $2' USING 登録する値, 登録する変数 ; END IF; END; $$; Page 18 of 23

19 初期化用の関数を実行することですでにテーブルができている状態を確立します条件分岐ではもし一時テーブル内に登録しようとしている変数が存在していなければ新規登録を行いすでに存在している場合は更新を行うようになっています ( 取得用関数 ) CREATE OR REPLACE FUNCTION スキーマ名. 取得用ファンクション名 ( 値を取得したい変数 TEXT) RETURNS TEXT LANGUAGE plpgsql AS $$ DECLARE 取得する一時テーブル名 TEXT := ' 一時テーブル名 '; 取得した値用変数 TEXT := NULL; BEGIN PERORM スキーマ名. 初期化用ファンクション名 (); EXECUTE 'SELECT val FROM ' quote_ident( 取得する一時テーブル名 ) ' WHERE key = $1' INTO 取得した値用変数 USING 値を取得したい変数 ; RETURN 取得した値用変数 ; END; $$; 上記 3 つの関数を使用することで DB への接続ごとに値を保持することが可能になり関数間で使い回すことが可能になりますただし PL/SQ L の場合関数内で RO LLB ACK が実行された場合でもパッケージ変数の値は保たれますが上記の方法ではそれを実現できていないことに注意してください Page 19 of 23

20 8. SQL Serverからへの移行本項目に関しては以下の資料を御参照下さい h ttps://w w w.pgecon s.org/w p-con ten t/u ploads/pg ECon s/2013/w G 2/06_StoredProcedu remigration Research.pdf# page= 16 Page 20 of 23

21 9. DB2からへの移行本項目に関しては以下の資料を御参照下さい h ttps://w w w.pgecon s.org/w p-con ten t/u ploads/pg ECon s/2013/w G 2/06_StoredProcedu remigration Research.pdf# page= 25 Page 21 of 23

22 10. 異種 DBMSからへの移行に関するまとめ SQ L レベルであったり手続き言語の構文についてはある程度単純な置換え作業は可能と思われますしかし業務処理を移行するためには以下の様な問題があります PostgreSQ L ではファンクション ( 関数 ) としてのみしか実装できないので呼び出し手順が変わる異種 DB MS の個別機能 ( 例えば O racle のパッケージなど ) の対応が複雑もしくは代替手段がない複雑なバッチ処理に必要なトランザクション制御が実装できないこのような状況を考えると単純に移行が出来る異種 DB MS のストアドプロシージャは限られてくるものと思われますもう一つ PL/pgSQ L の特徴として実行時にソースの解析が行われます異種 DB MS に実装されている事前コンパイル機能などにより実行レスポンスを向上させる目的で使用しているのであればこの部分においては移行前と同等の性能は期待できない可能性がありますこれらを総合すると処理の内容によっては異種 DB MS のストアドプロシージャは PL/pgSQ L に移行するよりも他の言語で実装する方が容易になる可能性がありますのユーティリティーパッケージについて O racle のストアドプロシージャではユーティリティパッケージ (DB MS_O U TPU T や U TL_FILE) がよく使用されていますがこれらは O racle が提供しているので PostgreSQ L には実装されていません DB MS_O U TPU T は同様の機能として RAISE NO TICE で代用できるものもありますが構文が違うので個別での対応が必要と思われます参考ですが O rafce ではユーティリティーパッケージの一部の実装を実現しています但し仕様的に O racle との違いがありますので注意が必要です例 )DB MS_O U TPU T の通知のタイミング O racle トランザクションの終了時 O rafce 送信都度 Page 22 of 23

23 11. 著者版ストアドプロシージャ移行調査編第 2 版 (2013 年度 W G 2) ストアドプロシージャ移行調査編第 3 版 (2017 年度 W G 2) 所属企業団体名部署名氏名クオリカ株式会社開発センター坂本浩行インフォメーションクリエーティブ株式会社ソリューション開発本部林田竜一 SRA O SS, In c. 日本支社 O SS 事業本部佐藤友章 SRA O SS, In c. 日本支社 O SS 事業本部千田貴大 Page 23 of 23

PGECons技術ドキュメントテンプレート Ver.3

PGECons技術ドキュメントテンプレート Ver.3 エンタープライズコンソーシアム技術部会 WG#2 ストアドプロシージャ移行調査編製作者担当企業名クオリカ株式会社 2013 Enterprise Consortium 改訂履歴版改訂日変更内容 1.0 2013/03/25 新規作成ライセンス本作品は CC-BY ライセンスによって許諾されていますライセンスの内容を知りたい方は http://creativecommons.org/licenses/by/2.1/jp/

WG2活動報告書 ストアドプロシージャ移行調査編

WG2活動報告書ストアドプロシージャ移行調査編