mysql の ELT と FIELD ファンクションの作成

Oracle の DECODE のサブセット的な MYSQL のファンクション ELT と FIELD を PL/SQL で作成する。
ELT と FIELD をネストして使用することで完全ではないが DECODE と似た処理ができる。(※1)

ファンクションの仕様

  • ELT ( pos , string1 [, string2 ...])

    文字列リスト( string1 , string2 , ....) の pos 番目の要素を戻す。
    pos = 1 の場合 string1、pos = 2 の場合 string2。 pos が 0 以下、または、文字列リストの数を超える場合 NULL を戻す。

  • FIELD ( expr, expr1 [ , expr2 ...])

    リスト ( expr1 , expr2 , ....) 中で expr が最初に見つかった要素番号(1 オリジン)、見つからない場合 0 を戻す。
    expr, および expr[1..N] は同じデータ型で VARCHAR2 、NUMBER、または、BINARY_DOUBLE 型のいずれか。
    NULL 同士の比較結果は UNKNOWN となるため、expr が NULL の場合、戻り値は 0 となる。

2つのファンクションとも引数の最大数は 256 (expr , expr1, .. expr255) 

(※1) FIELD ファンクション内での NULL 同士の比較は UNKNOWN、DECODE において NULL 同士の比較は TRUE になるので結果が異なる。

パッケージのソース

FIELD ファンクションは オーバーロード を使用しているので 「スタンドアロン・サブプログラム」 に書き換えはできません。
PL/SQL で可変長の引数のストアドプロシージャは作成できないため力技で実装を行っています。

対象ファイル名漢字コード
パッケージファイルfilemysqlapp.sql.1Shift JIS

ELT と FIELD の使用例

'ONE', 'TWO', 'THREE' のリストから 1 番目の要素を戻す。

SQL> select mysqlapp.elt(1, 'ONE', 'TWO', 'THREE') from dual;
 
MYSQLAPP.ELT(1,'ONE','TWO','THREE')
------------------------------------
ONE

'ONE', 'TWO', 'THREE' のリストから 'TWO' の要素番号を戻す。

SQL> select mysqlapp.field('TWO', 'ONE', 'TWO', 'THREE') from dual;
 
MYSQLAPP.FIELD('TWO','ONE','TWO','THREE')
-----------------------------------------
				        2

'ONE', 'TWO', 'THREE' を 'Ⅰ', 'Ⅱ', 'Ⅲ' に置き換える

SQL> select mysqlapp.elt(app.field('TWO', 'ONE', 'TWO', 'THREE'), 'Ⅰ', 'Ⅱ', 'Ⅲ') from dual;
 
MYSQLAPP.ELT(MYSQLAPP.FIELD('TWO','ONE','TWO','THREE'),'Ⅰ','Ⅱ','Ⅲ')
--------------------------------------------------------------------------------

'XS', 'XL' を '2S', '2L' に置き換え、結果を 'XS', 'S', 'M', 'L', 'XL' の順に並べ替える。

SQL> select
  2      size_name,
  3      mysqlapp.elt(mysqlapp.field(size_name, 'XS','XL'), '2S', '2L') ALIAS_NAME,
  4                   mysqlapp.field(size_name, 'XS','XL') IDX
  5  from size_sample
  6  order by size_name;
 
SIZE_NAME  ALIAS_NAME	     IDX
---------- ---------- ----------
L	   <NULL>	       0
M	   <NULL>	       0
S	   <NULL>	       0
XL	   2L		       2
XS	   2S		       1
SQL> update size_sample
  2  set size_name = mysqlapp.elt(mysqlapp.field(size_name, 'XS','XL'), '2S', '2L')
  3  where size_name in ('XS','XL');
 
2行が更新されました。

↑ の mysqlapp.elt(mysqlapp.field(size_name, 'XS','XL'), '2S', '2L') は decode(size_name, 'XS', '2S', 'XL', '2L') と書いたほうがシンプル。

SQL> select size_name, mysqlapp.field(size_name, '2S', 'S', 'M', 'L', '2L')
  2  from size_sample
  3  order by mysqlapp.field(size_name, '2S', 'S', 'M', 'L', '2L');
 
SIZE_NAME  MYSQLAPP.FIELD(SIZE_NAME,'2S','S','M','L','2L')
---------- -----------------------------------------------
2S							 1
S							 2
M							 3
L							 4
2L							 5
 


関連事項

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ オラクルサポートセンター