Oracle PL/SQLからMySQLへの移行苦労話

Oracleをバックエンドとする老朽化システムのマイグレーションにおいて、ライセンス料の課題から、無償のMySQLへRDBをダウンサイジングする、というのはよくある話です。しかし、テーブルデータだけではなく、サーバサイドに実装されているOracle PL/SQLがあった場合、これらもMySQLに移植し、クライアントの移行コストを抑える、という選択肢が出てきます。

ところが、OracleのPL/SQLとMySQLのサーバ処理（Procedure、Function）は、仕様的にいくつかの差があるため、実際に移行しようとすると、なかなか骨が折れます。今回は、実際に移行してみた経験から、大変だった苦労話をいくつかピックアップしてご紹介します。

パラメータ付カーソルが使えない

PL/SQLで複数行のデータ処理を行う場合、よくカーソルを使用します。MySQLにもカーソルは存在するのですが、こちらでは引数を指定する（Oracleのパラメータ付カーソル）ことができません。

このため、MySQLでカーソルの抽出条件を動的に変更するためには、予めカーソルが参照数ための広域変数を定義し、カーソルをOPENする前に、毎回変数に値をセットする必要があります。

<Oracleの場合>

  -- カーソル定義
  CURSOR cur(pi_num1 INT, pi_num2 INT) IS -- パラメタ付きでカーソルを定義する
    SELECT fieldA, filedB
      FROM table1
     WHERE field1 = pi_num1
       AND field2 = pi_num2;

BEGIN
  -- 処理部
  OPEN cur(11, 22); CLOSE cur;   -- 引数として条件を指定できる
  OPEN cur(33, 44); CLOSE cur;   
  OPEN cur(55, 66); CLOSE cur;   

<MySQLの場合>

BEGIN
  -- 変数定義
  DECLARE pi_num1    INT;      -- 条件に使用する変数を定義する
  DECLARE pi_num2    INT;

  -- カーソル定義
  DECLARE cur CURSOR FOR 
    SELECT fieldA, filedB
      FROM table1
     WHERE field1 = pi_num1    -- 定義した変数をSQLに渡す
       AND field2 = pi_num2;

  -- 処理部
  SET pi_num1 = 11;            -- カーソルOPEN前に、毎回変数をセットする
  SET pi_num2 = 22;
  OPEN cur; CLOSE cur;

  SET pi_num1 = 33;
  SET pi_num2 = 44;
  OPEN cur; CLOSE cur;

  SET pi_num1 = 55;
  SET pi_num2 = 66;
  OPEN cur; CLOSE cur;

パラメタによって、同一の問合せをカーソルで再利用できるメリットが半減してしまい、コードも冗長となります。

%ROWTYPEが使用できない

Oracleでは変数の一種として、テーブルの行を模した「%ROWTYPE」が使用できます。これは、Procedureの引数の返り値のI/Oや、先のカーソルのFETCH結果の受け取りで使用されます。

しかし、MySQLにはこの「%ROWTYPE」がありません。テーブルの行そのものを変数に格納することができないため、必要となるフィールド毎に個別に変数を定義して、それぞれ代入・取り出し操作を行う必要があります。

<Oracleの場合>

  -- カーソル定義
  CURSOR cur IS
    SELECT *
      FROM table1;

  rec    table1%ROWTYPE;   -- %ROWTYPEで変数recを宣言

BEGIN
  -- 処理部
  OPEN cur;
  FETCH cur INTO rec;                               -- recに、table1の1行文が取り出される
  result := rec.fieldA || rec.fieldB || rec.fieldC; -- rec.[列名]で、任意の値が取得できる
  RETURN result;

<MySQLの場合>

BEGIN

  -- 変数定義
  DECLARE rec_fieldA    VARCHAR(10);   -- フィールド毎の格納変数を宣言
  DECLARE rec_fieldB    VARCHAR(10);
  DECLARE rec_fieldC    VARCHAR(10);

  DECLARE cur CURSOR FOR 
    SELECT fieldA, filedB, fieldC      -- FETCHで受け取るフィールドを個別に指定 ※
      FROM table1;

  OPEN cur; CLOSE cur;
  FETCH cur INTO rec_fieldA, rec_fieldB, rec_fieldC;  -- SELECT句と同じ並びで変数を指定
  SET result = CONCAT(rec.fieldA, rec.fieldB, rec.fieldC);

※SQL部分を、Oracleのように SELECT * FROM ~とすることも可能ですが、その場合は、FETCH INTO に対象テーブルのすべてのフィールドを列挙しないといけません。フィールド数不一致でエラーとなります。

変数宣言、SQLのSELECT句、FETCHのINTO先、いずれにも、カーソルから取得するフィールドを列挙する必要があるため、コードが非常に大きくなります。

このROWTYPEと、前述のパラメタ付カーソルが使用できない件によって、PL/SQLのカーソルをMySQLに移植すると、次のように非常に長いコードになります。

<MySQLのカーソル使用例>

BEGIN
  -- 変数宣言
  DECLARE ci_fieldA  AS INT;
  DECLARE ci_fieldB  AS INT;
  DECLARE ci_fieldC  AS INT;
  DECLARE ci_fieldD  AS INT;
  DECLARE ci_fieldE  AS INT;
  -- カーソルにINPUTしたい変数をすべて定義

  DECLARE co_field1  AS INT;
  DECLARE co_field2  AS INT;
  DECLARE co_field3  AS INT;
  DECLARE co_field4  AS INT;
  DECLARE co_field5  AS INT;
  -- カーソルからOUTPUTしたいフィールド分すべて定義

   -- カーソル宣言
  DECLARE cur CURSOR FOR
  SELECT fiedl1, field2, field3, field4, field5 ...  -- 取得したいフィールドはすべて列挙
    FROM table1
   WHERE fieldA = ci_fieldA  
     AND fieldB = ci_fieldB  
     AND fieldC = ci_fieldC;  
     AND fieldD = ci_fieldD;  
     AND fieldE = ci_fieldE;  

   -- 処理部
  SET ci_fieldA = 11;
  SET ci_fieldB = 22;
  SET ci_fieldC = 33;
  SET ci_fieldD = 44;
  SET ci_fieldE = 55;
  -- カーソルにINPUTしたい変数すべてに値を設定

  OPEN cur;
  FETCH cur INTO co_field1, co_field2, 
        co_field3, co_field4, co_field5 .... -- カーソルから受け取る変数をすべて列挙
  CLOSE cur;

カーソル属性 %NOTFOUND等が使用できない。

次もカーソルに関する問題です。カーソルをループ内でFETCHするとき、すべての行を読み取ったかを判断するために、Oracleでは「カーソル名%NOTFOUND」属性によって判断します。MySQLでは、この「読み切った」ということをハンドラで判定します。

<Oracleの場合>

BEGIN
  -- カーソル定義
  CURSOR cur IS
    ... 
IS
  OPEN cur;
  LOOP
    FECH cur INTO rec;
    EXIT WHEN cur%NOTFOUND;  -- %NOTFOUNDを参照してループを抜ける

    ... （ループごとの処理）

  END LOOP;
  CLOSE cur;

<MySQLの場合>

BEGIN

  -- 変数定義
  DECLARE done INT DEFAULT FALSE;  -- ループ終了判定変数（初期値はFALSE）

  -- カーソル定義
  DECLARE cur CURSOR FOR
    ... 

  -- NOTFOUNDハンドラ 定義
  DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET done = TRUE;  -- NOTFOUND時にdoneにTRUEを設定

  OPEN cur;
  loop1 : LOOP
    FECH cur INTO rec;

    IF done THEN    -- doneがTRUEになったら処理終了
        LEAVE loop1;
    END IF;

    ... （ループごとの処理）

  END LOOP loop1;
  CLOSE cur;

こちらは先ほどのような機能不足ではなく、OracleとMySQLの「仕様（お作法）の違い」ではありますが、地味に書き換えが面倒です。カーソルはPL/SQLの主要機能のため数が多く、ひとつのProcedureに複数のカーソルがあった場合には、それぞれを判定するための終了判定変数を作る必要がある（あるいはそれぞれをBEGINで括り、干渉を避ける）など、移行に手間がかかります。

その他

上記以外にも、二つのDBMSには組込み関数の名称の違いや、内部仕様の差異など、多くの違いがあります。

以下に一部をご紹介します。

概要	Oracle	MySQL	移行時の注意点
NULL値判定の違い	'(空)'もNULLもNULL扱い（'' IS NULL = TRUE）	'(空)'はNULLではない （'' IS NULL = FALSE）	入力値に応じて複数の条件式に分ける。 　　IF (A='') OR (A IS NULL) THEN これ以外にも、NVLやISNULLといったNULL判定に注意が必要である
DECODEが使えない	DECODE(A, 1, 'yes', 'no');	CASE A WHEN 1 THEN 'yes', 'no' END	左記のとおり書き換える
配列データの取り回し	VARRAY、連想配列、ネスト表などが使用できる	配列がないため、JSON型のリストを使用する 例 '[ 1, 2, 3 ...]'	データの加工・取出しには、MySQL組込み関数である「JSON関数」を使用する
NULL置き換え方法の違い	NVL(abc, 0);	IFNULL(abc, 0);	左記のとおり書き換える
文字列連結方法の違い	data := a || b || c;	SET data = CONCAT(a, b, IFNULL(c, ''));	CONCATは一つでもNULLがあると結合結果がNULLとなるため、NULLが入りえる変数にはIFNULLと併用する。
再帰ルーチンの実装	できる	できない	別途実装方法を検討

まとめ

今回、実際に移行を行ってみて、OracleのPL/SQLの優秀さを改めて知るとともに、MySQLの非力さにうんざりしました。特に、カーソル制御まわりのI/Oがチープすぎで、古のＣＯＢＯＬのようだと、最初は目を疑いました。引き渡し用の変数が相当数必要になるため、書き換えでミスが生まれそうで怖かったです。

こういったリプレース作業は、いつか、巷の生成ＡＩがさくっとこなしてくれるようになるんでしょうね（遠い目）