PL/SQLをPostgreSQLに移行する ～移行概要/移行例～
PostgreSQLインサイド

Oracle DatabaseからPostgreSQLへの移行を検討する際、膨大なPL/SQL（注1）資産をどのように移行すればよいか、お困りになったことはないでしょうか。この記事では、これからOracle DatabaseからPostgreSQLへの移行を担うエンジニアの皆さまが、安全かつスムーズに移行を実施できるよう、PL/SQLとPL/pgSQL（注2）の違い、移行の概要やポイントについてサンプルを交えて解説します。
対象となるOracle Databaseのバージョンは10g以降、PostgreSQLのバージョンは11以降を想定しています。

• 注1）

  Oracle Database独自の手続き型言語の1つ

• 注2）

  PostgreSQL独自の手続き型言語の1つ

なお、Oracle DatabaseからPostgreSQLへの移行にあたっては、まずアーキテクチャーの違いや移行プロセスの概要について押さえておくことが重要です。以下の記事で解説していますので、まだ読まれていない方は、是非先にお読みください。

• データベース移行で押さえておくべきこと ～アーキテクチャーと機能の違い～
• データベース移行で押さえておくべきこと ～移行プロセスについて～

目次

• 1. 移行対象となるOracleの手続き言語の種類
• 2. PL/SQLとPL/pgSQLの基本的な構文と呼び出し方の違い
• 3. 移行時によくある非互換の観点と対処方法

1. 移行対象となるOracleの手続き言語の種類

ここでは、移行の対象となるOracle Databaseの手続き言語の種類と、それに対応するPostgreSQLの機能について説明します。

Oracle DatabaseのPL/SQLは、無名ブロックとストアド・サブプログラムに分類されます。さらに、ストアド・サブプログラムはスタンドアロン・サブプログラムとパッケージ・サブプログラムに分類され、スタンドアロン・サブプログラムにはストアド・プロシージャとストアド・ファンクションがあります。以下の図では、移行の対象となる手続き言語の種類と、対応するPostgreSQLの機能を、太字で示しています。

• 注1）

  ストアド・パッケージにはOracle社から標準で提供されているパッケージも含まれますが、本記事では対象外とします。

参考

OSSのPostgreSQLをエンタープライズ向けに強化した製品「FUJITSU Software Enterprise Postgres（以降、Enterprise Postgresと略す）」では、PL/SQLパッケージの中でもよく利用されている以下のものを、パッケージとして提供しています。

DBMS_ALERT、DBMS_ASSERT、DBMS_OUTPUT、DBMS_PIPE、DBMS_RANDOM、DBMS_UTILITUY、UTL_FILE、DBMS_SQL

なお、PL/SQLパッケージとは仕様の差異があるため、利用の際にはマニュアルを確認してください。

無名ブロック

PostgreSQLでは無名コードブロック（DO文）に相当します。

• 特徴：スクリプト言語から直接SQLやPL/SQL制御ステートメントの処理を記述したものです。
• 仕組み：シェルスクリプトやバッチのようなものです。1行ずつデータベースと通信しながら実行されます。
• 違い：構文が少し異なります。Oracle Databaseでは無名ブロック内にサブプログラムを書けますが、PostgreSQLでは書けません。

ストアド・プロシージャ

• 特徴：SQLやPL/SQL制御ステートメントを組みわせた処理手順を記述したものです。トランザクション制御が利用できます。
• 仕組み：解析済みの状態でサーバーに格納され、記述した処理はサーバー上で実行されます。復帰値を返しません（ただし、OUTパラメーターを使うことで値を返すことが可能）。
• 違い：構文が少し異なります、Oracle Databaseではストアド・プロシージャ内にサブプログラムを書けますが、PostgreSQLでは書けません。

ストアド・ファンクション

• 特徴：SQLやPL/SQL制御ステートメントを組みわせた処理手順を記述したものです。トランザクション制御は利用できません。
• 仕組み：解析済みの状態でサーバーに格納され、記述した処理はサーバー上で実行されます。復帰値を返します。
• 違い：構文が少し異なります、Oracle Databaseではストアド・ファンクション内にサブプログラムを書けますが、PostgreSQLでは書けません。

ストアド・パッケージ

• 特徴：機能や用途などの単位で、複数のプロシージャやファンクションをパッケージングしたものです。
• 仕組み：解析済みの状態でサーバーに格納され、記述した処理はサーバー上で実行されます。
• 違い：PostgreSQLには同様の機能は無く、移行にはストアドプロシージャやストアドファンクションに分解して置き換えていく必要があります。

ストアド・パッケージの移行については、本記事の説明に加えてストアド・パッケージならではの手法が必要になります。詳しくは「PL/SQLのストアド・パッケージをPostgreSQLに移行する ～移行概要/移行例～」も併せてお読みください。

2. PL/SQLとPL/pgSQLの基本的な構文と呼び出し方の違い

Oracle DatabaseのPL/SQLと、PostgreSQLのPL/pgSQLでの基本的な構文と呼び出し方の違いについて説明します。

無名ブロック

PostgreSQLでは、無名コードブロック（DO文）で記述します（構文の[]は省略可能）。

呼び出し方：

ストアド・プロシージャ

PostgreSQLでは、PostgreSQL 11からストアドプロシージャが使えるようになりました。ただし、以下のように構文に違いがあります。

• 補足）

  Oracle Databaseの場合、パラメーターが無い場合は引数用の括弧()は不要ですが、PostgreSQLでは必要です。

呼び出し方：

ストアド・ファンクション

以下のように構文に違いがあります。

• 補足）
  • Oracle Databaseでは、パラメーターが無い場合は引数用の括弧()は不要ですが、PostgreSQLは必要です。
  • PostgreSQLでは、戻り値が無い場合は、RETURNS VOIDを指定します。

呼び出し方：

• 補足）

  PostgreSQLでは、戻り値を受け取る変数を定義するため、無名コードブロックを使います。データ型は、PostgreSQLで扱える型に変更します。なお、VARCHAR2(64)は64バイトの文字列を意味しますが、VARCHAR(64)は64文字の文字列を意味します。

3. 移行時によくある非互換の観点と対処方法

移行に際しては、PL/SQLとPL/pgSQLの構文の違いだけでなく、多くの非互換を考慮する必要があります。そのうち、よく遭遇する非互換の観点を以下に挙げました。ここでは、これらの観点と対処方法について説明します。

• VARIABLE変数宣言
• コレクション型を戻すストアド・ファンクション
• トランザクション制御
• 制御文（GOTO文）
• 制御文（FORALL文）
• 制御文（FOR LOOP文）
• 例外処理
• %ROWTYPE属性（ファンクションやプロシージャ引数として利用する場合）
• カーソル属性（%ISOPEN / %NOTFOUND / %FOUND / %ROWCOUNT）
• その他（関連する非互換）
  • 長さ0の文字列（空文字列）
  • 結合（||）
  • 暗黙の型宣言
  • テーブルの外部結合（+）

VARIABLE変数宣言

VARIABLE変数は、SQL*Plusのプロンプト上で宣言できる変数であり、PL/SQL内で使用することができます。PostgreSQLのpsqlのプロンプト上の変数は、「¥set <変数名> <値>」（¥はバックスラッシュ）で定義すれば使えますが、扱えるのは文字列のみです。そのため、移行の手段としては、その変数、および、その変数を利用する処理ロジックを、無名データブロック、あるいは、ストアドファンクションの中で記述することで代替できます。

移行例：

コレクション型を戻すストアドファンクション

上記の「2.基本的な構文と呼び出し方の違い」の「ストアド・ファンクション」のところで示したように、単純なデータ型を戻り値として返す場合は、データ型を変更するだけで移行できます。
しかし、ストアド・ファンクションの利用方法として、例えば、ある商品のその日の注文個数や売上額などをレコードとして抽出するような用途で使われることもよくあります。そのようなストアド・ファンクションの戻り値には、必要情報を含む複数のレコードを返すために、コレクション型などが使われます。そこで、ここではコレクション型を戻すストアド・ファンクションを例に挙げて説明します。

Oracle Databaseでは、コレクション型を定義するIS TABLE OFや、その型を定義するOBJECT型などが使われますが、PostgreSQLには同様の機能が存在しません。そのため、PostgreSQLでは別の手段で実現します。IS TABLE OFについては、PostgreSQLのファンクションがテーブルを返却する機構としてSETOFがあるため、これを代用します。OBJECT型についてはCREATE TYPEで代用します。

移行例：

トランザクション制御

ストアド・プロシージャを使用している場合は、トランザクション制御を利用できます。
ただし、Oracle DatabaseとPostgreSQLとでは、トランザクション制御自体に非互換があります。まず、その例を説明します。

Oracle Databaseは、SQL文が実行されると暗黙的にトランザクションを開始し、COMMIT/ROLLBACKでトランザクションを終了します。トランザクション内の特定のSQL文がエラーになったとしても、成功したSQL文はコミットされます。以下の例では、SQL文1およびSQL文3がコミットされることを示しています。
なお、コミットしなかった場合、データベースとの接続が切断されたタイミングで暗黙的にコミットされます。

PostgreSQLは、BEGIN文でトランザクションを開始し、END/COMMIT/ROLLBACKでトランザクションを終了します。なお、BEGIN文が無い場合は、SQL文の1行ごとに暗黙的にトランザクションをコミットします。トランザクション内で1つでもSQL文がエラーになると、成功したSQL文も含めてすべてのSQL文がコミットされません。以下の例では、SQL文1がロールバックされることを示しています。
なお、トランザクション終了時やデータベースとの接続が切断されたタイミングで暗黙的にロールバックされます。

以上のような非互換があるため、ストアドプロシージャを移行する際にもその影響を受けます。以下に注意すべき非互換について説明します。

1) エラー発生時のロールバックについて

Oracle Databaseは、エラーが発生してEXCEPTIONに移動した際に、トランザクション制御を行う必要があります。また、カーソルなどの資源が残っている場合には回収処理が必要です。

PostgreSQLは、エラーが発生すると、トランザクション内で行った処理がすべてロールバックされます。これにより、トランザクション制御やカーソルの資源回収などはシステム側で行われるため、記述する必要がありません。なお、プロシージャの呼び出し元において、エラーが発生した際のリトライ処理があれば、どのSQL文から再実行するかなども別途見直す必要があります。

2) EXCEPTION句を含むプロシージャ内にトランザクション制御がある場合について

EXCEPTION句を含むプロシージャ内にトランザクション制御（COMMIT/ROLLBACK）がある場合、Oracle DatabaseとPostgreSQLとでは動作が異なります。Oracle Databaseでは、定義時 / 実行時にエラーになりません。PostgreSQLでは、定義時に正常終了しますが、実行時にCOMMITやROLLBACKでエラーになります。なお、EXCEPTION句の中にはCOMMIT/ROLLBACKを記述することができます。

対処方法としては、以下の2つの方法があります。

• A）COMMIT/ROLLBACKの単位でストアドプロシージャを分割し、上位の呼び元(JDBCなど)でCOMMIT/ROLLBACKおよびEXCEPTIONを処理します。
  以下の例では、ストアドプロシージャをCOMMIT/ROLLBACKの単位で分割し、トランザクション制御（COMMIT/ROLLBACK）を上位の呼び出し元で行っています。

• B）EXCEPTIONでキャッチすべき対象の処理だけを1階層深いサブブロックで記述し、その上位でCOMMIT/ROLLBACKを処理します。
  詳細は、PostgreSQL技術インデックス「PostgreSQLでストアドプロシージャを使用する」の「3.1 トランザクション制御に関する制約」において、サブブロック化の説明を参照してください。

3) ストアドプロシージャ内のトランザクション制御がエラーになるケースについて

PostgreSQLについて、トランザクションブロック、あるいは、ストアドファンクションから呼び出したストアドプロシージャの中にトランザクション制御（COMMIT/ROLLBACK）を記述することができません。実行時エラーになります。

対処方法の一例として、呼び出し元のトランザクションブロックを無名コードブロックやストアドプロシージャで記述する方法を示しますので、対応を検討してください。

制御文（GOTO文）

PostgreSQLではGOTO文が利用できないため、GOTO文を利用しないよう、構造化された処理に書き換える必要があります。複数のルートから呼ばれる処理へのGOTO文であれば、その処理をプロシージャ化して呼び出したり、ループ内の処理を抜けるGOTO文であれば、GOTO文をCONTINUE文やEXIT文に置き換えたりすることで対処します。

移行例：

制御文（FORALL文）

PostgreSQLには、FORALL文はありません。FOR LOOP文に変更してください。なお、パフォーマンス劣化に注意が必要です。

移行例：

• 補足）

  depts.FIRSTやdepts.LASTは、Oracle Databaseのコレクション・メソッド・コールであり、PostgreSQLではサポートされていません。移行方法としては、先頭のデータは1に固定し、最後のデータはarray_length関数を使うことで代替しています。

制御文（FOR LOOP文）

PostgreSQLでもFOR LOOP文は利用できます。ただし、FOR LOOP文で使用する変数がレコード型の場合は、使用するレコード型変数をPostgreSQLの定義に変更する必要があります。また、REVERSE指定がある場合は、下限値と上限値を入れ替える必要があります。

移行例：

例外処理

EXCEPTIONの記述方法に違いはありませんが、対応するPostgreSQLの例外名やエラーコードへの書き換えが必要となる場合があります。

移行例：

%ROWTYPE属性（ファンクションやプロシージャ引数として利用する場合）

PostgreSQLでは、ストアドプロシージャやストアドファンクションの引数に、「テーブル名%ROWTYPE」の指定はできません。そのため、複合型で別途定義する必要があります。また、呼び出し元の修正も必要になります。

移行例：

カーソル属性（%ISOPEN / %NOTFOUND / %FOUND / %ROWCOUNT）

カーソル属性はPostgreSQLにはありません。以下のような代替方法を使って移行を行います。なお、GET DIAGNOSTICS構文は、直前の実行SQL文の処理件数を取得する機構です。

移行例：

その他（関連する非互換）

PL/SQLのコード内に記載されるその他の非互換として、よく遭遇するものについて以下に列挙します。

• 長さ0の文字列（空文字列）
• 結合（||）
• 暗黙の型宣言
• テーブルの外部結合（+）

長さ0の文字列（空文字列）

''（シングルクォーテーションが2つ連続）で表現される「長さ0の文字列」について、Oracle DatabaseとPostgreSQLでは扱いが異なります。Oracle Databaseでは、変数などに長さ0の文字列を代入すると、実際にはNULLが格納されるため、その変数を判定する場合には、"IS NULL"や、"IS NOT NULL"で判定します。それに対して、PostgreSQLでは長さ0の文字列とNULLを区別して格納されるため、ここに非互換があります。

そのため、Oracle DatabaseからPostgreSQLに移行する際には、基本的に長さ0の文字列('')を設定している箇所において、長さの0の文字列をNULLに変更します（なお、判定文の方を書き換える手段もありますが、ロジックを考慮する必要があるため工数が増える可能性があります）。

書き換え例：

参考までに、長さ0の文字列が設定される主なケースを以下に示します。

• 代入文 name := '' ;
• ファンクションパラメタ func('abc', 3, '', 10)
• ファンクションパラメタ定義する際のデフォルト値 DEFAULT ''
• ファンクションの復帰値 RETURN ''
• SELECT文の選択リスト SELECT '' as text,・・・
• INSERT文のVALUE句 INSERT ・・・ VALUE('', ・・・)
• UPDATE文のSET句 UPDATE ・・・ SET name = '' ・・・

なお、以下の場合はこの非互換とは関係ありません。

• replace関数の第3引数にある長さ0の文字列
• 長さ0の文字列の||による文字列連結

結合（||）

処理結果メッセージのテキストを作成する際に、結合（||）を利用してテキストを結合することはよく見られます。
文字列連結の中にNULLが含まれている場合、Oracle DatabaseではNULLを無視しますが、PostgreSQLでは1つでもNULLが含まれていると結合文字全体がNULLになります。
そのため、結合するテキストがNULLになる箇所の見直しが必要です。

該当箇所が見つかった場合、CONCAT関数(NULLは無視されます）の利用や、NVL関数やCOALESCE関数でNULLを抑止することを検討してください。また、結合する数が多い場合などは、連結（||）のオーバーロードを作成し、その中でNULLを抑止する処理を入れる手段もあります。

書き換え例：

暗黙の型宣言

異なる型どうしの比較や演算などにおいて、Oracle Databaseでは暗黙に、強力な型変換が行われます。
PostgreSQLにも暗黙の型変換の機構がありますが、基本的な型変換のみ行われます。

Oracle DatabaseからPostgreSQLに移行する際に、データ型が合っていない旨のエラーが多くでることが良くあります。
そのため、異なる型どうしの比較や演算を行っている箇所について、明示的な型変換、あるいは、CREATE CASTを利用して新たな型変換を定義するなどの対処が必要になります。

書き換え例：

テーブルの外部結合（+）

PostgreSQLは、外部結合（+）に対応していません。
LEFT OUTER JOIN/RIGHT OUTER JOIN句を用いた結合に書き換えてください。

書き換え例：

参考

PostgreSQLは、他のデータベースからの移行を考慮した改良が行われています。PL/pgSQLの関連において、以下の互換性対応があります。

• PostgreSQLのパラメーター定義は「モード/パラメタ名/データ型」だが、互換性対応のため「パラメタ名/モード/データ型」も可能
• PostgreSQL 14からプロシージャやファンクションのパラメーター定義にOUTモードをサポート

Oracle DatabaseのPL/SQLからPostgreSQLのPL/pgSQLへの移行について、概要と移行例を説明しました。本記事を活用して、安全かつスムーズに移行作業を進めて頂ければ幸いです。

2022年11月18日公開

こちらもおすすめ

• データベース移行で押さえておくべきこと ～アーキテクチャーと機能の違い～
• PostgreSQLでストアドプロシージャを使用する
• PL/SQLのストアド・パッケージをPostgreSQLに移行する ～移行概要/移行例～

本コンテンツに関するお問い合わせ