【SQL講座4】テーブルファンクションについて
今回は、テーブルファンクションについてです。
Access、MySQLはサポートしていないはずです。記述方法がそれぞれ違いますが、Oracle、SQL Server、DB2、PostgreSQLなどはサポートされています。
内容的には、パラメータがついたビューと考えれば良いでしょう。
拡張言語なので、記述方法がRDBMS毎にかなり違います。個別の記述については各々のマニュアルをご覧ください。
パラメータがついたビューなら、個人的にはビューのままの方が良いのではないかと思います。
なぜなら、ビューは実行時にテーブル単位に分解されてアクセスパスが評価されますが、テーブルファンクションの実行結果にインデックスはなく、ファンクションの結果を再度抽出したり、結合したりという使い方をするときには、パフォーマンスについて調整する必要があります。
そのため、わたしは基本的にファンクションのみで完結する使い方をします。
SQL Serverでは、ファンクションをプライベート、プロシージャをパブリックという使い方が理想と考えています。Oracleでは、ストアドプロシージャで結果セットを返すのが面倒なので、参照系は1つのテーブルファンクションにすることが多いです。
もちろん、
SELECT *
FROM
fn1(Par1, Par2, Par3, Par4) a
INNER JOIN fn2(Par1, Par2, Par3, Par4) b
ON a.Col1 = b.Col1
というようなことも可能ですが、インデックスが効かないので、利用するか大変悩むところです。サブクエリが何重にもネストするときには、パフォーマンス、可読性、メンテナンス性とトレードオフして検討しましょう。
十分に絞り込めるようにパラメータを調整すれば、非常に効果的です。
ほかに有効的な使い方があります。UI側でチェックボックスなどで複数選択されたものを
IN(入力リスト)
としたいだけのために、動的SQLを使わねばならなくなるということを避けることができます。まぁ、動的SQLでも良いのですけれど。
fnパラメータ分解(区切り文字, Pra1, Pra2,……)
パラメータの「区切り文字」で仕切られた文字列パラメータの
Par1, Pra2,……
を、[fnパラメータ分解]内で、パラメータを分解して出力する。
※ パラメータを複数用意するのは1つ数千バイトの桁制限があるため。
というようなテーブル関数を作るわけです。
つまり、
■SQL Server
SELECT * FROM fnパラメータ分解('|','1|2|3|4|5');
■ORACLE
SELECT * FROM TABLE(fnパラメータ分解('|','1|2|3|4|5'));
とすると、実行結果は
Col1
-----
1
2
3
4
5
となります。これだけなら何の意味もないのですけれど、
SELECT *
FROM Table1
WHERE
Col1
IN (SELECT Col1 FROM fnパラメータ分解('|','1|2|3|4|5'))
最終的には、ストアドプロシージャの中でも固定のSQLとして利用可能になります。
「だからどうした？」と言われればそれまでですけれど、SQLはすべてストアドプロシージャにするときは案外使えるのです。
もちろん、戻りの型別に、文字型用、数値型用、日付型用を用意すべきです。
更に変わった使い方としては、更新結果を返すというのもありです（却下されるけれど……）。
関数内で更新して、戻り値を1行の結果セット、つまり、
エラーコード・エラー内容……
とする。
使い方は
SELECT * FROM TABLE(fn更新ファンクション(Pra1, Pra2,……))
UNION ALL
SELECT * FROM TABLE(fn更新ファンクション(Pra1, Pra2,……))
UNION ALL
SELECT * FROM TABLE(fn更新ファンクション(Pra1, Pra2,……))
UNION ALL
SELECT * FROM TABLE(fn更新ファンクション(Pra1, Pra2,……))
UNION ALL
SELECT * FROM TABLE(fn更新ファンクション(Pra1, Pra2,……))
とするわけです。明細型の更新で、結果セットがエラーコードとエラー内容になります。DBに対するアクセスも1回になるし、ログも読みやすくならない？
ストアドプロシージャはベンダー依存が大きいから、これはやめた方が良いというのもわかりますけれど、慣れの問題と……。
◇ ◇ ◇ ◇
詳しい説明は、ハンズオンセミナーで行っています。
もっと基本的な部分から、テーブルファンクション・OLAP関数ぐらいまで説明します。
第1回は終了しました。
Cobol経験が長く汎用機でJOINのないSELECT文は使ったことがあるぐらいの経験しかない人がほとんどでしたが、これぐらいの問題は解けるレベルまで、このぐらいのSQLは読めるレベルまで、ご理解いただけたようです。
次回は2日間コースにして、SELECT文だけでなく、更新系やストアドプロシージャまでいければと考えております。
ご興味のある方はinfo@g1sys.co.jpまで。