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 となる。
２つのファンクションとも引数の最大数は 256 (expr , expr1, .. expr255)
(※1) FIELD ファンクション内での NULL 同士の比較は UNKNOWN、DECODE において NULL 同士の比較は TRUE になるので結果が異なる。
パッケージのソース
FIELD ファンクションは オーバーロード を使用しているので 「スタンドアロン・サブプログラム」 に書き換えはできません。
PL/SQL で可変長の引数のストアドプロシージャは作成できないため力技で実装を行っています。
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') と書いたほうがシンプル。但し decode は PL/SQL のコード内で関数としては記述できない。
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
関連事項
|
mysqlapp.sql.1