表関数 - 戻り値として表を返す - HHeLiBeXの日記正道編

SQL2003から、表関数というものが追加された(と本には書いてある(ということにしておく(謎)))。

SQL2003ハンドブック―SQL最新標準規格

作者: 土田正士,小寺孝
出版社/メーカー: ソフトリサーチセンター
発売日: 2004/12
メディア: 単行本
クリック: 1回
この商品を含むブログ (4件) を見る

DB2でも、少なくともv9.1以降では表関数を定義することができる。
例えば、次のような感じで関数を定義する。

--
-- employees tableから、指定した年のデータだけを返す表関数。
--
CREATE FUNCTION TBL_FUNC_SAMPLE(pYear INTEGER)
    RETURNS TABLE(
          EmpID INTEGER
        , EmpName VARCHAR(8)
        , Dept VARCHAR(8)
        , Title VARCHAR(8)
        , Salary INTEGER)
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    BEGIN ATOMIC
        RETURN (SELECT EmpID, EmpName, Dept, Title, Salary
                FROM TABLE(
                    VALUES(1001, 'Tom', 'Med1', 'tA',  6000, 2003)
                        , (1002, 'Ken', 'Dev1', 'tB',  6200, 2003)
                        , (1001, 'Tom', 'Med2', 'tA',  6000, 2004)
                        , (1002, 'Ken', 'Dev2', 'tB',  6300, 2004)
                        , (1001, 'Tom', 'Med3', 'tA',  6400, 2005)
                        , (1002, 'Ken', 'Dev3', 'tB',  6300, 2005)
                        , (1001, 'Tom', 'Med4', 'tA',  6700, 2006)
                        , (1002, 'Ken', 'Dev4', 'tB',  6500, 2006)
                        , (1001, 'Tom', 'Med4', 'tC',  6900, 2007)
                        , (1002, 'Ken', 'Dev4', 'tD',  6800, 2007)
                        , (1001, 'Tom', 'Med4', 'tE',  6900, 2008)
                        , (1002, 'Ken', 'Dev4', 'tF',  7000, 2008)
                        , (1001, 'Tom', 'Med4', 'tG',  6900, 2009)
                        , (1002, 'Ken', 'Dev4', 'tH',  7000, 2009)
                        , (1001, 'Tom', 'Med4', 'tI',  6900, 2010)
                        , (1002, 'Ken', 'Dev4', 'tJ',  7100, 2010)
                ) AS T(EmpID, EmpName, Dept, Title, Salary, Year)
                WHERE Year = pYear);
    END

いろいろと面倒なので(謎)、表の内容をべた書きしているが、実際にはどこかに表を作って、関数の呼出者からは直接参照できないようにアクセス権を設定して、とかやるんだと思う。
そして、この表関数は次のように使用する。

SELECT EmpID, EmpName, Dept, Title, Salary
    FROM TABLE(TBL_FUNC_SAMPLE(2003)) AS T

実行結果。

EMPID       EMPNAME  DEPT     TITLE    SALARY
----------- -------- -------- -------- -----------
       1001 Tom      Med1     tA              6000
       1002 Ken      Dev1     tB              6200

一見すると「ビューと変わらないじゃないか」と言われそうなのは、サンプルが単純すぎるというのもあるかもしれない。
表関数にする場合、ビューと比べて次のような違いがあると考えられる。

単一のSQL文では表現できない、またはとても複雑になるような課題に対して、制御構文(IF、WHILE、etc.)を使用した手続きを書くことでよりシンプルな解が得られる可能性がある。
関数の中身をSQLで書く必然性がないので、より柔軟にいろんなものを表として返すことができるかもしれない(これはまぁ良し悪し、という気もするが‥)。

ただ、DB2では、動的SQLを実行するための構文(EXECUTE IMMEDIATE、etc.)を関数の本体に書くことができないようなので、関数本体をSQLで書く場合には何が何でも静的SQLで書くしかない、というのが注意点。

今回使用したSQL文の全体。

2010-0824-test.sql