実績のあるSQLなのに動かない？移植で問題になりやすいDEOCDEについて解説します

SQLの移植でエラーになる原因として挙げられるのがDECODEです。そこで、今回はDBサーバー毎のDECODEの機能と、DECODEでエラーになった場合の書き換え方法についてご紹介します。

SQLのエラーの原因となるDECODEとは

SQLは、国際標準として規定されたデータベース言語であり、基本的な機能はどのデータベース管理ソフトでも動作します。しかし、ソフト毎にいろいろな拡張が施されており、別のソフトで実績のあるSQLでも、移植したら使えない、といった機能もあります。そのようなSQLの中でも、特に注意が必要なコマンドがDECODEです。

DECODEの詳しい解説の前に、まずは、なぜDECODEに注意が必要かについて説明します。

データベースの標準とも言えるOracle

データベースエンジニアにとって、SQLが使えるデータベース管理ソフトの標準と言えば、Oracle Databaseを挙げる方が多いでしょう。

Oracle Databaseは、まだ、Linuxが登場する遥か前の1979年に登場した、かなり古いソフトウェアです。SQLは、Oraclde Databaseが登場した後の1986年に制定され、その後に何度も改版されていますが、Oracle Databaseはそれに準拠するものの、独自の拡張機能も数多く利用されてきました。

そして、そういった拡張機能の1つが今回取り上げるDECODEです。なお、今はデータベース管理ソフトと言えば、Webシステムなどのアプリケーションの裏側で動作しており、直接操作する機会は減っています。しかし、昔は、データベース管理ソフトを操作して、リスト出力などの処理に活用していました。そして、そういった処理で便利だったのが、DECODEです。

後発のオープンソースのデータベース管理ソフト

今、シェアの多いデータベース管理ソフトと言えば、Oracle Database, MySQL, SQL Serverの3つです。この中でもオープンソースのMySQLは、Linuxサーバーに標準でインストールされており、Webシステムで使うデータベースの標準ともいえるソフトです。

また、MySQLが登場したのは1995年であり、サーバーOSのLinux、WebサーバーのApache、プログラム言語のPHPとセットで、LAMPスタックとして広く普及しました。そのためMySQLは、国際標準のSQLに準拠しており、さらにPHPなどのプログラム言語に似た機能が多く組み込まれています。

そして、MySQLでは、文字列の暗号化を扱う機能として、プログラム言語と同じような名称のENCODEとDECODEが組み込まれています。

OracleとMySQLではDECODEの意味が違う

このように、昔からデータベースの標準とも言われるOracle Databaseと、Webシステムで使われることの多いデータベース管理ソフトのMySQLとでは、DECODEの機能が違います。

このように、DECODEが使われていると、Oracle Databaseで実績のあるSQLだとしても、MySQLで動作するとは限りません。もし、データベースの専門家に作成してもらったSQLがMySQLでエラーになったら、それはDECODEが原因かもしれません。

ポテパンダの一言メモ

Oracle DatabaseのDECODEと、MySQLのDECODEは別の機能です。そのため、Oracle Databaseで実績のあるSQLでも、DECODEが原因でMySQLではエラーになります。

Oracle DatabaseのDECODEの機能

Oracle DatabaseのDECODEは、条件分岐に使われる機能です。なお、Oracle Databaseでは、条件分岐としてCASEとDECODEの2つが使えますが、DECODEは制限があるものの、簡潔に書けることからよく使われます。

条件分岐として使われるDECODEの文法は、次のとおりです。

これを簡単に解説すると、引数の1番目が比較対象で、データが文字列や数字に一致するかをチェックします。そして、もしその値が値1と一致したら、結果1を返し、一致しなかったら結果2を返します。また、引数は255個まで指定できるので、複数の条件を記述できます。なお、DECODEの中では式が使えないので注意してください。

次に、DECODEを使ったSQLの例を次に紹介します。

OracleのDECODEを使ったSQL文の例

SELECT
DECODE( table1.flg, 1, 'OK', 'NG')
FROM table1;

この例では、table1のflagの値が1だったら、’OK’を返し、一致しなかったら’NG’を返します。

Oracle DatabseのDECODEの制限

先ほど解説したように、DECODEは簡潔に書けるので便利ですが、引数に式を指定できません。そのため、値の判定に大小比較やワイルドカードが使えず、さらに、結果も単純な文字列などしか使えません。

Oracleでそういった比較をする場合は、CASEを使うのが一般的です。そのため、リスト出力のためのSQLを簡潔に記述する方法として使われます。

MySQLのDECODEの機能

新しい世代のデータベース管理ソフトであるMySQLやPostgreSQLはプログラム言語の影響を受けており、暗号化の処理に、ENCODE()とDECODE()を使います。そして、MySQLでDECODEといえば、テーブルに暗号化されて格納されたデータを、デコードして扱うための関数です。

MySQLのDECODE()の文法

DECODE( 復号化するデータ, パスワード )

DECODEの書き換え例

Oracle DatabaseのDECODEと同じ機能は、企業向けのデータベース管理ソフトとして長く使われているIBM製のDB2などでも使えます。しかし、Webシステムでデータベースを利用するなら、MySQLかPostgreSQLなどのオープンソースのデータベース管理ソフトで使いたいものです。

しかし、Oracle Database用に書かれたSQLで、DECODEが使われていると、それがエラーの原因になって正常に動作しません。しかし、DECODEは、簡単に書き換えることが可能です。次から、MySQLやSQL Serverで使えるDECODEの書き換え例をご紹介します。

CASEで書き換える

Oracleで作られたDECODEを含むSQLを、MySQLなどで利用する場合によく使われるのが、CASEで置き換える方法です。なおCASEは、SQLにおける条件分岐の方法の一つで、プログラム言語のIFと同じように使用できます。

CASEの文法は次のとおりです。

CASEを使ってDECODEを書き換えた例を次に紹介します。

OracleのDECODEを使ったSQL文の例

SELECT
DECODE( table1.flg, 1, 'OK', 'NG')
FROM table1;

CASE文でDECODEを置き換えたSQLの例

SELECT
CASE table1.flg
WHEN 1 THEN 'OK'
ELSE 'NG' END
FROM table1;

MySQLならIFで書き換える

MySQLでは、プログラム言語でよく使われる条件分岐の定番、IF ELSEが使えます。そのため、Oracle DatabseのDECODEは、MySQLならIF ELSEで書き換えられます。

MySQLのIF ELSEの基本は、次のとおりです。

次に、IFを使ってDECODEを書き換えた例を次に紹介します。

OracleのDECODEを使ったSQL文の例

SELECT
DECODE( table1.flg, 1, 'OK', 'NG')
FROM table1;

IF文でDECODEを置き換えたSQLの例

SELECT
IF( table1.flg = 1, 'OK', 'NG')
FROM table1;

SQL ServerならIIFで書き換える

マイクロソフト製のSQL Serverは、独自の拡張機能が多く、条件分岐にIIFが使えるのも特徴の一つです。なお、IIFは、CASEが使えないAccessで使える条件分岐の機能です。

IIFの文法は次のとおりです。

次に、IIFを使ってDECODEを書き換えた例を次に紹介します。

OracleのDECODEを使ったSQL文の例

SELECT
DECODE( table1.flg, 1, 'OK', 'NG')
FROM table1;

IIF文でDECODEを置き換えたSQLの例

SELECT
IIF( table1.flg = 1, 'OK', 'NG')
FROM table1;

DECODEの書き方はプログラムでも可能

Oracle DatabaseからMySQLなどにSQLを移植する際に、よくエラーになるDECODEについて、その文法と置き換え方法についてご紹介しました。そもそも、WebシステムでそのSQLを使うのなら、Rubyなどのプログラムで処理した方が簡単なケースもあります。

DECODEをOracle DatabaseからMySQLなどに移植するのは可能ですが、移植にかかる工数とプログラムを修正する工数を比較するなど、最も良い方法を検討されると良いでしょう。